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?

Improve company productivity with a Business Account.Sign Up

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
 
omgangIT ManagerCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best 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 IT ManagerCommented:
In cactus_data's example, NewDate is the name of the new field you create in your table.
OM Gang
0
 
omgangIT ManagerCommented:
...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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.