Converting 5 numeric fields to a date

[betheball]

In my db, the parts of a date are broken into 5 separate numeric fields, namely, month, day, year, hour, minutes. I need to retrieve records with a date after today. Is there a way I can group those fields together to form a valid date that I can then compare to today's date? Was wondering if I could maybe use the cDate function in my SQL? Thoughts?

 

[dcx693]

Look up the DateSerial() function. If you need to consider the time also, look at the TimeSerial() function.

 

[betheball]

Can I combine the two in my SQL to form one valid date string??? Something like:

SELECT * From Table WHERE DateSerial(mymonthfield, mydayfield, myyearfield)&TimeSerial(myhourfield, myminutefield)=Now()

 

[raskew]

You haven't explained why you're storing your date/time values in this manner, and perhaps there's a valid reason, but I'd have to say that it's really cumbersome and unorthodox.

Here's an example--from the debug window--where a valid date/time datatype is broken down into its components, and then reconstructed (as you will have to do using the present scheme) back into a valid date.

mydate = now()
? mydate 
1/27/04 6:30:20 PM 

'show it as it's stored in Access
? cdbl(mydate)
 38013.7710648148
 
myyear = datepart("yyyy", mydate)
? myyear
 2004 

mymonth = datepart("m", mydate)
? mymonth
 1 

myday = datepart("d", mydate)
? myday
 27 

myhour = datepart("h", mydate)
? myhour
 18 

mymin = datepart("n", mydate)
? mymin
 30 

mysec = datepart("s", mydate)
? mysec
 20 

'Now, to reconstruct using the variables created above:

? dateserial(myyear, mymonth, myday)
1/27/04 
? timeserial(myhour, mymin, mysec)
6:30:20 PM 

yourdate = dateserial(myyear, mymonth, myday) + timeserial(myHour, mymin, mysec)
? yourdate
1/27/04 6:30:20 PM 

'and to prove it's actually in date/time data type:
? cdbl(yourdate)
 38013.7710648148

You really should consider adding a date/time field and using an update query to populate it.

HTH - Bob