Like Query... Maybe???

[sundance0000]

I need some help. I have a spreadsheet (DD.xls)of data with part ids on it that need to be compared to my sql table (dbo_part) to update prices. Problem arises from the fact that no one knows what to do with leading zeros so some items have them and some don't.


i.e.
dbo_part.id = RE-006392843
dd.partid = 0006392843

The prefix RE- is our addition to identify the vendor and usually I just concatenate this on when prepping the spreadsheet before import to access but now my problem is that I don't know how many zeros were used by the people inputting. And, we're talking about 1k to 2k of part ids.

Anyone know how I can do a comparison or like? It may sound dumb but what I would like to do is just search for the ending part of the id because they match and forget all the beginning fluff.
Sort of like

If dbo_part.id is like dd.partid

Is there any way possible to do this?

Any help would be appreciated. Also if you need more info, let me know.

[alansidman]

I would bring both tables into access if you already have not done so. Open a query for the first table and bring the part id into the grid. Create an expression in field that is 00000 & [idField]. Or some number of zeros that you deem appropriate to make this work.

Now determine how long you want the part number field to be and in an adjacent field type and expression Right([idField], Number of characters you want the field to be). Either replace the id field with this one or create an update query to update the table.

Now do the same thing for the other table and your id's will be the same length. Additionally, you may want to split out the vendor code before doing this.

Alan