Convert Numbers to Dates
Use an Update Query, Left, Right, and Mid Functions
Q: I received a database from
someone that has dates stored as six-digit numbers such as 990102 for
Jan-02-1999. How do I convert
them to Access Dates?
A: You've inherited a database
that has dates in a text field, with a format like 990102. You need to
convert those to regular Access date fields. Here's my sample table:
Add a new blank DATE field to your table to store your new value. My old
date is in the text field CustomerDOB, so I'll add a new one called
NewCustomerDOB.
Now I'll create an UPDATE QUERY using the LEFT, RIGHT, and MID functions
to put the date together:
Be sure to back up your database before running
any update queries as there is always the possibility of data loss!
Watch the video lesson
below to see how it's done. I cover a LOT more detail in the full
lesson. I've actually added what to do if you get a date that's not
exactly six digits... if your dates are stored in NUMBER type fields,
you can't have leading zeros.
Taken from Microsoft
Access 309, Lesson 4:
4. Converting Numbers into Dates
Got a field with dates like 990105
Convert with an Update Query to 1/5/99
LEFT, RIGHT, MID functions
Update Query
String Concatenation
Calculated Fields
By Richard Rost
Click here to sign up for more FREE tips
|