How can I filter dates using month and day only for seasonal addre

R

Roy Laws

I need to be able to choose between addresses for my clients based on a date
range such as Nov 1 - Feb 28. I understand how to do it for the full date,
but then I have to redo the filters for each year or I get bogus results. Is
there a way to consider only the month and day portion of the date?
 
F

fredg

I need to be able to choose between addresses for my clients based on a date
range such as Nov 1 - Feb 28. I understand how to do it for the full date,
but then I have to redo the filters for each year or I get bogus results. Is
there a way to consider only the month and day portion of the date?
Click to expand...

In a query?
For the previous November through the last day of February (28th OR
29th) of whatever the current year is, as criteria in a query:

Where YourTable.DateField between DateSerial(Year(Date())-1,11,1) and
DateSerial(Year(Date()),3,0)

Note: DateSerial(Year,Month+1,0) is the last day of any Month, which
allows for leap years in February.
 
K

Klatuu

This will only work correctly for Jan1 through Feb 28/29 of the year of the
end date. Otherwise, it will return the previous year's data.
 
J

John Nurick

Hi Roy,

Something like this, perhaps

SELECT *
FROM ManyDates
WHERE DateSerial(2000, Month(TheDate), Day(TheDate))
BETWEEN DateSerial(2000, StartMonth, StartDay)
AND DateSerial(2000, EndMonth, EndDay)
ORDER BY TheDate;
 
R

Roy Laws

I will have to try it out to be sure, but I suspect it won't be what I need.
This is a case where I have clients who are at one address most of the year
(any year) and at a different address for a vacation period, again, this is
relative to the day and month, the year is irrelevant, but if it comes into
play in the selection, I will not get a match. For instance, the only way I
know to enter a date in the underlying table is [startdate] 11/11/2005 and
[EndDate] 02/15/2006, etc.
This will work with a "is today between those two dates" type query.
However, if I overlook updating all the records next year, then I will not
get a positive result on any of them because the year will throw off the
match. I am seeking a way to enter only the month and day, yet still retain
the ability to find a "between" relationship. What I would need would bet
the equivalent of the built-in format for short date, yet one that would be a
"shorter date" which would read only 11/11 or 02/15 without any checking for
year at all. That way, it could be entered in the master table and not need
to be updated each year.
 
Top