QueryDef criteria using OR

[hockeyfan21]

HI, apologies if my post appears twice, I did not see where my first one got posted.

I have a form with 3 combo boxes that filter another form/report. The first combo (cboByCategory) contains options from 2 different fields within the same table. Before I added this add'l piece of code, all 3 combos worked fine. I am not getting error messages, it just does not filter on the other 2 combo boxes - cboDiv and cboGender.

Would appreciate someone taking a look and letting me know where my syntax could be off.

Private Sub cmdModifyRecords_Click()

On Error GoTo Err_cmdModifyRecords_Click
    Dim stDocName As String
    Dim strFilter As String
    Dim stLinkCriteria As String
  
   stDocName = "Modify_OpenItems"
 
'9/5/14 thart updated to allow selection of all Divisions


If IsNull(cboDiv) And IsNull(cboGender) Then
[B]stLinkCriteria = "[Category]= '" & Me.cboByCategory & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "'"[/B]
ElseIf IsNull(cboGender) Then

stLinkCriteria = "[Category]= '" & Me.cboByCategory & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "' AND [Division]= '" & Me.cboDiv & "'"
ElseIf IsNull(cboDiv) Then
stLinkCriteria = "[Category]= '" & Me.cboByCategory & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "' AND [Gender]= '" & Me.cboGender & "'"
Else: stLinkCriteria = "[Category]= '" & Me.cboByCategory & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "' AND [Division]= '" & Me.cboDiv & "' AND [Gender]= '" & Me.cboGender.Column(1) & "'"

End If
   
 Me.txtRecordCount.Value = DCount("[ASR User Id]", "LateAdds_Current", stLinkCriteria)

 
 If Me.txtRecordCount.Value = 0 Then
 MsgBox "No records available for the selections made", vbOKOnly
 Else: DoCmd.OpenForm stDocName, , , stLinkCriteria
 
  End If
Exit_cmdModifyRecords_Click:
'
    Exit Sub

Err_cmdModifyRecords_Click:
    MsgBox Err.Description
    Resume Exit_cmdModifyRecords_Click
End Sub

Thank you!

 

[JHB]

Try to watch the stLinkCriteria after the criteria is added, using MsgBox or Debug.Print, seeing if it is as you expected.

 

[hockeyfan21]

Thanks for the tip, I did this and it looks like what I should expect but the form is not filtered for what is showing -

[Category]= '2000 - CAMPING' OR [ProdtSubCatLongDesc]='2000 - CAMPING' AND [Division]= 'TENTS' AND [Gender]= 'MENS'

 

[hockeyfan21]

Think I've got it sorted out

stLinkCriteria = "[Category]= '" & Me.cboByCategory & "' AND [Division]='" & Me.cboDiv & "' OR [ProdtSubCatLongDesc]='" & Me.cboByCategory & "' AND [Division]= '" & Me.cboDiv & "'"

Added my other combo boxes to the first portion of the criteria, it was just looking at Division and/or Gender if cboByCategory = [ProdtSubCatLongDesc]and ignoring [Category]

Thank you so much for your help.

 

[JHB]

Good you got it solved.

 

[namliam]

Be carefull when mix and matching AND and OR statements, this can yield unexpected results unless you bracket things to FORCE SQL to get things right using brackets

Also to me these long lines become unreadable fast. Readable code is maintainable code...

That put together might yield code looking something like:

stLinkCriteria = "(     [Category]           = '" & Me.cboByCategory & "'" & _ 
            "       AND [Division]           = '" & Me.cboDiv & "') " & _ 
            "  OR (     [ProdtSubCatLongDesc]= '" & Me.cboByCategory & "'" & _
            "       AND [Division]           = '" & Me.cboDiv & "' ) "

Or perhaps something like

stLinkCriteria = "      [Division]           = '" & Me.cboDiv & "' " & _ 
            "  AND (    [ProdtSubCatLongDesc]= '" & Me.cboByCategory & "'" & _
            "        OR [Category]           = '" & Me.cboByCategory & "'"  ) "