Make SQL query using checkboxes

[Hafeez Esmail]

I'm trying to make a form that will ease the process of
making a query for my users.

The users want to be able to view a query based what ever
constraints they select on the form (in the form of
checkboxes).

The base of the query is:
Set qdfTemp = CurrentDb.CreateQueryDef("qrySteveCustom",
strSQL)
strSQL starts off as:
strSQL = "SELECT * FROM tblOne"

strSQL
When the user checkmarks a box, strSQL gets updated and
will now read:
strSQL = strSQL & " WHERE (tblOne!Field1 = strConstraint1)"

The 9 checkboxes are 9 constraints all in tblOne!Field1.


Public Sub Constraint2_AfterUpdate()

strColour = "Red"
Call IfStatement(strColour)
End Sub

Public Sub IfStatement(strColor As String)

If strSQL = "*WHERE*" Then
strSQL = strSQL & " AND (tblOne!Field1 = '" & strColor
& "')"
Else
strSQL = strSQL & " WHERE (tblOne!Field1 = '" &
strColor & "')"
End If

End Sub


Please help
Thanks
Hafeez Esmail

 

[John Smith]

The line 'If strSQL = "*WHERE*" Then' will look for strSQL containing the
literal characters "*WHERE*", so will not do what you intend. To use wild
cards, you need the LIKE operator, which is not available in this context. You
could use InStr instead to search for WHERE in strSQL, but a simpler option is
to ensure that WHERE is always present. If you initially set :-

strSQL = "SELECT * FROM tblOne WHERE True"

This will make no difference to the query, but you will only need to AND any
additional criteria to it, i.e.

strSQL = strSQL & " AND Field1 = '" & strColor & "'"

Note, since there is only one table in the query there is no need to qualify
the column name. If you still want to do so, in SQL syntax you should be using
a dot rather than an exclamation mark, i.e. tblOne.Field1