Date Ranges

[Former Member]

Hello,

I need to date ranges please; 1 to pick up interactions for the last 8 weeks and 1 to pick up interactions for the last 4 months.

Please help!

Many thanks.

[Former Member · Accepted solution]

I am not sure what you want to do, but if you are looking to select data, my prefrence is to have a start and an end date parameter, rather than a date range parameter then use something like

date({interaction date}) >= {start date parameter} 
and date({interaction date}) < {end date parameter}+1

This will work for either of your examples.

Debi

[Former Member]

Thanks for the replies guys and apologies for the vague request! The 2 date ranges are for 2 separate reports, 1 to show interactions for the last 8 weeks and 1 to show interactions for the last 4 months. These reports are run automatically using CRD therfore I'm unable to use date parameters.

Many thanks.

[Former Member]

In that case...

Last 4 months...

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

Last 8 weeks...

DateAdd("ww", DateDiff("ww", #1/7/1900#, CurrentDate) -8, #1/7/1900#)

Jason

[Former Member]

Debi is right... You aren't very specific about what you want to do here.

If you're looking the last 4 months of data to show on the report and then distinguish the records for the last 8 weeks, then do something like this...

In the Select Expert use a formula like this...

{TableName.DateField} >= DateAdd("m", DateDiff("m", #1/1/1900#, CurrentDate) -4, #1/1/1900#)

This will bring in all of the records for the last 8 months.

Then create a formula like this...

IF {TableNmae.DateField} >= DateAdd("ww", DateDiff("ww", #1/7/1900#, CurrentDate) -8, #1/7/1900#) 
THEN "Last 8 weeks"
ELSE "Last 4 months"

You could then group on this formula if you so choose.

HTH,

Jason