Query based on form input

[alfonsomozo]

I have created a table with a list of clients and their details.
I have then set-up a form with alot of free text boxes to search each specific field. This works well unless there is one blank field for the entire record. If this is the case i can not get the record to turn up no matter what. At the moment I am using
Like "*" & [Forms]![Search]![City] & "*"
The only way i can get it to return all records is to modify the above statement to
Like "*" & [Forms]![Search]![City] & "*" OR is null
This works but if i search specifically for a City and the City field is left blank for a number of records their results will always be returned.
I was hoping to use an IIF statement but i was having trouble getting it to work, ideally i would like to use
IIf([Forms]![Search]![FirstName] Is Null,Is Null Or Like "*",Like "*" & [Forms]![Search]![FirstName] & "*")
which should work because if i type in manually
is null OR "*" all results are returned.
Access for some reason changes this to
IIf([Forms]![Search]![FirstName] Is Null,([Contacts].[FirstName]) Is Null Or ([Contacts].[FirstName]) Like "*",([Contacts].[FirstName]) Like "*" & [Forms]![Search]![FirstName] & "*") which consequently causes the query not to return anything.
Basically is there a way to change the query to work or is there an easy way to change the set-up of my tables so as opposed to being NULL when no value is entered they will instead have have the value "" which will be returned with "*".
thanks in advance.
Alex.

 

[PHV]

Like '*' & [Forms]![Search]![City] & '*' OR [Forms]![Search]![City] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alfonsomozo]

thanks for the prompt response PHV. I have tried writing in what you suggested in Access 2003 but have still not achieved desired results. If i save and close the query then reopen it Access has created a new field for each text input titled along the lines of [Forms]![Search]![City] with the criteria being Is Null. If I press search with no search criteria all results are returned but if I search by surname for a record with no first name it is still not returned.
Have you any suggestions to fix this query or alternatively enter "INVISIBLE" data into each field thus returning a result every time.

Thanks.

 

[PHV]

What is the SQL code of your query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alfonsomozo]

Here is the SQL code. I hope it makes sense to you. As you can see i am searching many fields and the chances of all of them being filled out are minimal.

SELECT Contacts.ContactID, Contacts.SalutationID, Contacts.FirstName, Contacts.LastName, Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.StateID, Contacts.PostalCode, Contacts.CompanyName, Contacts.Position, Contacts.WorkPhone, Contacts.WorkExtension, Contacts.MobilePhone, Contacts.FaxNumber, Contacts.EmailName, Contacts.ContactTypeID, Contacts.Notes, Contacts.[Authorised By], Contacts.[Authorised Date]
FROM Contacts
WHERE (((Contacts.SalutationID) Like '*' & [Forms]![Search]![Salutation] & '*') AND ((Contacts.FirstName) Like '*' & [Forms]![Search]![FirstName] & '*') AND ((Contacts.LastName) Like '*' & [Forms]![Search]![LastName] & '*') AND ((Contacts.Address1) Like '*' & [Forms]![Search]![Address] & '*') AND ((Contacts.Address2) Like '*' & [Forms]![Search]![Address2] & '*') AND ((Contacts.City) Like '*' & [Forms]![Search]![City] & '*') AND ((Contacts.StateID) Like '*' & [Forms]![Search]![State] & '*') AND ((Contacts.PostalCode) Like '*' & [Forms]![Search]![PostalCode] & '*') AND ((Contacts.CompanyName) Like '*' & [Forms]![Search]![CompanyName] & '*') AND ((Contacts.Position) Like '*' & [Forms]![Search]![Title] & '*') AND ((Contacts.WorkPhone) Like '*' & [Forms]![Search]![WorkPhone] & '*') AND ((Contacts.WorkExtension) Like '*' & [Forms]![Search]![WorkExtension] & '*') AND ((Contacts.MobilePhone) Like '*' & [Forms]![Search]![MobilePhone] & '*') AND ((Contacts.FaxNumber) Like '*' & [Forms]![Search]![FaxNumber] & '*') AND ((Contacts.EmailName) Like '*' & [Forms]![Search]![EmailName] & '*') AND ((Contacts.ContactTypeID) Like '*' & [Forms]![Search]![ContactTypeID] & '*') AND ((Contacts.Notes) Like '*' & [Forms]![Search]![Notes] & '*') AND ((Contacts.[Authorised By]) Like '*' & [Forms]![Search]![Authoriser] & '*') AND ((Contacts.[Authorised Date]) Like '*' & [Forms]![Search]![Authorised Date] & '*')) OR ((([Forms]![Search]![CompanyName]) Is Null) AND (([Forms]![Search]![Title]) Is Null) AND (([Forms]![Search]![WorkPhone]) Is Null) AND (([Forms]![Search]![WorkExtension]) Is Null) AND (([Forms]![Search]![MobilePhone]) Is Null) AND (([Forms]![Search]![FaxNumber]) Is Null) AND (([Forms]![Search]![EmailName]) Is Null) AND (([Forms]![Search]![ContactTypeID]) Is Null) AND (([Forms]![Search]![Notes]) Is Null) AND (([Forms]![Search]![Authoriser]) Is Null) AND (([Forms]![Search]![Authorised Date]) Is Null) AND (([Forms]![Search]![Address]) Is Null) AND (([Forms]![Search]![Address2]) Is Null) AND (([Forms]![Search]![City]) Is Null) AND (([Forms]![Search]![State]) Is Null) AND (([Forms]![Search]![PostalCode]) Is Null) AND (([Forms]![Search]![LastName]) Is Null) AND (([Forms]![Search]![FirstName]) Is Null) AND (([Forms]![Search]![Salutation]) Is Null));

 

[PHV]

Replace the whole WHERE clause with the following:
WHERE (Contacts.SalutationID Like '*' & [Forms]![Search]![Salutation] & '*' OR [Forms]![Search]![Salutation] Is Null)
AND (Contacts.FirstName Like '*' & [Forms]![Search]![FirstName] & '*' OR [Forms]![Search]![FirstName] Is Null)
AND (Contacts.LastName Like '*' & [Forms]![Search]![LastName] & '*' OR [Forms]![Search]![LastName] Is Null)
AND (Contacts.Address1 Like '*' & [Forms]![Search]![Address] & '*' OR [Forms]![Search]![Address] Is Null)
AND (Contacts.Address2 Like '*' & [Forms]![Search]![Address2] & '*' OR [Forms]![Search]![Address2] Is Null)
AND (Contacts.City Like '*' & [Forms]![Search]![City] & '*' OR [Forms]![Search]![City] Is Null)
AND (Contacts.StateID Like '*' & [Forms]![Search]![State] & '*' OR [Forms]![Search]![State] Is Null)
AND (Contacts.PostalCode Like '*' & [Forms]![Search]![PostalCode] & '*' OR [Forms]![Search]![PostalCode] Is Null)
AND (Contacts.CompanyName Like '*' & [Forms]![Search]![CompanyName] & '*' OR [Forms]![Search]![CompanyName] Is Null)
AND (Contacts.Position Like '*' & [Forms]![Search]![Title] & '*' OR [Forms]![Search]![Title] Is Null)
AND (Contacts.WorkPhone Like '*' & [Forms]![Search]![WorkPhone] & '*' OR [Forms]![Search]![WorkPhone] Is Null)
AND (Contacts.WorkExtension Like '*' & [Forms]![Search]![WorkExtension] & '*' OR [Forms]![Search]![WorkExtension] Is Null)
AND (Contacts.MobilePhone Like '*' & [Forms]![Search]![MobilePhone] & '*' OR [Forms]![Search]![MobilePhone] Is Null)
AND (Contacts.FaxNumber Like '*' & [Forms]![Search]![FaxNumber] & '*' OR [Forms]![Search]![FaxNumber] Is Null)
AND (Contacts.EmailName Like '*' & [Forms]![Search]![EmailName] & '*' OR [Forms]![Search]![EmailName] Is Null)
AND (Contacts.ContactTypeID Like '*' & [Forms]![Search]![ContactTypeID] & '*' OR [Forms]![Search]![ContactTypeID] Is Null)
AND (Contacts.Notes Like '*' & [Forms]![Search]![Notes] & '*' OR [Forms]![Search]![Notes] Is Null)
AND (Contacts.[Authorised By] Like '*' & [Forms]![Search]![Authoriser] & '*' OR [Forms]![Search]![Authoriser] Is Null)
AND (Contacts.[Authorised Date] Like '*' & [Forms]![Search]![Authorised Date] & '*' OR [Forms]![Search]![Authorised Date] Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alfonsomozo]

WOW! it actually works. Thanks so much, i guess i should probably learn SQL now.

 

[p7eggyc]

Check out faq181 -5497 for a BuildWhere routine that loops through the controls on your form and builds the Where clause dynamically. I've used this code and it works really nicely.

Peg

 

[Dickie100]

A simple query:

Does anyone know how to write a WHERE clause in SQL based upon the value of a form Control in MS Access ?

Any help would be appreciated.

Muchos

Richard

 

[Golom]

Dickie100
This thread is an example of how to do that but start a new thread for a new question.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]