Using Dynamic Query - Would Like to Search Check Box from table

[elevins]

Can anyone help me?

I am using Dynamic Query, it is run when a click a command button. All the text boxes are working fine but I would like to use check boxes to search also. I am having trouble finding any info on how to search using a check box.

Here is an example of what code I am using:

Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()


On Error Resume Next
db.querydefs.Delete ("Dynamic_Query")
On Error GoTo 0

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 [Stored]= '" + Me![Stored] + "'" ' this is the checkbox - yes/no there will be three more check boxes


Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from SearchQueryBase " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub

 

[PHV]

Where = Where & " AND [Stored]= " + Me![Stored]

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

 

[elevins]

I tried this and does not seem to work for my searching of a Checkbox. Do you have any other ideas?

 

[elevins]

Anybody's help would be much appreciated. This is the only problem I am stuck on with my database.

 

[PHV]

And something like this ?
Where = Where & " AND [Stored]=" & IIf(Me![Stored], "True", "False")

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

 

[elevins]

Great! That helped out a lot. Thanks.

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

As you can probably tell I am not great with this code but I am learning quickly.

The problem now is that it only will work corectly for the feild "Stored". If I code the other fields say "Junk" and "Stolen" They will not work together. I will recieve a "Invalid use of Null" error.

Is there an "OR" statement or something else that could solve this problem?

This is the current code I have, any suggestions would help.


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")

 

[elevins]

Once I check a box like "Stored" and then unckeck it It changes the Dynamic Query to False and stays that way until I close the Form. Is there a way to have it "clear" the check box after deselection?