Converting Excel Dates

[blair.christopher]

I'm dealing with a very poorly constructed excel file that I need to
import into access. There are a lot of fields that are supposed to
contain dates, but a lot of them have dates plus text. I need the
process to be automated, so to avoid errors I had to read everything as
text. Now I need to convert the dates, which got read in as numbers
(like 38995), to dates. Is there a way to do this in a SQL query or in
VBA? Thanks for your help.

 

[Jeff Boyce]

One approach to doing this would be to use a query.

First, you'd need to try to convert the contents of the field to a date ...
but as you say, some are not even dates!

You could have Access attempt to read each as a date, and, if it is, convert
it to a Date/Time type, otherwise, do nothing.

I'd probably first try something like:

IIF(IsDate([ThisField]),CDate([ThisField]),Null)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[blair.christopher]

Thanks for helping on this. The CDate portion works, but IsDate won't
work because at that point Access sees it as just a number, so it never
returns true. Is there some easy function that checks to see if the
field contains a 5 digit number?

One approach to doing this would be to use a query.

First, you'd need to try to convert the contents of the field to a date ...
but as you say, some are not even dates!

You could have Access attempt to read each as a date, and, if it is, convert
it to a Date/Time type, otherwise, do nothing.

I'd probably first try something like:

IIF(IsDate([ThisField]),CDate([ThisField]),Null)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm dealing with a very poorly constructed excel file that I need to
import into access. There are a lot of fields that are supposed to
contain dates, but a lot of them have dates plus text. I need the
process to be automated, so to avoid errors I had to read everything as
text. Now I need to convert the dates, which got read in as numbers
(like 38995), to dates. Is there a way to do this in a SQL query or in
VBA? Thanks for your help.

 

[Jeff Boyce]

Check a couple of the other Access functions out. Maybe IsNumeric(), or
Len()...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for helping on this. The CDate portion works, but IsDate won't
work because at that point Access sees it as just a number, so it never
returns true. Is there some easy function that checks to see if the
field contains a 5 digit number?

One approach to doing this would be to use a query.

First, you'd need to try to convert the contents of the field to a date
...
but as you say, some are not even dates!

You could have Access attempt to read each as a date, and, if it is,
convert
it to a Date/Time type, otherwise, do nothing.

I'd probably first try something like:

IIF(IsDate([ThisField]),CDate([ThisField]),Null)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm dealing with a very poorly constructed excel file that I need to
import into access. There are a lot of fields that are supposed to
contain dates, but a lot of them have dates plus text. I need the
process to be automated, so to avoid errors I had to read everything as
text. Now I need to convert the dates, which got read in as numbers
(like 38995), to dates. Is there a way to do this in a SQL query or in
VBA? Thanks for your help.

 

[Guest]

Perhaps you can modify the Excel data in Excel to conform to your
requirements? There are all manner of XL functions which can manipulate text.

Dave
--
Brevity is the soul of wit.

Check a couple of the other Access functions out. Maybe IsNumeric(), or
Len()...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for helping on this. The CDate portion works, but IsDate won't
work because at that point Access sees it as just a number, so it never
returns true. Is there some easy function that checks to see if the
field contains a 5 digit number?

One approach to doing this would be to use a query.

First, you'd need to try to convert the contents of the field to a date
...
but as you say, some are not even dates!

You could have Access attempt to read each as a date, and, if it is,
convert
it to a Date/Time type, otherwise, do nothing.

I'd probably first try something like:

IIF(IsDate([ThisField]),CDate([ThisField]),Null)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm dealing with a very poorly constructed excel file that I need to
import into access. There are a lot of fields that are supposed to
contain dates, but a lot of them have dates plus text. I need the
process to be automated, so to avoid errors I had to read everything as
text. Now I need to convert the dates, which got read in as numbers
(like 38995), to dates. Is there a way to do this in a SQL query or in
VBA? Thanks for your help.