You can do the calculated join in SQL view but will not be able to work it
thereafter in design view.

FROM TableA LEFT JOIN TableB ON TableA.ContractorKey =
Left(TableB.[pricelist], InStr(TableB.[pricelist], "_")-1)

--
Build a little, test a little.


"accesswanabe" wrote:

I am trying to provide a SQL query to the rowsource of a combobox.

I am trying to use two tables to do this but am not sure how to go about it.
The table for the combobox list is a contractor list and has two fields
pertinent to what I need to do; ContractorName and ContractorKey. A
ContractorKey data example is: USPipe. The other table is a pricelist
table, and has a price item key on every record. An example of the pricelist
table key is: USPipe_01_01A0.

Not every contractor has price items. What I would like to do is join the
tables so that the contractor list shows only contractors that have related
price list records. Is there a way to do that without using an intermediate
query by joining on a calculated field or something like that?

Thanks in advance!!