How to Convert Numbers to Dates in Microsoft Access

Introduction

If you have ever received a database from someone that has dates stored as six-digit numbers, like 990104, this lesson will teach you how to convert those to proper Access dates.

By: Amicron

Length: 3:20

Comments: 0

View more articles by this author

Flag | RSS

(4)

Add to Favorites

URL:
Embed:

Instructions

Text Size: – +

Print Article

Difficulty: Challenging

Tips & Warnings:

Be sure to back up your database before running any update queries as there is always the possibility of data loss!

Step1

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.

Step2

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.

Step3

Now I'll create an UPDATE QUERY using the LEFT, RIGHT, and MID functions to put the date together. My update query will update the NewCustomerDOB field to: Right([CustomerDOB],2) & "/" & Mid([CustomerDOB],3,2) & "/" & Left([CustomerDOB],2)