Returns records for the previous year up to the previous Saturday 1

[southbean]

Hello All,

I'm trying to create a formula that returns records for the previous year up to the previous Saturday.

I have two date formulas:

StartDate: CurrentDate - 366
EndDate: {table1.DateField} - (dayofweek({table1.DateField},crSaturday)-1)

In my record selection formula I have this:

{table1.DateField} >= {@StartDate} and
{table1.DateField} <= {@EndDate}

However, this is only returning records for Saturday Dates (02/25/2012, 03/03/2012, 03/10/2012, etc.).
I need the report to return all dates in the date range.

Any/All advice would be greatly appreciated!

Thanks,

- LM

 

[Madawc]

Put your formulas in separate formula fields and see what you've got. Always a good procedure

But I think the main problem is that you use {table1.DateField} to define End Date rather than basing it on CurrentDate.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[lbass]

Try this as a record selection formula:

{table.date} in dateadd("yyyy",-1,currentdate-dayofweek(currentdate))+1 to currentdate-dayofweek(currentdate)

This would return records from March 18, 2011 to March 17, 2012. There is nothing in your formula or mine that would only return Saturdays. If you are grouping on the date field, be sure the group is to "on change of day", not the default: week.

Also, if your field only contains Saturdays, then of course only Saturdays would be returned.

-LB

 

[southbean]

Hello,

Thank you BOTH for your responses!

Lbass, I tried your solution and it worked GREAT! Thanks a lot!

- LM