Invalid Use of Null 1

[elevins]

In a Dynamic Query I am using a button to run an event procedure. I have 3 check boxs that I would like to search and display 'if' the check box is selected. There will also be 9 text boxes and I would like to enter criteria and check box and have the query display a table with the correct criteria. I have the text boxes working and the check boxes will work If I only make one active.

Is there a way I can use multiple check boxes without the query saving the value of each check box field as 'True' or 'False'and still have it display correctly?

sample code:


where = Null
where = where & " AND [Inventory]like '" + Me![Inventory] + "*'"
where = where & " AND [SerialNum]like '" + Me![SerialNum] + "*'"
where = where & " AND [Description]like '" + Me![Description] + "*'"
where = where & " AND [Mfg]like '" + Me![Mfg] + "'"
where = where & " AND [ModelNum]like '" + Me![ModelNum] + "*'"
where = where & " AND [Cost]like '" + Me![Cost] + "'"
where = where & " AND [CaseNum]like '" + Me![CaseNum] + "*'"
where = where & " AND [Remarks]like '" + Me![Remarks] + "*'"
where = where & " AND [Date]like '" + Me![Date] + "*'"


where = where & " AND [Junk]=" & IIf(Me![Junk], "True", "False")
where = where & " AND [Stored]=" & IIf(Me![Stored], "True", "False")
where = where & " AND [Stolen]=" & IIf(Me![Stolen], "True", "False")

 

[KenReay]

Hi

Not sure I understand your question, but from the title, the Nz() function might help, used like so

where = where & " AND [Stored]=" & IIf(Nz(Me![Stored],True), "True", "False")

see help on Nz() for explanation


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[elevins]

OK.. I really appreciate you help.

I tried that but am still having the same problem.

Lets see if I can clarify my question.

Again I have 3 check boxes and 8 text boxes on a form, along with a command button that runs an event procedure to search a table for content and displays that criteria in a table.

The problem comes when I select (place check in box) in the checkbox "Stored" and the "Date" of 2002 entered into a text box and conduct a search, it will display the proper criteria in a table. When I close that table and search again with "Stored" NOT selected and the "Date" of 2002 entered into a text box. It will display nothing because Stored is now FALSE and remains that way in the query. My question is:

How can make it so when I search again and "Stored" is NOT selected the query will not count "Stored" as False in the criteria.

 

[PHV]

Set the TripleState property of your checkboxes to True and the DefaultValue to Null.
Then (for each checkbox):
If Not IsNull(Me![Stored]) Then
where = where & " AND [Stored]=" & IIf(Me![Stored], "True", "False")
End If

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

 

[elevins]

Thanks PHV that was very helpful, I now have the option to make the checkbox false.