Joining Tables where 1 field has an extra character

[smoothlarryhughes]

For example...if I have a field called Trade ID and one of the tables all the Trade ID's have an H and then 7 digits, and the other table the trade ID's just have the 7 digits...can these fields be joined, or do i need to query first to remove the H from the first table?

Example H1234567 vs 1234567

[JoeM]

Yep, create a query first that removes the first letter. Then in a new query, you can link this query with your other table.

[smoothlarryhughes]

i figured this was the way to go...wasn't sure if there is a way to cut the H off while uploading to the table via vba.

[JoeM]

i figured this was the way to go...wasn't sure if there is a way to cut the H off while uploading to the table via vba.

Depending on how you are doing it, there may be. If you are importing by recordsets, record-by-record, field-by-field, you can probably modify the field right in your code.
If you are just importing using something like TransferText or TransferSpreadsheet in VBA, then I don't think so.

If you are unsure, you can always post your code and let us take a look at it.

[smoothlarryhughes]

I'm planning on using transferspreadsheet to populate the tables, as the tables are reports exported from another system. Another problem I have with the reports this system generates is that it puts alot of extra spaces at the end of the cells, so i have to trim everything prior to uploading into the table. Another thing that would be nice to do in VBA automatically if possible.

[JoeM]

You need to use much more advanced VBA than what you are using to do it. Transferspreadsheet isn't going to cut it. You may need to use Recordsets where you loop through each record and field.

Here is a workaround I might suggest that might get you where you need to go without too much pain (if you are not familiar with working with ADO or DAO Recordsets in VBA). Export your Excel file to a Fixed Width text file. Then you can import that file into Access. The nice thing about that, is you get to tell it exactly where to start, so with your ID field, you can "lop off" the first letter on import. Also, when you import text files, I believe that all trailing spaces are auotmatically dropped without you have to do anything to them.

If the Fixed Width file does not sound like an option to, perhaps look at setting up some Update Queries with the TRIM function to fix your data. If you have a series of them, you could create a Macro and run them all at once (simply by using the OpenQuery action, which you can use in VBA).