Access - Convert text field to date field

I have been handed an Access database with 1.4 million records.  I didn't even think that was possible.  The date field is text.  Here is an example of a date, 20081001  What that date means is 01/10/2008.  Is there any VBA that will go through the table and turn it into a date field without destroying the current data?

 Any help is appreciated
JohnMac328Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gustav BrockConnect With a Mentor CIOCommented:
It is simpler to use Format:

UPDATE
  tblYourTable
SET
  NewDate = IIf(IsDate(Format([TextDate],"!@@@@/@@/@@")),CDate(Format([TextDate],"!@@@@/@@/@@")),Null)

/gustav
0
 
omgangCommented:
Create a new date/time field first.  Then you can experiment with data conversion update queries without disturbing the original data.

CDate is a conversion function to convert a string to a date value.  Try it and see.  May need to get a bit creative but it's doable.
OM Gang
0
 
pteranodon72Commented:
Back your data up.
Create a Date field in the table: NewDate.
Create an Update Query -- in SQL view:

UPDATE yourtablename SET NewDate = Iif(Len(TextDate & "")<>8, Null, DateValue(Left(TextDate,4), Mid(TextDate, 5,2), Right(TextDate,2))

HTH,

pT72
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JohnMac328Author Commented:
Good suggestion. Due to the number of records, I need to see if someone can come up with the code to loop through and convert ******** to **/**/****
0
 
JohnMac328Author Commented:
I will try that pteranodon72 - did not see your post when I was writing
0
 
pteranodon72Commented:
Sorry, I may have misread the month, day order, whatever your convention is.

Back your data up.
Create a Date field in the table: NewDate.
Create an Update Query -- in SQL view:

(If it is stored as yyyymmdd)
UPDATE yourtablename SET NewDate = Iif(Len(TextDate & "")<>8, Null, DateValue(Left(TextDate,4), Mid(TextDate, 5,2), Right(TextDate,2))


(If it is stored as yyyyddmm)
UPDATE yourtablename SET NewDate = Iif(Len(TextDate & "")<>8, Null, DateValue(Left(TextDate,4), Right(TextDate,2), Mid(TextDate, 5,2))


(DateValue takes arguments in (year, month, day) order.)
HTH,

pT72
0
 
JohnMac328Author Commented:
I am getting this error and can't see where the missing paren goes
Example.jpg
0
 
JohnMac328Author Commented:
Here is my statement

UPDATE DateCorrect2 SET NewDate = Iif(Len(D_DATE & "")<>8, Null, DateValue(Left(D_DATE,4), Mid(D_DATE, 5,2), Right(D_DATE,2))
0
 
JohnMac328Author Commented:
cactus - It pops-up asking for the value of "NewDate"    Also here is an example of the original format - no slashes

20081001

0
 
omgangConnect With a Mentor Commented:
In cactus_data's example, NewDate is the name of the new field you create in your table.
OM Gang
0
 
omgangCommented:
...and TextDate is the name of the current field in your table that has the dates stored as text.
OM Gang
0
 
JohnMac328Author Commented:
That worked - thanks all
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
All Courses

From novice to tech pro — start learning today.