"Douglas" <do********@yahoo.com> wrote in message
news:be**************************@posting.google.c om...
I have a Vehicle MOT field in my table which i have as a Date field
I dont really want to hold the year, just 'dd mmm' as MOTs are the
same date every year.
I have the field on my form as a listbox holding all the dates of the
year, except 29 Feb which i dont need anyway.
When i select a value from the list i.e. 14 May and it stores it in
the table i believe it stores the current year as well i.e 14 May 2004
The problem now is how do i write these query that finds MOT Dates?
1. Select * FROM Vehicle WHERE MOT_Due_Date LIKE "Aug"
This woud find all the dates in August, can you use LIKE when
finding Dates?
2. Select * FROM Vehicle WHERE MOT_Due_Date = "10 Oct"
This would find only 10th Oct Dates? this doesnt work either as
MOT_Due_Date has the year as well.
3. I use
WHERE ((([Vehicle_Customer].[MOT_Due_Date]) Between
DateSerial(Year(Now
()),Month(Now()),1) And
DateAdd("w",DateSerial(Year(Now()),Month(Now
()),1),42)))
to search for MOTs between the 1st of the current month and 42 days
after
but since the current year is held in the table field, this query
will only
work for the year in which the records were entered and will fail
next year
and all years after.
Should i go back to storing the MOT_Due_Date as a text field and do
date conversions?
TIA
Doug
My advice would be store them as dates and include the year. Although MOT's
generally have the same dates from year to year, they are not like birthdays
which (apart from a leap year) can be guaranteed to be on the same date each
year. There may be a number of cases where the date varies.
For example, the MOT expires on Jan 27 so you take it in on Jan 18 to get
the certificate. If you don't bring the old one, the garage must issue the
new one from the 18th so you lose a few days. Even if you do bring the new
one, my local garage seems to think if the old one expires on the 27th then
the new one expires on the 26th. I disagree, but he's the one who signs the
certificate. Another example is if the car is off the road for whatever
reason, then you don't need an MOT, so if people go abroad for a couple of
months, then there may well be a gap in the MOT history. So it could be
that the old one expires end Dec 03, there is a gap and the new one expires
Jan 05.
Anyway, if you are certain that you will ignore the year part of the date,
then you can do that and write a function like the one below. Just paste it
in a new module and you will be able to use it in your queries. It might
prove quite useful.
Public Function DaysToMOT(MOTDate) As Variant
On Error GoTo ExitHere
Dim dteThisYearMOT As Date
Dim dteNextMOT As Date
If IsNull(MOTDate) Then Exit Function
MOTDate = CDate(MOTDate)
dteThisYearMOT = DateSerial(Year(Date), Month(MOTDate), Day(MOTDate))
If dteThisYearMOT >= Date Then
dteNextMOT = dteThisYearMOT
Else
dteNextMOT = DateAdd("yyyy", 1, dteThisYearMOT)
End If
DaysToMOT = dteNextMOT - Date
ExitHere:
Exit Function
End Function
