Number as date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DKS - 29 Oct 2007 20:58 GMT

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

Reply to this Message

HELENA SMITH - 30 Oct 2007 04:17 GMT

sweet37helen@google.com hi are you

> Hi,
>
[quoted text clipped - 13 lines]
>
> Many thanks in anticipation

Reply to this Message

Jeff Boyce - 30 Oct 2007 13:14 GMT

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))

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

> Hi,
>
[quoted text clipped - 13 lines]
>
> Many thanks in anticipation

Reply to this Message