Conversion of Data Type

[jesusoneez]

I have a large database (so large in fact that it should probable be converted to a SQL backend), that was developed a few years ago. The tables import data from CSV files generated by our mainframe. This data contains dates (in the format "YYYYMMDD", so Jan 1st 2001 for example, would look like "20010101").

Not knowing at the time that I could import dates in any format I desired, I set the tables to import these dates as an 8 digit text string. Which is fine...until I need to use the dates as dates!

I've tried converting them to Date/Time fields and putting a format in of "yyyymmdd", to no avail. Access simply empties out the date field in all my records. I realise that Access is not seeing the text as "numbers" so this is probably why it's having a problem converting.

Can anyone suggest anything?

Hope I've made this reasonably clear!

JOE

 

[Mile-O]

MyFixedDate: DateSerial(Left([DateField], 4), Mid([DateField], 5, 2), Mid([DateField], 7, 2))

 

[jesusoneez]

Thanks...I see how that works, but I'm not sure where or how I would implement it...I'm not too good on the coding side of things...

Ste

 

[Mile-O]

Thanks...I see how that works, but I'm not sure where or how I would implement it...I'm not too good on the coding side of things...

No code necessary.

Base a query off the imported data and ensure that you create a new field with the expression written above based on the field with the ISO standard date.

 

[jesusoneez]

Smashing. That worked a treat. Thanks for your help!

Ste