dates are not entered in a consistent format

[daisy]

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!

 

[KARL DEWEY]

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])

 

[Dorian]

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