Query On a date range

[Advisedwolf]

Hi,

Sounds simple, but what is the criteria expression in a query I use to display all records that are older than 3 months.
I have a "date closed" field, and I need to query this to show all records closed over 3 months from "todays date"

Thanks

 

[Bob44TheCat]

Hi Gav,

Try [date closed]<=Date()-90

Cheers,

Bob.

 

[Golom]

3 months or 90 days?

The difference is that on Dec 1 (for example) September 30 is 3 months ago because the month number difference (12 - 9) is 3 even though thats only 62 days before Dec 1.

If you want MONTHS then

WHERE DateField <= DateAdd ( "m", -3, Date())

if you want DAYS then

WHERE DateField <= DateAdd ( "d", -90, Date())

 

[kphu]

editing bob's sample try

Try [date closed]<=Date(year(today(),month(today())-3,day(today())

Since every month isn't 30 days this will give you the actual days in the current month and prior.

Ken

 

[Golom]

kphu

Good idea but you need

[date closed]<=Dateserial(year(Date(),month(Date())-3,day(Date())

The "Date" function doesn't accept arguments. The other problem is that "Today" is not a standard VB function. "Date" returns the current date.

 

[Advisedwolf]

Thanks for your assistance, however when entering this criteria
[date closed]<=Dateserial(year(Date(),month(Date())-3,day(Date())

It states there is a missing ] in the statement.. I cannot see where this is missing from

Sorry for delay in responding...

Ta

 

[Golom]

Its not a missing "]" ... its a missing ")" ... and its missing here

[date closed]<=Dateserial(year(Date()),month(Date())-3,day(Date()))