Capture all but 1st 2 characters (1 Viewer)

[brucesilvers]

Is it possible to capture all but the left two characters of a text field when the text strings are of differing lengths?

Details. I am working with Product numbers for a company where Product numbers vary in length. When products become inactive, they remain in the database (out of necessity) and are added as additional records with "i " in front of the old product number. For example, if 497BK goes inactive another record is added with a product number of i 497BK. What I am trying to do is match a list of "i" products with their non-"i" counterparts so that I can capture the other fields from a non-"i" record and append them to a table with the "i" version of the product number.

Example. The following record exists in the Brands table I'm working with:

Product # Brand #
497BK 20201234

The following item appears in my query that identifies products that need to be added to that Brands table:

i 497BK

I would like to create an append query that will match 497BK with i 497BK, and then append the following record to that table:

Product # Brand #
i 497 BK 20201234

Thanks for any help you can give on this!

 

[pcs]

if you Always want to ignore the 1st two characters..

add an expression to your query like the sample below:

Expr1: Mid$([tblYourTable]![YourField],3,Len([tblYourTable]![YourField]))

hth,
al

 

[brucesilvers]

Thanks!

That worked perfectly for what I needed. I guess I understood the Left/Right/Mid/Len functions but didn't realize you could combine them in that way. Thanks again!