date query without year

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

I have a series of dates spanning several years and I'd like my where clause
to limit the dates between Nov 1 and Apr 30 regardless of year. Is there a
way to do it without writing multiple where clauses?
 
Quick and painless, worked like a charm -- thank you.

Duane Hookom said:
You can use a where clause like:
WHERE Month([YourDateField]) Not Between 5 and 10
--
Duane Hookom
Microsoft Access MVP


Ian said:
I have a series of dates spanning several years and I'd like my where clause
to limit the dates between Nov 1 and Apr 30 regardless of year. Is there a
way to do it without writing multiple where clauses?
Click to expand...
Click to expand...
 
I have a series of dates spanning several years and I'd like my where clause
to limit the dates between Nov 1 and Apr 30 regardless of year. Is there a
way to do it without writing multiple where clauses?
Click to expand...

Since Access stores only a complete date (a precise point in time) in a date
field, you'll need to trick it. Put a calculated field in your query

NoYearIncluded: DateSerial(Year(Date()), Month([datefield]), Day([datefield]))

This will migrate all your dates to the current year; you can use any year
from 100AD through 9999AD by replacing Year(Date()) with an integer if you
prefer. You can use a criterion

[>= #11/1# OR <= #4/30#

to get dates for November, December, January, February, March and April (if
that's what you mean by "between").
 
Back
Top