Query using criteria from non-related table

[ukdilbert]

I have what will hopefully be a simple question to resolve:

I want a query selecting records from table1 based upon a value in table2 -

table1 contains thousands of records - fields include VendorNo and VendorName

table2 contains a single record - fields include VendorSearchName (which is a "short name")

I am trying to create a query which lists all VendorNo and VendorName entries where VendorName contains VendorSearchName

eg. VendorSearchName = "Fred" will list VendorNames including "Fred Flinstone", "Big Fred" etc

Any help much appreciated.

 

[plog]

I would do a DLookup (http://www.techonthenet.com/access/functions/domain/dlookup.php) into table2 to get that string and use it in the WHERE clause.

Using the names you provided, this would be your WHERE clause:

WHERE (((table1.VendorName) Like "*" & DLookUp("VendorSearchName","table2") & "*"));

Note: That is case-insensitive and works as expected as long as you only have 1 record in table2.

 

[BlueIshDan]

Something like this? If I understand correctly?

SELECT table1.* 

FROM 
	table1,
	(
	 	SELECT VendorSearchName AS [search] 
		FROM table2
	) AS [search_query]

WHERE table1.VendorName Like '*' & search_query.search & '*'

 

[ukdilbert]

Thank you both - much appreciated.