QUERY CRITERIA NIGHTMARE 1

jflo · Mar 12, 2003

I've got a search form that has ContractNo, Name, Firstname and PhoneNo as four text fields. I need to look up informtation in a table and display matching records. I've looked at a couple of thread in this forum and it seams a lot of people are having issues with query criterias liked to form fields. I'll try to explain my situation.

Table tblClients:
ClientNo; ClientName; ClientFirstname; PhoneNo1; PhoneNo2; ContractNo1; ContractNo2; ContractNo3.

In my query, I need all these fields. A client has a Name, a Firstname and a ContractNo1 but might not have phone numbers entered nor second or third contract numbers. My problem is with the phone numbers and contract numbers fields when I try to lookup information, regardless of the field it is strored in.

I've got {Like "*" & [Forms]![fLookup]![ContractNo] & "*"} and this works when there is information in a field but when a field is left empty, it doesn't.

I've gone back to the simple basics and now I've only got criterias for Name, Firstname and ContractNo1. I've tried many things like {IIF([Forms]![fLookup]![ContractNo]<>"", Like "*" & [Forms]![fLookup]![ContractNo] & "*" OR nz([Forms]![fLookup]![ContractNo]="&quot

="",False)} a many others.

Can any one give me advice on how to do this. I have set up a dummy db with a the client table, the lookup query and the two forms I use for this. It's in Access 97.

Thanks

J-F

scriverb · Mar 12, 2003

J-F: Just what are you experiencing when the form field for ControlNo is left blank? Are you getting all records no matter what is in the field in the table? Are some being left out? We need to know that information to help you figure out how your criteria is working on your data. Bob Scriver

jflo · Mar 13, 2003

Thanks scriverb, I didn't want to put too much information in the first post not to scare anyone.

As I said, I've simplyfied it a bit for this post but what I have right now is

Clients in tblClient:
"ClientNo";"ClientName";"ClientFirstname";"PhoneNo1";"PhoneNo2";"ContractNo1";"ContractNo2";"ContractNo3"

1;"Smith";"Roger";"514-222-2222";"514-333-3333";"123456789";;""

2;"Smith";"Richard";;"450-444-4444";"006006006";"S06006006";"789123456"

3;"Smith";"Steve";;;"789123456";"";""

The SQL statement in my query is:
SELECT tblClients.ClientName, tblClients.ClientFirstname, tblClients.ContractNo1, tblClients.ContractNo2, tblClients.ContractNo3, tblClients.PhoneNo1, tblClients.PhoneNo2
FROM tblClients
WHERE (((([tblClients].[ClientName]) Like "*" & [Forms]![fLookup]![Name] & "*" Or nz([Forms]![fLookup]![Name]="&quot

="&quot

<>False) AND ((([tblClients].[ClientFirstname]) Like "*" & [Forms]![fLookup]![Firstname] & "*" Or nz([Forms]![fLookup]![Firstname]="&quot

="&quot

<>False) AND ((([tblClients].[ContractNo1]) Like "*" & [Forms]![fLookup]![ContractNo] & "*" Or nz([Forms]![fLookup]![ContractNo]="&quot

="&quot

<>False));

Right now, if I enter a ContratNo (or part of it) that is in the table, it works. If it's not there, I get nothing, which is good. When I enter a PhoneNo (or part of it) I get results, even if this client has no phone number entered. If I enter a Name (or part of it) it works, if the name entered isn't there, I get nothing, which is good. If I enter a Firstname (or part of it) it works, if the firstname entered isn't there, I get nothing, which is good.

So right now, the issue is with the phone number. But I only validate the first phone number field and the first contract number field.

How's that? Does it make more sens?

Thanks

scriverb · Mar 13, 2003

Okay, it is getting a little clearer now. But, the SQL that you posted has no reference to a criteria experession for the Phone Number. Had you overlooked that criteria. It can't be that simple can it? Are you satisified with the way the WHERE portion of your QUERY is working except for the Phone Number?

Please explain what you expect out of this:
nz([Forms]![fLookup]![Name]="&quot

="&quot

<>False)

I must have a mind block or something but I am not sure what you are trying to do here. This is the format of the statement on the OR side of each criteria statement.

Just explain in english what you want your criteria statement to do for you. Are you wanting to search by one and more than one of the columns of data? Search by Name and Phone Number both with the others left blank. so, in that scenario there would be a criteria search on Name and Phone number only with the others having no effect on the search. Is that what you are looking for? We can also have the one Phone# and one contract# form entry search both fields on a OR statement. Let me know if this is what you want.

Bob Scriver

jflo · Mar 13, 2003

Thanks for your reply.

-->But, the SQL that you posted has no reference to a criteria experession for the Phone Number. Had you overlooked that criteria. It can't be that simple can it?<--
You are right, I've overlooked it in the sample db I've assembled to simplify it for this forum. My original db is in french and all the field names are in french so I made a copy of the db, removed everything not needed to solve this issue. I must have missed it when translating.

-->Please explain what you expect out of this:
nz([Forms]![fLookup]![Name]="&quot

="&quot

<>False)<--
I took this from this forum somewhere and it's what makes it return records when a field is left blank on the search form.

-->Just explain in english what you want your criteria statement to do for you. Are you wanting to search by one and more than one of the columns of data? Search by Name and Phone Number both with the others left blank. so, in that scenario there would be a criteria search on Name and Phone number only with the others having no effect on the search. Is that what you are looking for? We can also have the one Phone# and one contract# form entry search both fields on a OR statement. Let me know if this is what you want. <--

Contract: (blank)
Name: Smith
Firstname: (blank)
Phone number: (blank)
--> should retrieve all three records

Contract: (blank)
Name: Smith
Firstname: P
Phone number: (blank)
--> shouldn't retrieve record

Contract: (blank)
Name: Smith
Firstname: R
Phone number: (blank)
--> should retrieve two records

Contract: 123456
Name: (blank)
Firstname: (blank)
Phone number: (blank)
--> should retrieve all three records

Contract: 1234567
Name: (blank)
Firstname: (blank)
Phone number: (blank)
--> should retrieve only one record

And so on. You should be able to enter information in none (not very practical I no, but...) and it should return all records as nothing is filtering the information. And you could also enter information in all the four fields as to be very precise in your search. In that case it would more then likely return one or zero record, depending on the data. I'm comfortable with access and sql, but I'm in a dead-end. It's not urgent nor critical for this application to have this feature. I just know it's feasible and it kill's me to stumble on it.

Thanks

Jean-Francois Loiselle
Montréal, Québec, Canada

scriverb · Mar 13, 2003

Here is modified SQL for the WHERE statement portion of your query. I have modified it to include the PhoneNo matches for both phone number fields AND all three contract number fields. The phone number and contract number criteria is still in and AND situation as compared to the other field comparisons like Name and FirstName but they are in an OR comparison within their individual group. So, if there is data entered in the PhoneNo form control then a LIKE comparison is made to both PhoneNo1 and PhoneNo2 and if a match is found in either then the record is selected. Same goes for the ContractNo form control. If there is an entry for searching in this control then a LIKE comparison is made to all three of them so that if a match is found in any one of them then the record is selected.

WHERE (((([tblClients].[ClientName]) Like "*" & [Forms]![fLookup]![Name] & "*"
Or nz([Forms]![fLookup]![Name]="&quot

="&quot

<>False) AND
((([tblClients].[ClientFirstname]) Like "*" & [Forms]![fLookup]![Firstname] & "*" Or nz([Forms]![fLookup]![Firstname]="&quot

="&quot

<>False) AND
(((([tblClients].[PhoneNo1]) Like "*" & [Forms]![fLookup]![PhoneNo] & "*" Or nz([Forms]![fLookup]![PhoneNo]="&quot

="&quot

<>False) or ((([tblClients].[PhoneNo2]) Like "*" & [Forms]![fLookup]![PhoneNo] & "*" Or nz([Forms]![fLookup]![PhoneNo]="&quot

="&quot

<>False)) AND (((([tblClients].[ContractNo1]) Like "*" & [Forms]![fLookup]![ContractNo] & "*" Or nz([Forms]![fLookup]![ContractNo]="&quot

="&quot

<>False) or ((([tblClients].[ContractNo2]) Like "*" & [Forms]![fLookup]![ContractNo] & "*" Or nz([Forms]![fLookup]![ContractNo]="&quot

="&quot

<>False) or ((([tblClients].[ContractNo3]) Like "*" & [Forms]![fLookup]![ContractNo] & "*" Or nz([Forms]![fLookup]![ContractNo]="&quot

="&quot

<>False)));

Let me know how this works for you. I was not able to test it as I don't have your data but I believe that I have counted the matching parens correctly.
Bob Scriver

jflo · Mar 14, 2003

It's so obvious once you look at the answer! I had something similar in the french db but must've had AND instead of OR between the two phone numbers and the three ContractNo or something (see SQL code below). Thanks a lot for your patience and help. Hope to get a chance to return the favor sometime (though I doubt I'll get a chance!).

Nom --> Name
Prénom --> Firstname
NoTéléphone --> Phone number
NoContrat --> ContractNo

WHERE (((TClients.NomClient) Like "*" & [Forms]![FNoContrat]![Nom] & "*&quot