By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Using LIKE in a Parameter Query - Access 2000

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1. BANKS
  2.   BankID (pk)
  3.   BankName
  4.   Other fields that are not pertinent to this query
Expand|Select|Wrap|Line Numbers
  1. VENDORS
  2.   VendorID (pk)
  3.   VendorName
  4.   Other fields that are not pertinent to this query
Expand|Select|Wrap|Line Numbers
  1. BANKVENDORS
  2.   BankID
  3.   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):
Expand|Select|Wrap|Line Numbers
  1. SELECT Banks.BankName, Vendors.VendorName
  2. FROM Vendors INNER JOIN (Banks INNER JOIN BankVendors ON Banks.BankID = BankVendors.BankID) ON Vendors.VendorID = BankVendors.VendorID
  3. WHERE (((Vendors.VendorName)=[Enter Vendor Name]));
Thanks for any help you can provide.

-Carolyn
Feb 2 '08 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, Carolyn.

Try
Expand|Select|Wrap|Line Numbers
  1. SELECT Banks.BankName, Vendors.VendorName
  2. FROM Vendors INNER JOIN (Banks INNER JOIN BankVendors ON Banks.BankID = BankVendors.BankID) ON Vendors.VendorID = BankVendors.VendorID
  3. WHERE (((Vendors.VendorName) ='*' & [Enter Vendor Name] & '*'));
  4.  
or, if SQL syntax option in your db is set to ANSI92, then

Expand|Select|Wrap|Line Numbers
  1. SELECT Banks.BankName, Vendors.VendorName
  2. FROM Vendors INNER JOIN (Banks INNER JOIN BankVendors ON Banks.BankID = BankVendors.BankID) ON Vendors.VendorID = BankVendors.VendorID
  3. WHERE (((Vendors.VendorName) ='%' & [Enter Vendor Name] & '%'));
  4.  
Regards,
Fish
Feb 2 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
I think you need to replace the "=" with "LIKE" in Fish's examples.
Expand|Select|Wrap|Line Numbers
  1. SELECT Banks.BankName,
  2.        Vendors.VendorName
  3. FROM Vendors INNER JOIN (Banks INNER JOIN BankVendors
  4.   ON Banks.BankID = BankVendors.BankID)
  5.   ON Vendors.VendorID = BankVendors.VendorID
  6. WHERE (Vendors.VendorName LIKE '*' & [Enter Vendor Name] & '*')
If you're using Access 2000, it will only support ANSI89 I think.
Feb 5 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Ooops.
Certainly "=" is a typo. It should be "LIKE".
Thanks NeoPa.
Feb 6 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
No worries my friend. We're all part of a good team :)
Feb 6 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.