Help with syntax

[Gizmodo]

Hey all,

Trying to pull dates between a 3 year period where beginning date is 4/31/(Current Year - 3 years) and 3/31/(Current Year)... this is what i have come up with...

WHERE ([County Master List].[Date Opened]) between #4/1/(datepart("yyyy",now()-3))# and #3/31/(datepart("yyyy",now()))#

I get a syntax error

any suggestions or new methods?



Thanks

[JoeM]

I prefer to use the DATESERIAL function to build dates. See: http://www.techonthenet.com/access/f...dateserial.php

[Gizmodo]

Perfect Thank you....

Came up with this

WHERE [County Master List].[Date Opened] between DateSerial((datepart("yyyy",now()))-3, 4, 1) and DateSerial((datepart("yyyy",now())), 3, 31)

One more question though?

Would it be possible to insert and iff statement within this where clause that would pull the current month and depending on if the month is December change the formula to the following:
WHERE [County Master List].[Date Opened] between DateSerial((datepart("yyyy",now()))-2, 4, 1) and DateSerial((datepart("yyyy",now())), 3, 31)

Basically to subtract 2 years instead of three

[JoeM]

I actually prefer

WHERE [County Master List].[Date Opened] between DateSerial(year(now())-3, 4, 1) and DateSerial(year(now()), 3, 31)

over

WHERE [County Master List].[Date Opened] between DateSerial((datepart("yyyy",now()))-3, 4, 1) and DateSerial((datepart("yyyy",now())), 3, 31)

Would it be possible to insert and iff statement within this where clause that would pull the current month and depending on if the month is December change the formula to the following:
WHERE [County Master List].[Date Opened] between DateSerial((datepart("yyyy",now()))-2, 4, 1) and DateSerial((datepart("yyyy",now())), 3, 31)

Basically to subtract 2 years instead of three

Try:

WHERE [County Master List].[Date Opened] between DateSerial(year(now())-IIf(Month(Now())=12,2,3), 4, 1) and DateSerial(year(now()), 3, 31)

[Gizmodo]

Got you...makes sense...code looks alot "cleaner" and easier to understand

Thanks again