Search criteria (1 Viewer)

Eljefegeneo · Jun 29, 2014

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] & "*"));

Uncle Gizmo · Jun 29, 2014

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])

Eljefegeneo · Jun 29, 2014

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.

vbaInet · Jun 29, 2014

What are the datatypes of all the fields?

Eljefegeneo · Jun 29, 2014

They are all text.

vbaInet · Jun 29, 2014

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.

Uncle Gizmo · Jun 29, 2014

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???

vbaInet · Jun 29, 2014

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???

That's because the OP is running it directly from a query, hence, the full name qualification

Uncle Gizmo · Jun 29, 2014

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!

axolotl66 · Jun 29, 2014

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

vbaInet · Jun 29, 2014

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!

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.

Uncle Gizmo · Jun 29, 2014

vbaInet said:
A query solution is provided in post #6.

I've been looking for a question that would be suitable to turn into an example of how to build queries with strings in MS Access and this one fitted the bill nicely.

I've done 3 youtube videos, which are in this YouTube playlist:-

MS Access - Building Search Criteria

HERE:-

https://www.youtube.com/playlist?list=PLhf4YcS5Ajdq0MFtLAHAN0Jg9soJpBVvw

I will add the other videos when I've done them, hopefully today! -

Eljefegeneo · Jun 29, 2014

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.

Eljefegeneo · Jun 29, 2014

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.

vbaInet · Jun 29, 2014

Let's see the code you said you tried.

Eljefegeneo · Jun 30, 2014

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.

vbaInet · Jun 30, 2014

Which of the queries did you try what was advised?

Eljefegeneo · Jun 30, 2014

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.

vbaInet · Jun 30, 2014

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

Eljefegeneo · Jun 30, 2014

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.

Search criteria (1 Viewer)

Still trying to learn

Nifty Access Guy

Still trying to learn

AWF VIP

Still trying to learn

AWF VIP

Nifty Access Guy

AWF VIP

Nifty Access Guy

New member

AWF VIP

Nifty Access Guy

Still trying to learn

Still trying to learn

AWF VIP

Still trying to learn

Attachments

AWF VIP

Still trying to learn

AWF VIP

Still trying to learn

Attachments

Similar threads

Users who are viewing this thread