Dates (with no year stored) query puzzle

  • Thread starter Thread starter Douglas
  • Start date Start date
D

Douglas

I have a table with a field that is used to store the date (MyDate) in
the format: "dd mmm"

ie

01 Jan
27 Mar
15 Sep
23 Nov

Its just a text field as I dont want to store the year as it is the
same date every year (ignore leap years for now)

I want to query this table and do a BETWEEN
ie

WHERE MyDate BETWEEN ComboStartDate.value and ComboEndDate.value

THis is fine as i just format the fields and tag on the current year
and it works perfectly

BUT!!!!!!!

if the ComboStartDate is after the ComboEndDate then ComboEndDate
becomes the following year
ie ComboStartDate = "01 Dec" and ComboEndDate = "31 Jan"

ComboSTartDate would become "01 Dec 2004" and ComboEndDate would be
"31 Jan 2005"

Again i can handle this perfectly with a simple IF check....

but how do I handle all my table entries in the query (MyDate), I
can't just tag on the current year this time as i want to check all
values in the above example from 01 Dec 2004 - 31st Jan 2005

Im a bit stuck :(

TIA

Doug
 
If you want to store a date, use a date/time field. If you only want the
Month and Day then use two fields, one for month and the other for day.
Attempting to use a text field will continue to be an issue until you change
it.
 
Back
Top