calculate 6 months of data for filter 1

[giggles7840]

xir2/win xp

I need to bring in only 6 months of data based on a date field using the record selection editor.

there isnt a set function like lastfullweek, monthtodate, etc.

whats the best way to calculate this?

 

[Turkbear]

Hi,
look into using the dateDiff function:
Here is an example from the CR Help:

Use DateDiff with the "m" parameter to find the number of months difference between two dates.

DateDiff ("m", #3/15/1999#, #7/13/1999#)

Returns 4.

So, use it to set a record selection formula that
uses the date field in your data, something like:

DateDiff("m",{DateFieldInData},CurrentDate) = 6

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Actually the formula I posted would only get you data from exactly 6 months ago...modify it to get 6 month's worth of data:

DateDiff("m",{DateFieldInData},CurrentDate) <= 6

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[bdreed35]

The only issue I see with the above example is that it will NOT pass through to the SQL which will make your report less efficient.

Write the logic this way so that you can accomplish the SQL pass thru:

{table.datefield} >= DateAdd("m", -6, CurrentDate)

~Brian