Dynamic Month in Query 1

[cwsstins]

I'm relatively new to Access...

I'm working on a query that will display multiple fields based on a criterion that indicates the "Opened Date" should be in a date range of the current month. Running the query each day will bring up all the data for that month. Right now, I need to modify the criterion every time we roll into a new month, to reflect the current month.

I'd like to use a function that will automatically pull data only for the current month, no matter what month we're in.

For historical purposes, I would also like to be able to modify the function to include current month and all preceding months in the same year (1/1/05 - present).

Any suggestions on how to do this would be appreciated.

 

[SkipVought]

Hi,

Where [YourDateField] Between DateSerial(Year(Date()), Month(Date()), 1) AND DateSerial(Year(Date()), Month(Date())+1, 0)

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[PHV]

1) current month:
WHERE Format([Date field],'yyyymm')=Format(Now(),'yyyymm')
2) historical purpose:
WHERE Year([Date field])=Year(Now) AND Month([Date field])<=Month(Now)

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

 

[cwsstins]

Guys, thanks for the suggestions...based on Skip's, I came up with this: Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date()),Day(Date()))

It seems to be working well...

Thanks again,
stinsman