Using LIKE in a Parameter Query - Access 2000

[GladGad]

I am not a real well versed query writer and therefore am having some problems figuring out how to write a parameter query where I want to input a partial name. I have tried a few different things, but either get no results or all of the records in BANKS are returned.

I am using Access 2000 on XP. I am using three tables for this query:

BANKS
  BankID (pk)
  BankName
  Other fields that are not pertinent to this query

VENDORS
  VendorID (pk)
  VendorName
  Other fields that are not pertinent to this query

BANKVENDORS
  BankID
  VendorID (both fields combined make up the pk)

I want to list the BankName and VendorName based on VendorName. I am using BANKVENDORS to link the two tables. Since the VendorName is generally long (i.e. Federal Reserve Bank of Atlanta), I want the user to be able to enter only a portion of the VendorName (i.e. Atlanta) and have the query return all Banks and Vendors where the VendorName contains the word Atlanta.

Here is what I have so far in the query (I used the Wizard to create it):

SELECT Banks.BankName, Vendors.VendorName
FROM Vendors INNER JOIN (Banks INNER JOIN BankVendors ON Banks.BankID = BankVendors.BankID) ON Vendors.VendorID = BankVendors.VendorID
WHERE (((Vendors.VendorName)=[Enter Vendor Name]));

Thanks for any help you can provide.

-Carolyn

[FishVal]

Hi, Carolyn.

Try

SELECT Banks.BankName, Vendors.VendorName
FROM Vendors INNER JOIN (Banks INNER JOIN BankVendors ON Banks.BankID = BankVendors.BankID) ON Vendors.VendorID = BankVendors.VendorID
WHERE (((Vendors.VendorName) ='*' & [Enter Vendor Name] & '*'));
 

or, if SQL syntax option in your db is set to ANSI92, then

SELECT Banks.BankName, Vendors.VendorName
FROM Vendors INNER JOIN (Banks INNER JOIN BankVendors ON Banks.BankID = BankVendors.BankID) ON Vendors.VendorID = BankVendors.VendorID
WHERE (((Vendors.VendorName) ='%' & [Enter Vendor Name] & '%'));
 

Regards,
Fish

[NeoPa]

I think you need to replace the "=" with "LIKE" in Fish's examples.

SELECT Banks.BankName,
       Vendors.VendorName
FROM Vendors INNER JOIN (Banks INNER JOIN BankVendors
  ON Banks.BankID = BankVendors.BankID)
  ON Vendors.VendorID = BankVendors.VendorID
WHERE (Vendors.VendorName LIKE '*' & [Enter Vendor Name] & '*')

If you're using Access 2000, it will only support ANSI89 I think.

[FishVal]

Ooops.
Certainly "=" is a typo. It should be "LIKE".
Thanks NeoPa.

[NeoPa]

No worries my friend. We're all part of a good team :)