Import excel date into column header - problems

[Guest]

I have created a linked excel table in my access database. The information
being linked includes calculated dollar amounts sorted by date. Each column
is assigned a specific month (column headers), and the months are calculated
based on user input. (So if the user enters Jan. 06 as the start month, the
ensuing columns are Feb. O6, Mar. O6 etc.)

When I link this table back into Access, I select the "First Row Contains
Column Headings" option. However, when I look at the data in access the
column headings that have a calculated date look like cell references (F48,
F49, F50, etc.) I don't this these refer to the excel cells however, as my
dates are all on Row A.

I would like the column headings to show the date, as I will use that
information in a report. Any guidance would be greatly appreciated!

 

[John Nurick]

Hi Steve,

I presume that the column headed F48 is the 48th column in the table. If
so, what's happening is that Access is subsituting default field names
(F1, F2...) where a column header Excel is not a valid field name.

If you're calculating dates in Excel to get these column headers, the
actual values in the cells are numeric (just formatted as dates), and
field names can't be numbers.

Try using the Excel TEXT() function to display the values as text rather
than numbers-formatted-as-dates.

 

[Guest]

Hi John,

Thank you for the suggestion. As I need to use the dates in my calculations,
I ran them on another sheet so my calcs can still refer to them. Then I used
the text function in the first row of the linked sheet. Worked like a charm.

Steve