Need HELP with Custom Dialog query

[daddy.dobie]

I seriously need some help. This problem has taken up way to much of
my time. I've spent hours searching through example code and trying to
change it so it works for me but nothing does. What I have is a small
database where I need to allow users to search for repair shops. I
have a 3 parameter query set up so the user can enter any or all of a
shop name, address, and or zip code to pull up a shop. My problem is I
would like to combine all 3 parameters onto 1 custom dialog box
instead of 3. Could someone please write this query and code for me?
The first time I opened Access was when I started this project. I've
figured out everything I need so far except for this. Here's what I
have: a custom form named frmShopSearch with text boxes named
txtDIRECTORY_NAME, txtAddress, & txtPol_ID_Zip. As well as a search
button "bSearch" and a cancel button "bCancel". Any help on this would
be greatly appreciated!!

 

[Douglas J. Steele]

Assuming you're using the graphical query builder, put the following as the
Criteria under the Shop Name field:

=Forms!frmShopSearch!txtDirectory_Name OR
Forms!frmShopSearch!txtDirectory_Name IS NULL

As the Criteria for the address, put

=Forms!frmShopSearch!txtAddress OR Forms!frmShopSearch!txtAddress IS NULL

As the Criteria for the zip code, put

=Forms!frmShopSearch!txtPol_ID_Zip OR Forms!frmShopSearch!txtPol_ID_Zip IS
NULL

 

[daddy.dobie]

I tried as you said and still no luck. I copied the beginning of the
criteria and on the next line put the OR. When I opened my form,
filled in a few things and hit the search button, I get error: "The
filter operation was canceled. The filter would be too long." Any
other idea?

 

[Douglas J. Steele]

Each of the 3 formulae I gave go into a single criteria box. Don't put the
OR on the second line.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I tried as you said and still no luck. I copied the beginning of the
criteria and on the next line put the OR. When I opened my form,
filled in a few things and hit the search button, I get error: "The
filter operation was canceled. The filter would be too long." Any
other idea?

 

[Douglas J. Steele]

Just to be certain, look at the SQL of your query. (If you're not familiar
with doing this, you select SQL View from the View menu while the query's
open in Design view)

The WHERE clause should be something like:

WHERE (ShopName = Forms!frmShopSearch!txtDirectory_Name OR
Forms!frmShopSearch!txtDirectory_Name IS NULL)
AND (Address = Forms!frmShopSearch!txtAddress OR
Forms!frmShopSearch!txtAddress IS NULL)
AND (Zip = Forms!frmShopSearch!txtPol_ID_Zip OR
Forms!frmShopSearch!txtPol_ID_Zip IS NULL)

(Of course, Access will have put a bunch more parentheses in there)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Each of the 3 formulae I gave go into a single criteria box. Don't put the
OR on the second line.