date query without year

[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?

 

[Duane Hookom]

You can use a where clause like:
WHERE Month([YourDateField]) Not Between 5 and 10

 

[Ian]

Quick and painless, worked like a charm -- thank you.

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

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?

 

[John W. Vinson]

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?

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").