Query not picking up fields with text and numbers.

[berninc]

I have 2 tables with a list of part numbers in each.

Table 1 (over 1.4m records) is my master parts number table with part number as a field and a standard cost as the other field.

Table 2 is a list of part numbers (only a couple thousand) that will change monthly. These part numbers are listed in table 1 and have standard cost values.

I'm trying to run a query to basically lookup the part number from Table 2 and return the standard cost value found in Table 1. This query is working great for part numbers containing only number (ex. 2074849). The query is not returning standard costs for parts containing numbers AND text (ex. 1A0621). Both Table 1 and Table 2 have primary keys set to the part number, and have a relationship setup between the part number. The data type on both tables is set to "Text".

How can I get the part numbers containing both numbers and letters/text to return my standard costs?

P.S. If I do a "find" in my Table 1 I can find these parts and see their standard costs, so I know they are in there and are correct.

Thanks!

[JoeM]

Can you post the SQL code of your query, and a small sampling of alphanumeric records in each table which should match, but aren't?

If it is working for the all numeric entries, but not for the alphanumeric entries, what that tells me is that while it may look like there are matching alphanumeric entries in each table, they may, in fact, not actually be the same. Look for extra spaces or special characters on the end of these entries. The LEN function is a handy function to use to check this.

[berninc]

Thanks for the LEN function, that found my problem. All of the alphanumeric part numbers have an extra space before the number. Question now is, how to get rid of that extra space on those alphanumeric numbers? There are clost to 50,000 that are alphanumeric, don't really want to go one by one and change them!

[JoeM]

Use an Update Query.

Just add that field to the query, and enter this in the criteria line:
Left([FieldName],1)=" "

Then in the Update To row, enter this formula:
Trim([FieldName])