query about multiple query

[jgeneie]

hi all,

I have a major problem, i have created a search form where users can select their criteria to search for a customer profile. the search form consist of a few textboxes.
eg. if User A wants to search by "name", he can just simply type the name in the textbox and all the profile of all the "name" will appear. He can also choose more than one search criteria. that is to say he can choose search by " name" and "country" just by entering the criteria he wants in the approriate textboxes.

Question:
all i did was creating the expressions in the expression builder. joining the expressions together using the
"AND" constant right?
eg. "country" AND "name" AND "company name"
but in this case all the textboxes in the "country", "name" and "company name" needs to be specify or else nothing will be displayed.
so lets say i only want to search by only "name this time. how do i mask off the "AND" constant in this case

 

[tlbroadbent]

Test if the input value equals a column value OR NULL. In this example, I assume you are using the input from the form in the query criteria.

WHERE (Name = forms!frmName.txtname OR forms!frmName.txtname IS NULL)
AND (Country = forms!frmName.txtCountry OR forms!frmName.txtcountry IS NULL)
AND ([Company Name] = forms!frmName.txtcompname OR forms!frmName.txtcompname IS NULL) Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[jgeneie]

hi tlbroadbent,
thanks for your help but i have tried your method, still cannot get it working. I still have to input the 3 values.

 

[tlbroadbent]

Please post your query. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[jgeneie]

hi, i have input the query in the query builder using expression builder

i making use of the 3 fields

CustomerName
------------
In row "criteria": [Forms]![frmcriteria]![txtcustomername]
In row "or": [Forms]![frmcriteria]![txtcustomername]Is Null

Country
-------
In row "criteria": [Forms]![frmcriteria]![txtcountry]
In row "or": [Forms]![frmcriteria]![txtcountry]Is Null

Companyname
-----------
In row "criteria": [Forms]![frmcriteria]![txtcompanyname]
In row "or": [Forms]![frmcriteria]![txtcompanyname]Is Null

hope its not too confusing. all this is written in the query builder, using expression builder

 

[tlbroadbent]

Open the Query in SQL view. I think you'll find it differs from the sample I posted. You can copy the SQL statement from the SQL view and paste it here if you can't see the difference and figure out how to change the query. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[jgeneie]

hey thanks man....
u have been a great help!

I have created a macro to display the output in a report format - which is to say, a OK button was created using a marco. Is there other better methods.
* because I have other records to display in the report. eg. address, email, tele. but whenever i click the ok button, it pops out the "enter parameter value" for address, email and tele. which i dont intend to use as a search criteria.

please assist
thanks