Why does this not work (selecting a date) ?

[mscertified]

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

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

 

[KARL DEWEY]

Works fine for me with 2003 SP2.
You omitted a closing parenthesis.

 

[mscertified]

Where is the missing parentheses? I count 4 opening and 4 closing???

 

[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.

 

[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
..

 

[Jerry Whittle]

There's 5 opening and 4 closing. Time to invest in bifocals! ;-)