Why does this not work (selecting a date) ?

M

mscertified

SELECT CDate((Month(RecordDate) & '/' & Day(RecordDate) & '/' &
Year(RecordDate)) AS RecDate

This converts 8/31/2007 to 8/1/2007
 
J

Jerry Whittle

What kind of data type is RecordDate? If a text field, does RecordDate look
like mm/dd/yyyy or dd/mm/yyyy?

Also why not just this assuming that the RecordDate field is text?
CDate(RecordDate)

Please show some examples of the problem dates.
 
J

John Spencer

IF RecordDate is a dateTime Field, I would use the DateSerial function.

SELECT DateSerial(Year(RecordDate), Month(RecordDate),Day(RecordDate))
FROM ...

What you posted seems as if it has one too many opening parentheses at the
beginning of the calculated field and I would expect it to generate an
error.
CDate((Month(RecordDate) & '/' & Day(RecordDate) & '/' & Year(RecordDate))
AS RecDate
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top