By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

Help with Date Data Type ( i dont want to hold the year)

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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
Nov 12 '05 #2

P: n/a
On 15 Jan 2004 01:55:36 -0800, do********@yahoo.com (Douglas) wrote:
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.


You know what the date is, but you don't want to store it, because
it's the same every year, and everyone knows what it is? Does Y2K
ring a bell? <g>

--
Mike Sherrill
Information Management Systems
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.