Help retrieving records by date range..what's the expression?

[MissAnna]

Hi all,

I need help retrieving records...

the records I need the query to retrieve are from the current month one year ago, or this month last year

Can anybody help me with this?

Thanks!
Anna

 

[Mitch_____W]

Try the following in the your date field's "Criteria":

Between Date()-365 and Date()

That should do the trick...

 

[MissAnna]

Hi Mitch,

Thanks for that, it works for returning last years records which is great.

Unfortunately the query is returning all months last year, as opposed to this month last year..do you know how I would fix that?

Thanks for your help,
Anna

 

[Jon K]

Try this setting in a column in the query grid (replacing with the correct name of the date field):-

Field: Format([nameOfDateField],"yyyym")
Show: uncheck
Criteria: Format(DateAdd("yyyy",-1,Date()),"yyyym")

 

[MissAnna]

Thanks Jon!

That appears to be working, thank you.

The query is retrieving duplicates though, i.e. returning the same record 4 times, is there a way I can stop that?

Thanks very much for your assistance!
Anna

 

[MissAnna]

Hi,

Strike my last comment, they're not duplicate records after all.....thank you again for your help!

It works perfectly...

Anna