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

Help with wildcards

P: 15
hi guys.. i want to include a search form in my database.. here is my current SQL statement:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblCompany.CompanyIDPK, tblCompany.CompanyName, tblCompany.CompanyAddress, tblCompany.ContactPerson, tblCompany.ContactPosition, tblCompany.OfficeNo, tblCompany.MobileNo, tblCompany.FaxNo, tblCompany.EmailAdd FROM tblCompany WHERE (((tblCompany.CompanyName)=[Forms]![frmSearchClient]![txtSearchFor]));
  2.  
but this statement only returns the exact name of the company... if the name of the company in the database is ABC company and you searched for "ABC", it will return zero results... so how can i improve on this? that even if they enter only "ABC", it will return a result.

thanks a lot and good day...
Edit/Delete Message
Oct 25 '06 #1
Share this Question
Share on Google+
7 Replies


Expert 5K+
P: 8,434
hi guys.. i want to include a search form in my database.. here is my current SQL statement:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblCompany.CompanyIDPK, tblCompany.CompanyName, tblCompany.CompanyAddress, tblCompany.ContactPerson, tblCompany.ContactPosition, tblCompany.OfficeNo, tblCompany.MobileNo, tblCompany.FaxNo, tblCompany.EmailAdd FROM tblCompany WHERE (((tblCompany.CompanyName)=[Forms]![frmSearchClient]![txtSearchFor]));
  2.  
but this statement only returns the exact name of the company... if the name of the company in the database is ABC company and you searched for "ABC", it will return zero results... so how can i improve on this? that even if they enter only "ABC", it will return a result.

thanks a lot and good day...
Edit/Delete Message
Here's probably the simplest, not certain how well it will work...
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCompany.CompanyIDPK, tblCompany.CompanyName, tblCompany.CompanyAddress, tblCompany.ContactPerson, tblCompany.ContactPosition, tblCompany.OfficeNo, tblCompany.MobileNo, tblCompany.FaxNo, tblCompany.EmailAdd
  2. FROM tblCompany
  3. WHERE (((tblCompany.CompanyName)
  4. Like [Forms]![frmSearchClient]![txtSearchFor]&"*"));
You can play with the wildcards to allow for different possibilities, of course.
Oct 25 '06 #2

P: 15
Here's probably the simplest, not certain how well it will work...
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCompany.CompanyIDPK, tblCompany.CompanyName, tblCompany.CompanyAddress, tblCompany.ContactPerson, tblCompany.ContactPosition, tblCompany.OfficeNo, tblCompany.MobileNo, tblCompany.FaxNo, tblCompany.EmailAdd
  2. FROM tblCompany
  3. WHERE (((tblCompany.CompanyName)
  4. Like [Forms]![frmSearchClient]![txtSearchFor]&"*"));
You can play with the wildcards to allow for different possibilities, of course.
thanks a lot for the reply.. it did work for my first query, but for my second query it didn't.. here's what i did with my second query:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblEmployee.EmployeeNo AS [Employee Number], tblEmployee.LastName AS [Last Name], tblEmployee.FirstName AS [First Name], tblEmployee.MiddleName AS [Middle Name], tblCompany.CompanyName AS Company, tblDealer.DealerName AS Dealer, tblOutlet.OutletName AS Outlet, tblPosition.PositionName AS [Position], tblEmployee.EmploymentStatus AS [Employment Status]
  2. FROM tblEmployee INNER JOIN (tblPosition INNER JOIN (((tblCompany INNER JOIN tblContract ON tblCompany.CompanyIDPK = tblContract.CompanyIDFK) INNER JOIN tblDealer ON tblContract.DealerIDFK = tblDealer.DealerIDPK) INNER JOIN tblOutlet ON tblContract.OutletIDFK = tblOutlet.OutletIDPK) ON tblPosition.PositionIDPK = tblContract.PositionIDFK) ON tblEmployee.EmployeeIDPK = tblContract.EmployeeIDFK
  3. WHERE (((tblEmployee.EmployeeNo)=[Forms]![frmSearchEmployee]![txtSearchFor] & "*")) OR (((tblEmployee.LastName)=[Forms]![frmSearchEmployee]![txtSearchFor] & "*")) OR (((tblEmployee.FirstName)=[Forms]![frmSearchEmployee]![txtSearchFor] & "*")) OR (((tblEmployee.MiddleName)=[Forms]![frmSearchEmployee]![txtSearchFor] & "*")) OR (((tblCompany.CompanyName)=[Forms]![frmSearchEmployee]![txtSearchFor] & "*")) OR (((tblDealer.DealerName)=[Forms]![frmSearchEmployee]![txtSearchFor] & "*")) OR (((tblOutlet.OutletName)=[Forms]![frmSearchEmployee]![txtSearchFor] & "*")) OR (((tblPosition.PositionName)=[Forms]![frmSearchEmployee]![txtSearchFor] & "*")) OR (((tblEmployee.EmploymentStatus)=[Forms]![frmSearchEmployee]![txtSearchFor] & "*"));
  4.  
Oct 25 '06 #3

NeoPa
Expert Mod 15k+
P: 31,470
Try the following as a replacement for your WHERE clause.

Expand|Select|Wrap|Line Numbers
  1. WHERE ((tblEmployee.EmployeeNo) & (tblEmployee.FirstName) & (tblEmployee.MiddleName) & (tblCompany.CompanyName) & (tblDealer.DealerName) & (tblOutlet.OutletName) & (tblPosition.PositionName) & (tblEmployee.EmploymentStatus) LIKE "*" & [Forms]![frmSearchEmployee]![txtSearchFor] & "*");
Or more visibly :-

WHERE ((tblEmployee.EmployeeNo) & (tblEmployee.FirstName) & (tblEmployee.MiddleName) & (tblCompany.CompanyName) & (tblDealer.DealerName) & (tblOutlet.OutletName) & (tblPosition.PositionName) & (tblEmployee.EmploymentStatus) LIKE "*" & [Forms]![frmSearchEmployee]![txtSearchFor] & "*");

The "*" before the [txtSearchFor] might be what makes the difference.
Oct 25 '06 #4

Expert 5K+
P: 8,434
Try the following as a replacement for your WHERE clause.
...
Nice one.

Just wanted to point out, what made the difference is that lushh was using = rather than Like.
Oct 25 '06 #5

P: 15
thanks for the reply...the query did not work... it still returned zero results...
Oct 26 '06 #6

Expert 5K+
P: 8,434
thanks for the reply...the query did not work... it still returned zero results...
I'm bowing out at this point. I just pasted your SELECT statement into Notepad and almost fainted when I saw the whole thing. That's way beyond my SQL expertise.

Just remember, there's a big difference between the = and Like operators. = will always look for an exact match, so if you have a wildcard character in the search string (Eg. [txtSearchFor] & "*") then that same character must exist at the same place in the data to match. Or, to put it another way...

You could say that "San Francisco" is Like "San*". But it is not equal to it.
Oct 26 '06 #7

NeoPa
Expert Mod 15k+
P: 31,470
If that didn't work for you Lushh, then we may need to look at the JOINs.
To do this (at least for me to be involved), you're going to need to simplify the SQL. Cut out stuff which isn't needed to illustrate the problem. If we restrict it to 3 tables then we still have the same issues, just not so much to go through.

We may also need to see some sample data. Something chosen to illustrate the problem.
Oct 26 '06 #8

Post your reply

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