Open Report with Where Clause 2 criteria on one control

[andy1968]

I'm trying to open a report with 2 criteria on one control, with no luck.


Here is the code:


Private Sub cmdPrintOpen_Click()
Dim x As String
Dim y As String
x = "Not Approved"
y = "Make Corrections Noted"
DoCmd.OpenReport "rptSubmittals", acViewPreview, , "[Job] = '" & Me![Job] & "' AND [Status] = '" & x & "' AND [Status] = '" & y & "'"
End Sub


I've got it to work with either the x or y value, but can't get it to work with both.


What is the syntax?

 

[andy1968]

Answered my own question -



Changed the code to:


DoCmd.OpenReport "rptSubmittals", acViewPreview, , "[Job] = '" & Me![Job] & "' AND [Status] = '" & x & "' OR [Job] = '" & Me![Job] AND &[Status] = '" & y & "'"

 

[plog]

I'm not certain that does work. If it does, its still bad form.

When you start mixing ANDs and ORs you should seperate the comparisons that go together with parenthesis. This is what you should do (psuedo-code):

Job=[Job] AND (Status=[x] OR Status=[y])

Its possible that the AND is only with the Status=[x] and all Status=y] records are coming thru regardless of their Job value. The parenthesis force the OR comparisons together and makes clear to humans what your intent is.

 

[andy1968]

Thanks for the tip.


Revised code worked, and is cleaner.


DoCmd.OpenReport "rptSubmittals", acViewPreview, , "[Job] = '" & Me![Job] & "' AND ([Status] = '" & x & "' OR [Status] = '" & y & "')"