Access – appending from two sources using unique multi-field ID. 1 field in 1 source formatted differently with leading 0s or letter

I am making updates to a database and am stuck with this problem. Appending to a destination with a unique two-field ID of, for example, field1 and field2.

One source table, a ODBC linked table, has field1 text with field size of 8. Second source table is linked xlsx file and is imported with field1 as text with field size of 255.

field1 values in both sources contain the same string, but in the xlsx linked table some values have leading zeros(one or two of them) or a leading W. I am trying to remove the leading characters so the field from both sources match up for the unique ID.

I have been researching how to trim leading zeros or characters, but with the combination of working from a linked xlsx table and trimming both numbers and letters I keep getting stuck. Since I run these queries each month I’m thinking it is most simple to create another table for modified strings to add and delete from that I can use to append to the destination table.

I found CLng function works on leading 0s but not leading Ws, those return #Error in the field. I was trying to create minimal new objects. Will I need to build two queries to trim leading characters, one for 0s and one for Ws?

EDIT I tried nesting a replace function within the conversion function but it is prompting for Enter Parameter Value for “W”. I missed something. See code below:

SELECT Bag.field2, Bag.field1, CLng(Replace(([Bag]![field1]),“W”,“”)) AS [Edited Number], 
Bag.[Print Year], Bag.[Book Type], Bag.[Returned Date], Bag.[Return Checked In Status], 
Bag.Status, Bag.[SV Book], Bag.Destroyed, Bag.[Last Updated Date]
FROM Bag;

Sample data and desired output as follows:

Starting Table	Ending Table
00123456	123456
09876543	9876543
W3456789	3456789

Answer

Can nest string manipulation and conversion functions. Consider:

CLng(Replace(field, "W", ""))

Advertisement

Answer