Because a value in the format '2/2005' will be interpreted as the 1st of the
month in question, and the CVDate function will operate quite happily on it,
it can in fact all be done in a single UPDATE query:

UPDATE [YourTable]
SET [New_Date] =
CVDate(IIf(Instr([ExcelDate],"/")0,[ExcelDate],"1/" & [ExcelDate]))
WHERE Len([ExcelDate] & "") 0;

Be sure to force the ExcelDate value to a text data type by means of the
dummy row as described by Karl.

Ken Sheridan
Stafford, England

daisy wrote:
Is it possible to clean up a date field where someone was keeping an excel
sheet and no we are moving this into Access but the date field is a mess

For example he has
1998
2/2005
1/1/2010

We need all dates to be mm/dd/yyyy?

Is there a formula or update I could run against this so he's not going thru
3000 rows of data?

Thank you!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1