Wildcard on a field in IIF statement

[gkrenton]

I would think there's a way to do this but for the life of me can't stumble upon the correct syntax.

Am trying to select records where the beginning part of the record is held in another field - UPC code data. I'm only concerned with pulling the record if the first 6 digits of the code resides in the VendorCode file.

So here's what I'm trying to do
Here's the total code in the UPC field - 084084857149
The vendorcode field = 084084

If I wasn't trying to pull this data from antoher field the statement would look like

iif ([UPC] like 084084*, [upc],0) but that limits me to having to update the 'like' statement anytime we add a new manufacturer code. Since it already resides elsewhere seems like I should be able to do something like

iif ([UPC] like [vendorcode]*, [upc],0) but how does one wildcard a lookup against another field? [vendor_code]* isn't it, nor is [vendor_code*] isn't either.

Anyone know how to overcome this seemingly easy comparison?

 

[PHV]

One way:
In the criteria cell of vendorcode: =Left([UPC],6)

Another way:
In the criteria cell of UPC: Like vendorcode & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cranebill]

Select * from UPC_Table
where Left([UPC],6) in (Select vendorcode from vendor_table);

 

[gkrenton]

All these work. Thanks - I knew there had to be a way just wasn't seeing it.

- gina