How to use many checkbox values in a Query

Question

My goal is to return the results of 5 textboxes into a SQL query, by incorporating the Query string with the variables.

How can I get my code to function so that when a checkbox is checked, the value (eg: ID, SC...) is recorded and placed into a Query? And if a checkbox is not checked, then it is not placed into the query.

The 5 checkboxes are as follows:

Checkbox picture

The code I current have to record whether a textbox is selected, and to place the value (eg: ID, SC, AS...) into a variable is as follows:

If (Me.BoxID = False) And (Me.BoxSC = False) And (Me.BoxASSC = False) And (Me.BoxAS = False) And (Me.BoxEH = False) Then
MsgBox "You must check a Fonction Checkbox", 0
Exit Sub
Else
    If (Me.BoxID= True) Then IDValue = Chr(34) & "ID" & Chr(34) Else IDValue = """"""
    If (Me.BoxSC= True) Then SCValue = Chr(34) & "SC" & Chr(34) Else SCValue = """"""
    If (Me.BoxASSC= True) Then ASSCValue = Chr(34) & "ASSC" & Chr(34) Else ASSCValue = """"""
    If (Me.BoxAS= True) Then ASValue = Chr(34) & "AS" & Chr(34) Else ASValue = """"""
    If (Me.BoxEH= True) Then EHValue = Chr(34) & "EH" & Chr(34) Else EHValue = """"""
End If

fonctionQryString = "(((tblF.f1)=" & IDValue & ") OR " + "((tblF.f1)=" & SCValue & ") OR " + "((tblF.f1)=" & ASSCValue & ") OR " + "(tblF.f1)=" & ASValue & ") OR " + "(tblF.f1)=" & EHValue & ")))"

The fonctionQryString goes into the WHERE section of the SQL Query.

I know that the method I'm using is not efficient, even though it works.

My problem is that I don't know how to do this another way. I want my code to function so that when a checkbox is not checked, it doesn't go into the Query string.

Any help would be much appreciated.

HansUp · Accepted Answer · 2012-11-17 19:29:30Z

2

These two WHERE clauses should produce equivalent results. Consider switching to the second form.

WHERE tblF.f1 = "ID" OR tblF.f1 = "SC" OR tblF.f1 = "AS"
WHERE tblF.f1 IN ("ID","SC","AS")

Here is a rough and untested code sample to produce a similar WHERE clause based on my understanding of what you're trying to achieve.

Dim fonctionQryString As String
Dim lngLoopNum As Long
Dim strControlName As String
Dim strValueList As String
For lngLoopNum = 1 To 5
    Select Case lngLoopNum
    Case 1
        strControlName = "ID"
    Case 2
        strControlName = "SC"
    Case 3
        strControlName = "ASSC"
    Case 4
        strControlName = "AS"
    Case 5
        strControlName = "EH"
    End Select
    If Me.Controls("Box" & strControlName) = True Then
        strValueList = strValueList & "," & Chr(34) & _
            strControlName & Chr(34)
    End If
Next
If Len(strValueList) > 0 Then
    fonctionQryString = "tblF.f1 IN (" & Mid(strValueList, 2) & ")"
Else
    MsgBox "You must check a Fonction Checkbox"
End If

I assumed you didn't actually want to include the condition, WHERE tblF.f1 = "" (an empty string). If I guessed wrong, you'll have more work to do, but hopefully this will still point you to something useful.

answered Nov 17, 2012 at 19:29

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paolo Bernasconi Over a year ago

You're correct in assuming that I don't want to include the empty string (tblF.f1 = ""). I didn't know about the IN expression, and your code is very helpful, thank you!

Collectives™ on Stack Overflow

How to use many checkbox values in a Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related