How to fix up Date criterias for specific reports

[shalskedar]

In my db there are 2 reports which requires specific date criteria.

First report is (91-180 days--)-)...Here it should display me all the records from Aug 2009 to Sep 2009,assuming here the current month as Dec.

For the month of Nov it should display from July 2009 to Aug 2009..
Thus depending on the current month the criteria changes

Similarly other Report (more than 180 days)-)...Here it should display me all the records for July & before July 2009(i.e<=July2 009).

Like for Nov---<=June & so on

Currently I change the criteria manually accrding to the current month...But i woull like to know what criteria i set to get it automatically for every current month..So that i need not do it manually
Can anyone Plz help with this

[MikeTheBike]

Hi

You coud try a Where clause like this

WHERE Month(RecordDate) Between DateSerial(Year(CriteriaDate), Month(CriteriaDate)-4,1) AND DateSerial(Year(CriteriaDate),Month(CriteriaDate)-2,1-1)

where CriteriaDate the specified date and RecordDate is the Date to be filtered.

If CritereaDate is ANY date in December then all the records with a date between 1st August and 30th September of the same year wil be returned.

I know this lookes odd, particularly if you are not familiar with the DateSerial() function.

For instance this

DateSerial(Year(CriteriaDate),Month(CriteriaDate)-2,1-1)

first calculates the date for first day of the month 2 months previous to CriteriaDate and THEN calculates the date offset one day previous (-1 in the day position) ie. the last day of the previous month.

Access help gives more info.

There are obviously a number of ways of doing this, but I hope that helps.


MTB

[NeoPa]

It can actually be simpler even than that, although your question has need for much clarification and fewer contradictions. I'll assume (because it makes some sense) that you are looking for records between 6 and 4 months prior to the current month. The number of days is a red-herring and quite misleading. August and September, being a two month period is just another error in the question as far as I can guess. Unfortunate that you couldn't be more careful when asking for assistance. It's always easier to answer questions that make sense.

Moving on :

WHERE Format([RecordDate],'yyyymm')
      Between Format(DateAdd('m',-6,[Date]),'yyyymm')
      And Format(DateAdd('m',-4,[Date]),'yyyymm')

Clearly from this basis you can choose any month-based period, in the past or future, simply by changing the second parameter of the DateAdd() calls.

[shalskedar]

Thanks!!its working...

[NeoPa]

Cool!

And thanks for responding, letting us know it's worked.