help with syntax for a date range criteria please

H

Howard

I have tried doing this for a while but can't get my head around the
change of year on 31st Dec.

Can anyone offer an elegant solution to a criteria satisfying the follwing.

From a date/time field called IncidentDate I need to extract those
records falling between today's date a year ago and the 1st September
before that.

ie
if today is 10th Oct 2009 I want records from 1st September 2008 to 10th
Oct 2008

but if today was 3rd January 2010 I would want those from 1st September
2008 to 3rd January 2009 because the range spans two different years.

Without wrtiting a length UDF what is the best way to do it?

Howard
 
M

Marshall Barton

Howard said:
I have tried doing this for a while but can't get my head around the
change of year on 31st Dec.

Can anyone offer an elegant solution to a criteria satisfying the follwing.

From a date/time field called IncidentDate I need to extract those
records falling between today's date a year ago and the 1st September
before that.

ie
if today is 10th Oct 2009 I want records from 1st September 2008 to 10th
Oct 2008

but if today was 3rd January 2010 I would want those from 1st September
2008 to 3rd January 2009 because the range spans two different years.

Between DateSerial(Year(DateAdd("yyyy",
IIf(Month(Date()<9,-1,-2), Date()), 9, 1)
And DateAdd("yyyy", -1, Date())
 
H

Howard

Thank you for a fast reply. I can see what you are trying to do but I
get a 'wrong number of arguments' error with it.

Howard
 
H

Howard

OK, fixed it, just a bracket missing. Thank you. here it is in case it
helps ayone else

Between
DateSerial(Year(DateAdd("yyyy",IIf(Month(Date())<9,-1,-2),Date())),9,1)
And DateAdd("yyyy",-1,Date())
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top