dates are not entered in a consistent format

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!
--
Thank you in advance!!!!

Bring into Access as a text field by adding phony record just below the
column names and putting text in the column -- XXXX.
Add a text field and a DateTime field to the Access table.
Run update queries like these to update the new text field --
Old_Date: Len([Excel_Date])
Criteria: 4
New_Text:
Update To: "1/1/" & [Excel_Date]

Old_Date: Len([Excel_Date]) New_Date:
Criteria: 8 Is Null
New_Text:
Update To: Left([Excel_Date], InStr([Excel_Date], "/")) & "1" &
Mid([Excel_Date], InStr([Excel_Date], "/"))

Old_Date: [Excel_Date] New_Date:
Criteria: Is Null
New_Text:
Update To: [Excel_Date]

New_Date:
Update To: CVDate([New_Text])


--
Build a little, test a little.


"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!
--
Thank you in advance!!!!

How can you possible figure out a day and month for 1998?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"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!
--
Thank you in advance!!!!

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