Formula for last month

[Former Member]

I am not sure why I cant think of this but for whatever reason I cant figure out what to use for a formula for 'Last Month'

Example:

Selection:

{date.field} = {@lastmonth}

I need to select all of the dates in {date.field} that are of @lastmonth

So currently it needs to give me everything that happened in the month of July.

Thanks for your help.

[Former Member · Accepted solution]

Use:

{Date.Field}=LastFullMonth

Hope this helps!!

[Former Member · Accepted solution]

PERFECT!!!!!

[Former Member]

{@Date Packed} IN

DateAdd("m", DateDiff("m", #1/1/1900#, CurrentDate) -1, #1/1/1900#)

To

DateAdd("m", DateDiff("m", #1/31/1900#, CurrentDate) -1, #1/31/1900#)

This is what I came up with in the end, and this gives me all of last month but not this month(which is what I am after). Do you see any problems with this if one of the months does not have 31 days or any other issues with it this way?

[Former Member]

Sorry about that. This forum isn't exactly iPhone friendly. You did make the proper correction with the #.

My furmula doesn't care how many days are in a month. It even works on February on leap years.

If, by "last month", you mean current month to date, then use this...

{DateField} IN DateAdd("m", DateDiff("m", #1/1/1900#, CurrentDate), #1/1/1900#) TO CurrentDateTime

How it works... It relys on the fact that the DateDiff function only reruns an integer based on whole time periods. So using the DateDiff function to calculate the number of full months since 1/1/1900 and then adding that number back to 1/1/1900 will give a result of 12:00am of the 1st day of the curent month. The "-1" from the 1st formula pushes it back a month to 12:00am of the previous month.

Hth,

Jason

[Former Member]

I just added a # to the end of each of those and that seemed to work

[Former Member]

This is what I get.

The matching # for this date time literal is missing

[Former Member]

Try this...

{DateField} IN 
DateAdd("m", DateDiff("m", #1/1/1900#, CurrentDate) -1, #1/1/1900)
TO 
DateAdd("m", DateDiff("m", #1/1/1900#, CurrentDate), #1/1/1900)

HTH,

Jason