I am editing data in a table that was designed by someone else. For some reason, they divided the date into three columns (YR, MON, DAY). I would like to join these three fields together and create a legal DATE field. I have tried the following:

SELECT YR AND MON AND DAY AS TheDate
FROM MyTable;

However, it returns an odd result. I think this might be because ACCESS does not know I want a date format.

My question is: is the above QUERY correct and how do I tell ACCESS to put the concatenated data in a date format.

Thanks in advance for any help you can provide...

Look at the DateSerial Function.

It depends on the datatypes of YR, MON,DAY. If they are all integer then you can use:
DateSerial(YR, MON, DAY)

Else if your fields are string then try:

Datevalue(DAY & " " & MON & " " & YR)

Datevalue is quite intuitive in its ability to decipher a text date. So the following will all work:
DateValue("dec 30 2006")
DateValue("30 dec 2006")
DateValue("12 30 2006")
DateValue("2006 12 30")

hth
Stopher

Sorry, Paul, didn't see your post until too late :o

No problemo. I only typed 5 words, so was a lot faster. :p

Thanks very much for your help.