If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
dates are not entered in a consistent format
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 |
Thread Tools | |
Display Modes | |
|
|