Date Field Names imported from Excel renamed as numeric :-(

[pbassett]

I had a new PC installed on my desk at work and have jumped from Office 2000 to 2002, and a weekly procedure I run is no longer working correctly

I import an Excel sheet with Field Names that are Dates. Unfortunately, the Import Spreadsheet wizard brings them into Access fields as a number such as 38109 for 5/2/2004. I can't remember if I had to change something on the old PC way back when, but in Access 2000 the field names were correct. I looked in Tools->Options but to no avail.

Can I change a setting so that field names stay as dates on import?

Thanks,
Pete

 

[Norie]

Can I ask why you are using dates as field names?

It seems a little unusual?

How is the Excel spreadsheet set up?

 

[pbassett]

Re: Date Field Names imported from Excel renamed as numeric

Each week I get a table listing employees' weekly hours worked from 5/2/2004 to the present. It grows by 1 Field (the new week) weekly. Here is the schema:

[All Text]
Last
First
Mgmt Type
Staff Type
Mgr
[All Number]
5/2/2004
5/9/2004
.
.
.
9/26/04

I like the date as a field name because I have a macro to take this pre-Hours table and turn it into a post-Hours table that has the correct format to be imported into Access for reports, as below:

Last First Mgmt Type Staff Type Mgr Date Hours
Smith John SUPV STAFFA Jones 5/2/2004 68.25
Smith John SUPV STAFFA Jones 5/9/2004 68
Smith John SUPV STAFFA Jones 5/16/2004 60.5
Smith John SUPV STAFFA Jones 5/23/2004 90
Smith John SUPV STAFFA Jones 5/30/2004 50
Smith John SUPV STAFFA Jones 6/6/2004 52
Smith John SUPV STAFFA Jones 6/13/2004 70.5
.
.
.

 

[Norie]

So you are actually transforming the imported data into your last sample using a macro.

Why not within that macro change the field name back to a date?

 

[pbassett]

Re: Date Field Names imported from Excel renamed as numeric

I am resigned to do that now that I can't find a way to retain the date literal as the Field Name.

Is there a macro function to convert a serial format date as 38109 to its date equivalent 5/2/2004 other than invoking the Excel function
=TEXT(Var,"m")&"/"&TEXT(Var,"d")&"/"&TEXT(Var,"yyyy")?

Thanks,
Pete

 

[Norie]

Where are you trying to convert the date?

If it's in Access try this

DateAdd("d", 38109, 0)

Seems to work.

 

[pbassett]

Re: Date Field Names imported from Excel renamed as numeric

Thanks! Much better method.

Also, when I imported the "Charged Hours" spreadsheet, the date in the Import Wizard looked like "38109" - oops! It looked like "5/2/04" in the spreadsheet.

But not to worry, it correctly filled the Date field in Access as a proper date format. Whew!

Pete