Generate dynamic SQL using Option Buttons - any combination

[Rx_]

See attached diagram: Dynamic Report - user selects 1 or all 4 Checkbox controls. Results in SQL statement to be used for a report. Any combo of checkbox results in the AND for the column for the SQL result.

Any Demo, links or ideas would be appreicated.

A function with a case statement could select each possible outcome. Since there are 4 binary option buttons that would be 2 to the forth power minus 1 possible outcomes. That is 15 SQL statements to write.

Or something that can construct a string:
"Select CustumerID, CustomerStatus, " & MyCheckboxString &
" From MyTable"
Where MyCheckboxString adds the fields as needed.

It won't be as simple as above, because each checkbox has optional criteria's for seletion too. But, that is a different question for later.

 

[plog]

In your VBA you simply loop through the criteria fields, if its used it gets added to the WHERE clause, if not, it doesn't. That's 4 conditional statements to write, not 15.

The key is to add one WHERE condition that will always be true, so you can dynamically build the WHERE no matter how many criteria the user selects. Here's some pseudo code:

strSQL="SELECT FieldName FROM YourTableName WHERE (1=1)"
If (checkbox1) THEN strSQL=strSQL & " AND [Field1]=7"
If (checkbox2) THEN strSQL=strSQL & " AND [Field1] LIKE '*Jim*'"
...
...
DoCmd.RunSQL (strSQL)

That initial "(1=1)" portion is integral in being able to dynamically add more criteria later in the code.

 

[mdlueck]

I was thinking along the same lines here:

Fun/side project: Build dynamic SELECT query using ADODB.Command / .Parameters objs
http://www.access-programmers.co.uk/forums/showthread.php?t=230838

 

[BigHappyDaddy]

This is probably overkill for your situation, but this is the code I have to filter the data in my form. The form header currently has 2 text boxes and 7 combo boxes. Upon execution of this code, a new SQL statement is built, then it is set as the form's recordsource. If nothing is selected, the form returns all records.

For each control in the header, I set it's Tag value to the corresponding name of the control in the detail section (same as the table's field name).

Private Sub btnSearch_Click()
    Dim strSQL As String
    Dim strWhere As String
    Dim blnWhereExists As Boolean
    Dim strWhereSub() As String
    Dim lngControlCount As Long
    Dim lngCounter As Long
    Dim ctrl As Control
 
    strSQL = "SELECT PNFrom, VendorIDFrom, PNTo, VendorIDTo, " & _
                    "OwnerID, CategoryID, ProductInfoURL, " & _
                    "EventID, DataSourceID, DataSourceComments, " & _
                    "ApprovedBy, CrossSetStatusID " & _
             "FROM tblCrossSet "
    blnWhereExists = False
 
    ReDim strWhereSub(Me.Section(acHeader).Controls.Count)
 
    lngCounter = 0
    For Each ctrl In Me.Section(acHeader).Controls
        If (TypeOf ctrl Is TextBox) Or (TypeOf ctrl Is ComboBox) Then
            If Nz(ctrl.Value, "") <> "" Then
                If ctrl.Name = "cboEventID" Then
                    'cboEventID is the only field that is a number; all others are text
                    strWhereSub(lngCounter) = " (tblCrossSet." & ctrl.Tag & " = " & ctrl.Value & ") "
                Else
                    strWhereSub(lngCounter) = " (tblCrossSet." & ctrl.Tag & " = '" & ctrl.Value & "') "
                End If
                lngCounter = lngCounter + 1
                blnWhereExists = True
            Else
                strWhereSub(lngCounter) = ""
            End If
        End If
    Next
 
    If blnWhereExists Then
        strWhere = " WHERE "
        lngCounter = lngCounter - 1
        For lngControlCount = 0 To lngCounter
            strWhere = strWhere & strWhereSub(lngControlCount)
            If lngControlCount < lngCounter Then
                strWhere = strWhere & " AND "
            End If
        Next lngControlCount
    Else
        strWhere = ""
    End If
 
    strSQL = strSQL & strWhere & ";"
    Me.RecordSource = strSQL
End Sub

 

[Rx_]

Thanks! Anyone else? Please contribute

I like the tags.
Yes, I have a base query that then has fields (two fields have optional criteria choices) added to it.

Plog: Took a while, but the (1=1) - got it!
That is nice.
For Text column:
Like '*' is true for records that have a value
Not Like '*' is true for empty records
Like '*' Or Not Like '*' is true for ALL records

The variable number of Parameters - that is really insightful! Thanks.

Will try to come back with something later this week.