Date/Time Query issue..

[PogoWolf]

Hey all,
I'm trying to create a query that will pull data in 3 month chunks. The function would be run on the 1st of the month (every month) and pull the previous 3 months work of data.

The issue is that I need the Query Dynamic (such as using the Date() command) so that I don't need to update the Query each month, and the query needs to handle 'weird' month lengths, and pull everything up to midnight...

For example, if today was 11/1/04. I would need to pull all data from 7/1/04 12:00:00 AM THOUGH 10/31/04 11:59:59 PM


I was thinking something like this:

Between CDate(DateAdd("m",-3,Date()) & " 12:00:00 AM") And CDate(DateAdd("d",-1,Date()) & " 11:59:59 PM")

on the where clause..but this doesn't seem to work right..



The PogoWolf

http://pogowolf.GamersCommune.com

 

[PHV]

What about this ?
Between CDate(DateSerial(Year(Date),Month(Date)-3,1) + #00:00:00#) And CDate(DateSerial(Year(Date),Month(Date),0) + #23:59:59#)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PogoWolf]

thanks!!!
that's what I needed.


The PogoWolf

http://pogowolf.GamersCommune.com