Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    8

    Unanswered: Problem filtering query using checkboxes and option groups

    I am trying to filter a query based on 3 check box selections and 2 seperate option groups

    I am using

    SELECT tblNames.LAST, tblNames.FIRST, tblNames.PORT, tblNames.OFFICER, tblNames.SUPV, tblNames.PERMIT, tblNames.DFO_ECC, tblNames.PASSPORT, tblNames.PASSPORT_EXP
    FROM tblNames
    WHERE (((IIf(forms![Report Menu]!PERMIT,[PERMIT]=True,True))=True) And ((IIf(forms![Report_Menu]!DFO_ECC,[DFO_ECC]=True,True))=True) And ((IIf(forms![Report Menu]!PASSPORT,[PASSPORT]=True,True))=True));

    for the 3 check boxes and it works perfectly.

    I am trying to add 2 different Option groups that have

    Group 1
    Officers
    Supervisors
    All

    Group 2
    Port A
    Port B
    ALL

    I want to select one button to pull up the report and filter by each of the option group selections and include all records that have yes or no values matching any of the 3 checkmarks.

    I am unsure how to incorporate the Option groups into the SQL of the query

    please help

  2. #2
    Join Date
    May 2012
    Posts
    8
    bump..anyone? this can't be that difficult for an expert user..
    any help would be very appreciated even if its just pointing me somewhere i can research myself. I have googled this topic 50 different ways to try and figure it out.

    thank you

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you use an OptionGroup control, you do not care about the individual value (or state) of each control included in the OptionGroup. When you select/deselect a CheckBox in an OptionGroup control, the Value of this OptionGroup changes and the BeforeUpdate and AfterUpdate events are fired for the OptionGroup, not for the CheckBox.

    You must then compose the filter from the value of the OptionGroups, not from the values of the CheckBoxes that are included into them.

    You should also refrain from using several IIf functions combined with multiple parentheses on a single line, this renders your code very difficult to decipher. Moreover, I'm not sure of the usefulness of such an expression (although I'm sure it can be written more clearly):
    Code:
    IIf(forms![Report Menu]!PERMIT,[PERMIT]=True,True)
    Condition: If forms![Report Menu]!PERMIT (Missing explicit evaluation --> returns True or False)
    Returned value when True: [PERMIT]=True (evaluates as True or False)
    Returned value when False: True

    This means that:
    - If the value in forms![Report Menu]!PERMIT is non-zero (True), then returns True if [PERMIT] = True, returns False otherwise.
    - If the value in forms![Report Menu]!PERMIT is zero (False), then returns True.
    Have a nice day!

  4. #4
    Join Date
    May 2012
    Posts
    8
    I am sure this made pefect sense to you but it went sailing over my head ... guess you have to know what your doing to know what your doing.

    /sigh back to googling and hoping i find something

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The first part (about OptionGroup controls) is actually very simple. Let's take an example:
    On a froms, I have an OptionGroup control: Option_Gender with 3 Checkbox controls inside of it:
    a) Check_Male (OptionValue = 1)
    b) Check_Female (OptionValue = 2)
    c) Check_Both (OptionValue = 3)

    If I set the DefaultValue property of the OptionGroup control to 1, Check_Male (OptionValue = 1) will be automatically "checked" when the form opens.

    Now, if I want to know which option (1-->Male, 2-->Female or 3--> Both) is selected, all I need to do is interrogate the Value property of the OptionGroup (Option_Gender):
    Code:
    Select Case Me.Option_Gender.Value
        Case 1    ' Check_Male is "ticked"
        Case 2    ' Check_Female is "ticked"
        Case 3    ' Check_Both is "ticked"
    End Select
    If I want to use the selection to build a query string, I can use:
    Code:
        Dim strSQL As String
        strSQL = "SELECT <Field1>, <Field2>, <Field3>, etc... FROM <SomeTable>" 
        If Me.Option_Gender.Value < 3 Then
            strSQL = strSQL & " WHERE Gender = '" & IIF(Me.Option_Gender.Value = 1, "M", "F") & "';"
        Else
            strSQL = strSQL & ";"
        End If
    Or:
    Code:
        Dim strSQL As String
        strSQL = "SELECT <Field1>, <Field2>, <Field3>, etc... FROM <SomeTable>" 
        Select Case Me.Option_Gender.Value
            Case 1    ' Check_Male is "ticked"
                strSQL = strSQL & " WHERE Gender = 'M';" 
            Case 2    ' Check_Female is "ticked"
                strSQL = strSQL & " WHERE Gender = 'F';" 
           Case 3    ' Check_Both is "ticked"
                strSQL = strSQL & ";"
    End Select
    The second part simply means that the expression does not make sense to me, as it seems to always return True. However, I might be wrong as I don't know enough about your database.
    Attached Thumbnails Attached Thumbnails Option_Gender.jpg  
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •