converting a date

[LG]

I exported a text file into access with a link table. I then created a make
table query. When the data comes from our system is shows as 20080401. How
do I convert it in the DB to read 4/01/2008?

 

[LG]

I have tried it many ways. I opened up the tble in design view added the
second column for each with date/time.
Went to the query new pulled in the 2 fields the old text and the new
date/time
I entered what you have below in the criteria for the new field and got
invalid expression and than flipped it and got the same error.
Any suggestions?

Open the table that was created and check the field datatype before doing
anything. If it is a text field then you need to insert a new field that is
datatype DateTime. Then run an update query to move and convert the text
data to a date.


DateSerial(Left([YourTextData],4),Right(Left([YourTextData],6),2),Right([YourTextData],2))

I exported a text file into access with a link table. I then created a make
table query. When the data comes from our system is shows as 20080401. How
do I convert it in the DB to read 4/01/2008?

 

[KARL DEWEY]

I entered what you have below in the criteria for the new field and got
invalid expression
What I posted was not criteria. Why would think it was criteria for the new
field as it has not data?
What I posted was for you to use in an Update query to convert the old field
text to DateTime for the new field.
Make sure you use the name of your old text field where I put 'YourTextData'
in the expression.

I have tried it many ways. I opened up the tble in design view added the
second column for each with date/time.
Went to the query new pulled in the 2 fields the old text and the new
date/time
I entered what you have below in the criteria for the new field and got
invalid expression and than flipped it and got the same error.
Any suggestions?

I then created a make table query.

Open the table that was created and check the field datatype before doing
anything. If it is a text field then you need to insert a new field that is
datatype DateTime. Then run an update query to move and convert the text
data to a date.


DateSerial(Left([YourTextData],4),Right(Left([YourTextData],6),2),Right([YourTextData],2))

I exported a text file into access with a link table. I then created a make
table query. When the data comes from our system is shows as 20080401. How
do I convert it in the DB to read 4/01/2008?