Situation: I have in a table of records with three number fields for storing a date (year, month, day). I have a query where I want to return records between a certain dates, but how do I convert the three number fields in to a single date field so I can use a query like this

SELECT records FROM table
WHERE recorddate BETWEEN startdate AND enddate

Thanks ahead of time!

Why three fields?

I didn't have a choice in it... it was the way the application had been built before I got there. It also has to do with the data it imports...

Trust me, when I say if I had a choice I would have used a date field and saved myself the headache... =)

I guess you can use the DataSerial function.
Look in the Access helpfile for "DateSerial".

HTH,

RV

Try this

SELECT CDate([Dates]![Month] & "/" & [Dates]![Day] & "/" & [Dates]![Year]) AS [Date] FROM Dates
WHERE (((CDate([Dates]![Month] & "/" & [Dates]![Day] & "/" & [Dates]![Year]))>#1/1/2000#));

Concatnate the Month, Day, Year together with a slash in between, THEN use the cDate function to convert it to a date.

Jerid