Number as date

[Guest]

Hi,

I am importing data from .xls to .mdb

One of my columns in .xls is a date field where the contents of the data
respect the format yyyymmdd but it specified as "number" format in .xls. So
for example today's date would be noted as the number 20071029. Note also
that single digit dates or months are always noted as two digits with a
leading zero, thus Nov 1 2007 will be 20071101.

How can I get access to interpret that data as "date" format?

If impossible then how could I write a query that takes all columns of the
imported data, and then I added a computed column that transforms this number
into a date.

Many thanks in anticipation

 

[HELENA SMITH]

(e-mail address removed) hi are you

 

[Jeff Boyce]

Take a look at Access HELP on the DateSerial() function, and the Left(),
Right() and Mid() functions.

Use a query to "parse" the data in that column/field in that table. You'll
add a new expression/field in the query that looks something like:

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

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/