Change data with a Query

[sislman01]

Hi,

I'm new to this so please bear with me.

I have a Table consisting of data thats been imported from CSV.

Dates contained with in the data are in text format i.e 12122009 (English date format).

I can convert these to dates unless the first digit is Zero. Access seems to not show the Zero so I get i.e. 1122009 rather than 01122009.

Is there anyway I can run a query that adds a Zero to the start of the text if the Text is only seven charaters in length?

I would then be able to convert all.

Thanks

Matt

 

[DCrake]

First of all create a new query with the following additional column

DDate:Right("0" & [ActualDateField],8)

This will pad out any leading zeros

use this instead of the original field for conveting the text to date

David

 

[namliam]

The zero is dropped because the field is imported as a number field.

Import it as a text field and your zero should stick around.

 

[sislman01]

Thanks guys that helped big time.

regards

matt