Converting date format

[rockocubs]

I have imported a text file to an access table i have some date field
in yyyymmdd format so like this 20051006 , I imported everything as
text field in the table. i want to apend to another table but int
date format fields, i want to get to a format of mm/dd/yy format. I
there an easy way to do this. I have tried just the append to
time/date field with no format for starters and the date come in a
null.Oh this access 2000

 

[Van T. Dinh]

Use:

DateSerial( Left([DateStr], 4), Mid([DateStr], 5, 2), Right([DateStr],
2) )

to convert your date string to a date value. You can then format the date
value to whatever format you need.

 

[rockocubs]

Not sure where to put the code you entered at

 

[rockocubs]

I got it to work with access 97 at work, so will try it when i ge
home on my access 2000. Thanks for the help

 

[Van T. Dinh]

In your Append Query like:

INSERT INTO DestTable ( ..., DateField, ... )
SELECT ..., DateSerial( Left([DateStr], 4), Mid([DateStr], 5, 2),
Right([DateStr], 2) ), ...
FROM SourceTable

Check Access Help on Append Queries.

 

[John Vinson]

On Wed, 02 Nov 2005 19:45:06 -0600,

I have imported a text file to an access table i have some date fields
in yyyymmdd format so like this 20051006 , I imported everything as a
text field in the table. i want to apend to another table but into
date format fields, i want to get to a format of mm/dd/yy format. Is
there an easy way to do this. I have tried just the append to a
time/date field with no format for starters and the date come in as
null.Oh this access 2000.

In your Append query, use a calculated field:

DateSerial(Left([textdate], 4), Mid([textdate], 5, 2),
Right([textdate], 2))

and append this to the Date/Time field. You can use the same
expression in an Update query if the records already exist in your
target table.

John W. Vinson[MVP]