Search criteria

Eljefegeneo

Eljefegeneo

Still trying to learn
Local time
Today, 15:28
Joined
Jan 10, 2011
Messages
902
Probably another novice question, but I am stumped again.

I have made a simple search form which is used to set the criteria for a query. When I run the query without putting any search criteria into the search form it only selects about 70% of the records. There are about 20K records that should meet the criteria but I only get about 14K. I am wondering what I am doing wrong. It may be that some of the fields in the query do not have any data, i.e. a telephone number? Initially I use OR instead of AND but that just gave me all of the records regardless of the criteria. Thanks.

SELECT tblMain.ClientID, tblMain.Surname, tblMain.Organization, tblMain.ProgramTitle, tblMain.City, tblMain.State, tblMain.Zip4, tblMain.Telephone
FROM tblMain
WHERE (((tblMain.Surname) Like "*" & [Forms]![frmJeffSearch]![txtSurname]) AND ((tblMain.Organization) Like "*" & [Forms]![frmJeffSearch]![txtOrganization] & "*") AND ((tblMain.ProgramTitle) Like "*" & [Forms]![frmJeffSearch]![txtProgramTitle] & "*") AND ((tblMain.City) Like "*" & [Forms]![frmJeffSearch]![txtCity] & "*") AND ((tblMain.State) Like "*" & [forms]![frmJeffSearch]![cboState]) AND ((tblMain.Zip4) Like [Forms]![frmJeffSearch]![txtZip4] & "*") AND ((tblMain.Telephone) Like "(" & [forms]![frmJeffSearch]![txtAreaCode] & "*"));
 
If you omit the criteria then instead of returning "ALL" the values (as you would expect) no values will be returned.

So if you wanted to see all the records you would use this SQL statement:-

SELECT tblMain.ClientID, tblMain.Surname, tblMain.Organization, tblMain.ProgramTitle, tblMain.City, tblMain.State, tblMain.Zip4, tblMain.Telephone
FROM tblMain

If you had only a sirname to search on you would use this statement:-

SELECT tblMain.ClientID, tblMain.Surname, tblMain.Organization, tblMain.ProgramTitle, tblMain.City, tblMain.State, tblMain.Zip4, tblMain.Telephone
FROM tblMain WHERE (((tblMain.Surname) Like "*" & [Forms]![frmJeffSearch]![txtSurname])
 
Thanks for the prompt reply. I do understand what you are telling me, but, I have seven different criteria on which I want to do the search. They can be just one or all of them, or any combination of the seven. The problem seems to be that if, for example, a record does not have a telephone number, it won't even be considered in the search. There are 27 records with the state of AK. But only 21 show up in the query because they don't have a telephone number.
 
It's been a long time since I did this stuff but I think it goes like this:
Code: 
Like "*" & [Forms]![frmJeffSearch]![txtSurname] & "*" Or [Forms]![frmJeffSearch]![txtSurname] Is Null
Or this:
Code: 
Like Nz([Forms]![frmJeffSearch]![txtSurname], "") & "*"
I would advise that you begin with one text box first, get it working full proof before incorporating the other controls.
 
I'm wondering why you use >>> [Forms]![frmJeffSearch]![txtSurname] <<< ???

Is the Command button that executes the query on a different form? Or is it on the same form as the text boxes... txtSirName???
 
Uncle Gizmo said:
I'm wondering why you use >>> [Forms]![frmJeffSearch]![txtSurname] <<< ???

Is the Command button that executes the query on a different form? Or is it on the same form as the text boxes... txtSirName???
Click to expand...
That's because the OP is running it directly from a query, hence, the full name qualification :)
 
Ah! thanks vbaInet, I assumed it was all strings in the form module, which is what it will have to be to work I reckon.

I expect an SQL expert could be do it in an SQL statement, but it would be too ugly for me!
 
Last edited:
I have done something similar and it works for me. I think you need to include a wildcard in the query for each form field that could be left blank by the user, looks like you have done this in the same way as I did though, and I am not sure that helps much now I read it back!!!
Graham
 
Uncle Gizmo said:
Ah! thanks vbaInet, I assumed it was all strings in the form module, which is what it will have to be to work I reckon.

I expect an SQL expert could be do it in an SQL statement, but it would be to ugly for me!
Click to expand...
No problem Uncle Giz, I made an assumption there too since it was posted in the Queries section and it was written in plain text.

A query solution is provided in post #6.
 
Well, I am overwhelmed by the response to my problem. And I certainly appreciate everyone's input. I did try the code posted by VbaInet in post #6, but it still produced the same results.

I haven't had a chance to see Uncle Gizmo's videos yet, but will take a look at them at them later on today.No sound on the computer I am using at the moment.

In any case, perhaps I will post a sample of what I am trying to do with the hope that someone will show me what is wrong.
 
I forgot to mention that the problem seems to occur when there is no data in a particular field in a record. I.e. no telephone number, the record is not considered even if it it meets some other criteria.
 
Attached is the sample DB and, my data and queries. I have not had a chance to look at Uncle Gizmos videos yet, will do tonight.
 

Attachments

I tired the all to no avail. I tried both of your suggestions and they didn't work. I tried some of my own to no avail either.
 
You have 5 or so queries in your db. What I'm asking is, which one of those queries did you try what I suggested? I don't have time to look at all of them to find the one ;)
 
vbaInet:
Both are the buttons in yellow. Qry#2 is your first one using the Nz code. Qry#6 is your second one. I tried to use this (your second code) but when I save the query and then open it again, it results in some very strange coding of which I did not include; double criteria for the same column. My attempt to modify your second code was #3 and #4 but the results were the same as the others.

I do appreciate you taking the time to look at this for me.
 

Attachments

You must log in or register to reply here.

Similar threads

H
Problem with Search Boxes
2
Replies
24
Views
954
Pat Hartman
P
H
Solved Search across multiple fields
Replies
8
Views
619
Harry Paraskeva
H
B
Combo Box Search Issue
2
Replies
34
Views
967
MajP
MajP
M
Solved Multi combo box and textbox search
Replies
14
Views
1,718
mounty76
M
W
Solved MS Access Criteria code for searching a number value in a FORM
Replies
2
Views
449
pbaldy
pbaldy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom