Date-like headers (1 Viewer)

[bobfin]

We get an Excel spreadsheet every month from a client. Most of the columns have names like "Sep-01", "May-05", etc. Those columns contain the dollar amount received during that month. The problem comes when we import the data into an Access 2003 table. The Import Spreadsheet Wizard only samples the first 24 rows, and if a column has no value in those rows, the fieldname becomes something like "1/5/02 0:00:00" (instead of "May-02") and the data type becomes Text instead of Currency. How do we stop this from happening? Formatting the spreadsheet columns from General to Currency modifies the header names and importing into a pre-formattted table doesn't work.

 

[Jayanand]

If you will put (by adding one comma) like 'Mar.02 it will not change.

 

[bobfin]

It didn't work. The client had actually used dates for the headers in a customized format (mmm-yy). We made a workaround by inserting a dummy row 2 and filling the cells with "$0". Then the Import Wizard gives all the Access fields the proper names and data types. Thanks for your input - we'll keep that in mind for future reference.