Search criteria

[Eljefegeneo]

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

 

[Eljefegeneo]

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]

What are the datatypes of all the fields?

 

[Eljefegeneo]

They are all text.

 

[vbaInet]

It's been a long time since I did this stuff but I think it goes like this:

Like "*" & [Forms]![frmJeffSearch]![txtSurname] & "*" Or [Forms]![frmJeffSearch]![txtSurname] Is Null

Or this:

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.

 

[vbaInet]

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

 

[axolotl66]

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]

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.

 

[Eljefegeneo]

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]

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]

Let's see the code you said you tried.

 

[Eljefegeneo]

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]

Which of the queries did you try what was advised?

 

[Eljefegeneo]

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]

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]

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.

 

[Eljefegeneo]

I have been through the tutorials from uncle Gizmo but still don't see how to solve the problem if there is no data in a certain field. If all the fields in all the record are filled in, there is no problem. But If there is a null or blank value in one of the fields, it doesn't show up in the query. Or am I wrong about what is shown on the videos? I tried to insert a false criteria: insert and "x" into one of the unbound text boxes, but that didn't work either. The only way I think to do this is to have 49 different criteria with AND/OR statements (maybe even more) which will probably result in an error message telling me that the query is too complex.

I have been using a multi-search form which works fine, but there are just too many records; the need is for multi field filtering, i.e. all people named Jones in California. But if there is no telephone number, the the record is not returned.

Is there any solution to this or is this just not possible in Access 2010?

 

[vbaInet]

Is Null (i.e. the way I wrote it) and IsNull() (the way you wrote it) aren't quite the same in that context.

I'm going to use two fields as an example, Surname and Organisation. If I were to enter "how" in Surname and "ABC" in Organisation, what record(s) do you expect to see?

 

[GinaWhipp]

Hmm this might help...

IsNull vs Is Null
http://www.techrepublic.com/article/learn-the-differences-between-is-null-and-isnull-in-access/

So, your Criteria should look like...

Like "*" & [Forms]![frmJeffSearch]![[COLOR=red]txtSurname[/COLOR]] & "*" Or Is Null

Change field names (red) accordingly but second half should be as above not IsNull([txtSurname]), that is never going to work.

That said, I prefer not to perform the search via a query but use a Form. I use Allen Browne's Search Form...
http://allenbrowne.com/ser-62.html

Allows for a lot more flexibility as I can also pass the WHERE Statement to a Report without tying up two seperate queries (one for the Form and one for the Report).

Just my two cents...

 

[Eljefegeneo]

I finally had a chance to change all the code as directed and it seems to work fine. But this may be a case of be careful what you ask for. Maybe now there is too much date which includes all null fields. But it certainly is better than what I had previously that only brought up about 70% of the records. So this is a work still in progress. But I thank both of you who spent time on this and put up with my questions.