Solved Multiple search criteria using queries (1 Viewer)

[jazsriel]

Hi, it has been awhile. But I am back with a question. I have seen DBs where someone will implement search queries, using wildcards and like on the queries. However, I am trying to do this with multiple search fields on a query. I have had no luck but hoping someone can give me some ideas or guidance on how to do this.

I have attached what I was working on, however, where I am running into a problem is when I try to just enter no information to get past a specific search query it will still pull up all or most of the entries. Like let me search for a last name of smith, it will still pull up most of the entries. I want it to only pull up smith if that is what I am looking for when I leave the other entries blank. Any ideas?

 

[jdraw]

This is your query

SELECT tblecontact.Position
, tblecontact.[First Name]
, tblecontact.[Last Name]
, tblecontact.[Phone Number]
, tblecontact.Email
, tblecontact.Notes
, tblecontact.Extension
, tblecontact.Company
FROM tblecontact
WHERE
(((tblecontact.[Last Name]) Like "*" & [Enter Last Name] & "*")) OR
(((tblecontact.[First Name]) Like "*" & [Enter First Name] & "*")) OR
(((tblecontact.Position) Like "*" & [Enter Position] & "*")) OR
(((tblecontact.Company) Like "*" & [Enter Company] & "*"))
ORDER BY
tblecontact.[First Name] DESC
, tblecontact.[Last Name] DESC;

Each of your OR conditions is causing you to select records in addition to the LastName like "*Smith*".

Your Like "*First Name*", "*position*" and "*Company*" (where each is blank) is similar to saying
Any First Name, Any Position and Any Company.

 

[mike60smart]

Hi, it has been awhile. But I am back with a question. I have seen DBs where someone will implement search queries, using wildcards and like on the queries. However, I am trying to do this with multiple search fields on a query. I have had no luck but hoping someone can give me some ideas or guidance on how to do this.

I have attached what I was working on, however, where I am running into a problem is when I try to just enter no information to get past a specific search query it will still pull up all or most of the entries. Like let me search for a last name of smith, it will still pull up most of the entries. I want it to only pull up smith if that is what I am looking for when I leave the other entries blank. Any ideas?

Here is another way of doing this.

The frmSearch allows you to specify the data you want to find.

 

[jazsriel]

This is your query

SELECT tblecontact.Position
, tblecontact.[First Name]
, tblecontact.[Last Name]
, tblecontact.[Phone Number]
, tblecontact.Email
, tblecontact.Notes
, tblecontact.Extension
, tblecontact.Company
FROM tblecontact
WHERE
(((tblecontact.[Last Name]) Like "*" & [Enter Last Name] & "*")) OR
(((tblecontact.[First Name]) Like "*" & [Enter First Name] & "*")) OR
(((tblecontact.Position) Like "*" & [Enter Position] & "*")) OR
(((tblecontact.Company) Like "*" & [Enter Company] & "*"))
ORDER BY
tblecontact.[First Name] DESC
, tblecontact.[Last Name] DESC;

Each of your OR conditions is causing you to select records in addition to the LastName like "*Smith*".

Your Like "*First Name*", "*position*" and "*Company*" (where each is blank) is similar to saying
Any First Name, Any Position and Any Company.

I understand what you have pointed out to me, but I am still stuck trying to resolve it.

 

[jazsriel]

Here is another way of doing this.

The frmSearch allows you to specify the data you want to find.

I like what you did with this. I am looking at the code that you put in. I am trying to modify it so when you click search it will actually open the report to show the results. But I am finding out that it is not as simple as changing the openform to the openreport command. I really like when you add a new record that it automatically updates the search form to reflect the new information.

 

[jazsriel]

okay, i think i figured this out after playing around with it. I am using the WhereCondition to apply the search criteria and allow it to open the report with only my selected search criteria. Thank you everyone that answered my plea for help.

 

[mike60smart]

okay, i think i figured this out after playing around with it. I am using the WhereCondition to apply the search criteria and allow it to open the report with only my selected search criteria. Thank you everyone that answered my plea for help.

We were glad to help