Query criteria based on textbox

[rich.barry]

I have a table, a form with a text box for each of the fields in the table, and a query using the data in the text boxes as criteria by which to query the table.

If there is data in the text box then that is the criteria of that field in the query, but if there is no data, then no criteria.

I can't do a criteria of [Forms]![Form1]![TextBox1], because if the textbox is empty, its value is Null, and I don't want a criteria used at all when empty text boxes exits.

I've tried an IIf(IsNull([Forms]![Form1]![TextBox1]),,[Forms]![Form1]![TextBox1]) but Access doesn't like the <truepart> being left blank and puts quotes around the rest of the expression.
I also tried IIf(Not IsNull..... and left the <falsepart> blank, but that didn't work either.

Programatacally placing an * in blank text boxes doesen't work as it ends up excluding any records with Null entries in the table.

Does anyone have a solution other than building an ugly SQL statement using code?

[This message has been edited by rich.barry (edited 09-12-2001).]

 

[DJN]

In the criteria for each text box in the query put: Forms![FormName]![TextBoxName] Or Forms![FormName]![TextBoxName] Is Null

 

[vandewi]

1. Replace your field name in your query with the following statement.

IIf(IsNull([Forms]![Form1]![TextBox1]),-1,[forms]![form1]![Textbox1])

2. Place the number "-1" (quotes not included) in the criteria section. It may seem strange, but it works!

 

[vandewi]

Sorry, there's a correction to the statement

IIf(IsNull([Forms]![Form1]![TextBox1]),-1,[YOURFIELDNAME]=[forms]![form1]![Textbox1])