Subquery and IIF statement

[ksamaniego]

I want to have a subquery inputted into my main query based on if a check box has been checked or not...



How would I go about accomplishing this?

-Kyle S.

[TheShabz]

Let's say your query was:
SELECT * FROM tblTableA WHERE WidgetID In (SELECT WidgetID FROM tblTableB)
with the In() portion being the optional. What I would do is the following in the event procedure of whatever triggers the query.

Dim strSQL as String

strMain = "SELECT * FROM tblTableA WHERE "

If chkBox = True Then
strSQL = strSQL & "WidgetID In (SELECT WidgetID FROM tblTableB)"
Else
strSQL = "1=1"
End If

DoCmd.RunSQL (strSQL)

End Sub

So basically you are building your where clause depending on what the checkbox is. I'm sure your query is more complicated but the concept should still work. If you find yourself in a situation with no where clause, simply use 1=1, as above, to fake one.