Convert Text Field To Date

[Guest]

Hi All,

I've had a look at some existing questions, but being fairly new to Access,
I'm a bit lost once I get to the code stage.

I have a table someone sent to me, which i need to append to an existing one
database. Their date is in the format DDMMYYYY, but is formated as text. My
existing table is formated as DD/MM/YYYY as a date format. How can I convert
the text to a DD/MM/YYYY format, ready to be uploaded?

I've seen the posts about CDATE etc. but where do you put this code (or how
do you run it). I know how to write and run code in Excel, but only have a
basic knowledge of access.

Can anyone help?

Cheers, John

 

[Douglas J. Steele]

Import the text into a temporary table.

Create a query based on that temporary table. Instead of the text date
field, put something like:

CDate(Left([TextDateField], 2) & "/" & Mid([TextDateField], 3, 5) & "/" &
RIght([TextDateField, 4))

or (probably better)

DateSerial(RIght([TextDateField, 4), Mid([TextDateField], 3, 5),
Left([TextDateField], 2))

Use that query as the basis for an Append To query to put the data into your
existing table.