Report based on a finding a substring in a field

[2Grandpa]

One table in my database has information I down load from a mainframe database on manufacturing information. A field named “Part Number” has part numbers but also contains codes for the manufacturer and other junk. There is no fixed length or layout for this information. My user has a list of thousands of part numbers and wants a report of the information where his part numbers appears. He wants to this report on a recurring basis.

Since the list of part numbers change, I wanted to link to the spreadsheet which contains the part numbers.

Any help would be appreciated.

 

[Remou]

You can do just that. Choose File->Get External Data->Link Tables.

 

[2Grandpa]

Linking the table is easy. The part I could use help on is matching the part numbers, from the spreadsheet, with a substring of the part number field in the database table.

 

[Remou]

Something like this:

[tt]SELECT xl.Field1, Table2.Field1
FROM Table2, xl
WHERE (((Table2.Field1) Like "*" & [xl].[field1] & "*"));[/tt]

 

[2Grandpa]

Thanks for your suggestion Remou. I decided to take your suggestion and incorporate in the Access query definition.
I linked the spreadsheet and added it to the query without linking it to the other tables. The definition looked like this:

CSIPart: IIf([PART_NUMBER] Like '*' & [CSI Parts List]![CSIPartNumbers] & '*',"Yes","No")

I used Yes as the selection criteria and it works.