How to make the fields optional in a Query form

[Ramshan]

Hi everyone,
I have created a query form that contains 6 text boxes and two drop down boxes that will pull up the values, when I enter all those values and hit the query button. Here the problem is, MS Access is considering all the fields as mandatory fields. But I need some of them as optional. So what should I do here?,

thanks in advance

 

[llkhoutx]

Dynamically build your query, particularly the WHERE clause, with VBA depending on whether or not a text/combo box is populated, e.g.

sql = ""
if me.textbox1 & "" <> "" then
sql = "fieldname = " & me.textbox1 'a numeric criteria
end if
if me.textbox2 & "" <> "" then
if len(sql) <> 0 then
sql = sql + " and fieldname2 = '" & me.textbox2 & "'" ' a text criteria
else
sql = "fieldname2 = '" & me.textbox2 & "'" ' a text criteria
end if

'Note that date criteris is enclosed with " (quotation marks).

etc

if len(sql) > 0 then
sql = " WHERE " & SQL
end

yoursql = "SELECT . . . " & SQL

docmd.EXECUTE YOURSQL

ALTERNATIVELY

STORE SQL IN A QUERY TO EXECUTE ELSE WHERE

Hopefully, the above will get you started.

 

[John Big Booty]

In criteria for the fields that may be left blank use;

Like [URL="http://www.techonthenet.com/access/functions/advanced/nz.php"]Nz[/URL](Forms!YourFormName!TextBoxName, "*")

 

[Ramshan]

Thank you very for your reply. I will be more happy if you provide me some readings or example on this. Else can you just help me out in getting this. My query page consist of
1. Property Name (Textbox)
2. City (Text box)
3. Address (Text box)
4. County (Combo box)
5. Architecture style (Text box).

I need to make all of the above as optional fields, and if when I enter the values and hit a button called 'query' it should bring up the values.

This is the code I used,

Option Compare Database

Private Sub Command20_Click()
Me.qprop1.Value = ""
Me.qcity1.Value = ""
Me.qaddress1.Value = ""
End Sub

Private Sub Command21_Click()
DoCmd.OpenQuery "mainquery", acViewNormal
End Sub

And in the main query, I have following code under respective columns,

Like [Forms]![SearchForm].[qprop1] & "*"
Like [Forms]![SearchForm].[qcity1] & "*"
Like [Forms]![SearchForm].[qaddr1] & "*"


Please help me with this ,

 

[jdraw]

You might get some ideas from this free video tutorial
http://www.datapigtechnologies.com/flashfiles/searchform.html

 

[boblarson]

And another way would be to use this whole thing (which would take care of nulls and empty strings.

(Like Forms!YourFormName!TextBoxName & "*") OR (Len(Forms!YourFormName!TextBoxName & "") = 0l)