Linked Excel Table Field Name Issue

[Abraxus]

A coworker ran into something interesting and I'm seeing if anyone has a solution.

He has several tables that are links to Excel files.

In the tables were field names with a pound (#) sign. (Book #, for example)

When attempting to link the tables in a query using those fields, he gets an error about not finding that field.

Is there a way to link tables with that pound sign in the field name, or will the field need to be renamed?

Thanks!

[HansV]

If he refers to the field as [Book #] it should work as far as I know.
But it's much better to avoid spaces and unusual characters in column headers (field names) in Excel if one intends to import or link the data into Access. It'll save a lot of hassle.
So I would recommend renaming the column.

[Abraxus]

In the Relationships builder, it won't let you modify the field name to add the brackets.

In a query builder, it didn't object to the pound sign in the field name.

Just an Access oddity, I suppose...

[Wendell]

I think the issue involves the "space" between the K and the # as well - see this rather old article on the subject - https://support.microsoft.com/en-us/hel ... -databases. Bottom line is to rename the column in Excel, or import the table and then rename it in Access.

[CData]

Rather than work directly with the linked excel; instead make a query of that excel which includes all fields. Then alias the field name i.e.
BookNumber: Book #

Then work instead with this query for whatever you need to do rather than the excel table itself.