how can I get a selction where the source is a combi of letters and numbers

[Dick60]

Hi, I have a table (named 1) with a combi of letters and numbers (project 1000, project 1002) ect. this data is added by human and imported from another application.


I have also a table (named table 2) with a source with correct numers (only numbers). I want to compare table 2 with the table 1 data and that is shows all records in table 1 that do not match table 2 and place the results in a new table that has to be generated (table 3).

Extra info, the field is a txt field in both tables!

Hope someone can help me

[JoeM]

So, in table 1, you need to separate the number from the letters in order to link in to table 2 to do your check. Is that right?

If so, you can do that in a query. You can use a myriad of text functions in order to do that. If you need help with that part, please list all the different "structures" we that may exist (i.e. is there always one word and then one number, or are there other possibilities)?

Once you have done that, you can use use this query in a new query with table 2 doing an unmatched query to find the unmatched records. Access even has an Unmatched Query Wizard to walk you through the process.

If you would like to write the results to a new or existing table, you would use an Action Query in order to do so (a Make-Table or Append Query).

[Dick60]

This is right I am only interested in the last 4 numbers. The only structure in use is:
project 1234 (the word Project+space)

So, in table 1, you need to separate the number from the letters in order to link in to table 2 to do your check. Is that right?

Can you help me with the myriad of text function option?


If so, you can do that in a query. You can use a myriad of text functions in order to do that. If you need help with that part, please list all the different "structures" we that may exist (i.e. is there always one word and then one number, or are there other possibilities)?


Once you have done that, you can use use this query in a new query with table 2 doing an unmatched query to find the unmatched records. Access even has an Unmatched Query Wizard to walk you through the process.

If you would like to write the results to a new or existing table, you would use an Action Query in order to do so (a Make-Table or Append Query).

[JoeM]

Then you should be able to use the RIGHT function to get what you need:
Right([Field Name],4)