String (via non-visible txtbox) from Search Form as Criteria using checkboxes

[redneckfiveo]

String from Search Form checkboxes as Query Criteria

Hello! :banghead:
Well that let's you know where I'm at . I have an unbound form (named frmReportSearch) with unbound text & combo boxes providing the criteria for a query (named qSeqStreets). The form / query utilize 4 optional fields as search criteria plus date from / to. The results are returned via a report (named rptSeqStreets). The whole operation worked perfectly, however I realized I needed to change one of the criterion to a multivalued field. The change in the table (named Tasks) worked perfectly. I used three checkboxes (named chkA, chkB and chkC) to allow the user to select any combination of the 3 choices, including none (to be treated as no filter on [fldShifts]).

The three options in the field (named fldShifts) are "A" "B" and "C". I am able to manually run the query from design view by typing in the criteria "A" Or "B"... "A" Or "B" Or "C"... and any combination of the three options in the criteria box and running the query. I am using the following code under the OK button's OnClick. The Code below has other items related to all the options so you may want to scroll to the ****. I didn't want to give partial code so you may understand better:

Private Sub btnOK_Click()
    Dim strShift As String
    Dim strA As String
    Dim strB As String
    Dim strC As String
    
    If (IsNull(Me![cboPrimaryStreet] & Me![cbo2ndStreet] & Me![cboBeat] & Me![txtStartDate] & Me![txtEndDate]) And (Me![chkA] & Me![chkB] & Me![chkC] = 0)) Or ((Me![cboPrimaryStreet] & Me![cbo2ndStreet] & Me![cboShifts] & Me![cboBeat] & Me![txtStartDate] & Me![txtEndDate]) = "" And (Me![chkA] & Me![chkB] & Me![chkC] = 0)) Then
    
      Answer = MsgBox("Leaving the search options blank with result in displaying every record. Do you want to display all records?", vbYesNo + vbExclamation + vbDefaultButton2, "Information")
            If Answer = vbNo Then
    
        Exit Sub
        Else
        End If
     Else
    End If
   
  
 ' *****This is the code that makes the strings ****
    If (Me.chkA) = True Then
        If (Me.chkB) Or (Me.chkC) = True Then   'testing
            strA = Chr(34) & "A" & Chr(34) & " Or "
            
        Else
            strA = Chr(34) & "A" & Chr(34)
        End If
    Else
    'do nothing as checkbox "A" is not checked
    End If
        
    If (Me.chkB) = True Then
        If (Me.chkC) = True Then    'testing
            strB = Chr(34) & "B" & Chr(34) & " or "
        Else
            strB = Chr(34) & "B" & Chr(34)
        End If
    Else
    End If
    If (Me.chkC) = True Then
        strC = Chr(34) & "C" & Chr(34)
    Else
        
    End If
    
    strShift = strA & strB & strC
    txtstrShift = strShift   ' this is currently where I direct the query criteria to
        
    'this was used for debug MsgBox (strShift)
        
    'If DCount("*", "qSeqStreets") > 0 Then 'removed for troubleshooting   
    
    DoCmd.OpenReport "rptSeqStreets", acViewReport, , , acWindowNormal
     Forms.frmReportSearch.Visible = False
        
    'Else      'removed for troubleshooting   
    'MsgBox "There are no records to display that match your Search, please try again"       'removed for troubleshooting   
    'DoCmd.Close acReport, "rptSeqStreets", acSaveNo         'removed for troubleshooting   
                
 End Sub

My problem is that the query criteria needs to be entered into the criteria box with quotes and separated by "Or" depending on if multiple checkboxes are selected.

I can get the results to show correctly in the textbox, however I imagine the query is adding an extra set of ""s to the string so rather than "A" Or "B" .. it is getting ""A" Or "B"". My query Sql and even design mode are pretty complex, so I wouldn't know how to use the sql in VBA without blowing some fuses. Any help would be awesome!!

BTW... first thread, I'm a noob. Thanks

 

[redneckfiveo]

Anybody at all? I think someone would have an answer?? Or guidance.