TRIM field in a Table Design View in MS Access

[Valeriya]

Hi guys. I'm having trouble with the Excel linked Table:

1.Have an extract from ERP system saved as an Excel file.
2.Created a Macro in Excel to format everything as a TEXT plus insert a TRIM function to assure that any trailing spaces are removed.
3.So the formatted Excel spreadsheet is linked to my Access database.

However, it seems like it needs to be trimed twice.Because when I'm trying to Query this table, all the numeric values appeared as blanks. So for the testing purpose I've created a Make Table Query with the TRIM function again and it worked!

Wondering if there is a way to avoid those extra steps and resolve it either programmatically or insert a TRIM function in a Design View of that linked Table?

Please help,

And thank you in advance!

Valeriya

 

[lameid]

In my experience, Linked Excel tables are at best a major annoyance.

If at all possible I would export to a text file and either link to or import it.

As far as fixing up your data, just change your maketable query back to a select query. Then use it as the source of everthing you would use the table for. Quick and dirty fix is to change the name of the link and modify the query accordingly. Finally change the name of the query to the old name of the link.