Use 2 Select Cases as criteria to run report 1

[pbrown77]

In order to cut down on the number of reports to create. Basically 8 of each report. 4 per facility and 2 facilities, I would like to use to select cases which would enable the user to select:
From Option Group:
Location
1 or 2

and from Option Group:
Labor
1, 2, 3 or 4


Location group 1 criteria:
[CC Summary 2nd Level] = 1340 or >3999 and not between 7700 and 7799
Location group 2 criteria:
[CC Summary 2nd Level] = 9500 or 1800 or 1801 or 1805 or 1905 or 1350 or between 3000 and 3999 or between 7700 and 7799

Labor Code group 1 criteria:
[Labor Code] = D or P or K or H or J or E or N
Labor Code group 2 criteria:
[Labor Code] = G or Q
Labor Code group 3 criteria:
[Labor Code] = R or L or O or F
Labor Code group 4 criteria:
[Labor Code] = V or Y or X


I understand that I will need to put the code then the ultimate criteria in the on click of the button associated with each report.

Right now I am having difficulties in wording the Cost center criteria using "and between.... or between..."

 

[smandoli9]

It looks like you are taking one report and applying a filter to the recordsource. Is that true?

If so, and the report fails or doesn't give the right results, you should use a query to test and correct the filter syntax.

[purple]_______________________________
[sub]Never confuse movement with action -- E. Hemingway [/sub][/purple]

 

[pbrown77]

That is just it. I have been using queries and setting up 1 query for each combination and I would like to stop that because I end up maintenaning several reports when a change is requested. I would like to change on report not 4 different reports so that all options reports are the same.

 

[pbrown77]

Here is the code and it is currently giving "expect end of statement" at Select Case Me.Location at the 7700 in the following string ..... strwhere = [CC Summary 2nd Level] = 7800 Or [CC Summary 2nd Level] >= 7800 and not between 7700 and 7799

If I leave all code behind >=7800 the error, "can not find the field "l"

I am at a loss....



Private Sub Command23_Click()
Dim strwhere As String
Dim strwhere1 As String
Dim strwhere2 As String





If [Location] = 0 Then
MsgBox "No facility selected"
Exit Sub
End If
If [Labor] = 0 Then
MsgBox "No labor type selected"
Exit Sub
End If

Select Case Me.Location
Case 1
strwhere = [CC Summary 2nd Level] = 7800 Or [CC Summary 2nd Level] >= 7800 and not between 7700 and 7799
Case 2
strwhere = [CC Summary 2nd Level] = 1350 Or [CC Summary 2nd Level] = 1800 Or [CC Summary 2nd Level] = 1801 Or [CC Summary 2nd Level] = 1805 Or [CC Summary 2nd Level] = 1905 Or [CC Summary 2nd Level] = 9500 and [CC Summary 2nd Level] is between 3000 and 3999 or between 7700 and 7799

End Select

Select Case Me.Labor
Case 1
strwhere2 = [Labor Code] = "D" Or [Labor Code] = "P" Or [Labor Code] = "K" Or [Labor Code] = "H" Or [Labor Code] = "J" Or [Labor Code] = "E" Or [Labor Code] = "N"
Case 2
strwhere2 = [Labor Code] = "G" Or [Labor Code] = "Q"
Case 3
strwhere2 = [Labor Code] = "R" Or [Labor Code] = "L" Or [Labor Code] = "O" Or [Labor Code] = "F"
Case 4
strwhere2 = [Labor Code] = "V" Or [Labor Code] = "Y" Or [Labor Code] = "X"
End Select


strwhere = strwhere1 & strwhere2

DoCmd.OpenReport "W - TEST - 1", acViewPreview, , strwhere
End Sub

 

[Eupher]

Hi, pbrown77,

Okay, first of all, you're assigning values to string variables, so you must use quotes! And you have a logic problem in your criteria - 9500 meets the criteria for *both* Group 1 and 2. But I'm not sure what the criteria for Group 1 is really supposed to be, because your Select Case statement doesn't match the criteria in your original post:

Location group 1 criteria:
[CC Summary 2nd Level] = 1340 or >3999 and not between 7700 and 7799

Or...

[CC Summary 2nd Level] = 7800 Or [CC Summary 2nd Level] >= 7800 and not between 7700 and 7799

Based on your original post, try this:

Select Case Me.Location
    Case 1
        strwhere = "([CC Summary 2nd Level] = 1340) OR " _
        & "([CC Summary 2nd Level] > 3999 AND " _
        & "[CC Summary 2nd Level] <> 9500 AND " _
        & "[CC Summary 2nd Level] Not Between 7700 And 7799)"
    Case 2
        strwhere = "([CC Summary 2nd Level] In (1350, 1800, 1801, 1805, 1905, 9500)) OR " _
        & "([CC Summary 2nd Level] Between 3000 and 3999) OR " _
        & "([CC Summary 2nd Level] Between 7700 and 7799)"
        
End Select

Select Case Me.Labor
    Case 1
        strwhere2 = "([Labor Code] In ('D', 'P', 'K', 'H', 'J', 'E', 'N'))"
    Case 2
        strwhere2 = "([Labor Code] In ('G', 'Q'))"
    Case 3
        strwhere2 = "([Labor Code] In ('R', 'L', 'O', 'F'))"
    Case 4
        strwhere2 = "([Labor Code] In ('V', 'Y', 'X'))"
End Select    

strwhere = strwhere1 & " AND " & strwhere2

DoCmd.OpenReport "W - TEST - 1", acViewPreview, , strwhere

HTH,

Ken S.

 

[pbrown77]

First off, sorry for the double post.... That was a pure mistake.

Ken,
Here is what comes up while using the code:

Syntax error (missing operator) in query expression '(AND([Labor Code]In('D','P','K','H','J,','E','N')))'


This is the first I have seen anything like this, so I am at a complete loss.

 

[Eupher]

Ummm... That's not using the code as I posted it... There is an extra set of parentheses, and all the spaces are missing...

Ken S.

 

[pbrown77]

All I did was copy and paste. let me re-try.

 

[pbrown77]

Copied and re-pasted, same error...

 

[Eupher]

Just for fun, add this line to your code immediately BELOW the strwhere = strwhere1 & " AND " & strwhere2 line:

Debug.Print strwhere

Then open a code window and copy/paste the result of strwhere here so we can see what's actually being assembled.

Ken S.

 

[Eupher]

Oops, I see it...

Select Case Me.Location
    Case 1
        strwhere[COLOR=red][b]1[/b][/color] = "([CC Summary 2nd Level] = 1340) OR " _
        & "([CC Summary 2nd Level] > 3999 AND " _
        & "[CC Summary 2nd Level] <> 9500 AND " _
        & "[CC Summary 2nd Level] Not Between 7700 And 7799)"
    Case 2
        strwhere[COLOR=red][b]1[/b][/color] = "([CC Summary 2nd Level] In (1350, 1800, 1801, 1805, 1905, 9500)) OR " _
        & "([CC Summary 2nd Level] Between 3000 and 3999) OR " _
        & "([CC Summary 2nd Level] Between 7700 and 7799)"
End Select

Ken S.

 

[pbrown77]

Only one item seems to be missing...
strwhere1 case 1 working with all strwhere2

however,
strwhere1 case 2 is not. It is pull all labor codes no matter which case for strwhere2 is used.......

 

[Eupher]

Hmmm...

Try this:

strwhere = "(" & strwhere1 & ")" & " AND " & "(" & strWhere2 & ")"

Ken S.

 

[pbrown77]

How did you do it?!?

That is all I can say!

 

[Eupher]

It's magic... ;-)

Just a matter of grouping the conditions to get the proper result.

Ken S.

 

[pbrown77]

Got a small request from users.

On one of the reports they do not need to see all of the data for a facility, only 1 summary at a time.

I tried changing:
Select Case Me.Location
Case 1
strwhere1 = "([CC Summary 2nd Level] = 1340) OR " _
& "([CC Summary 2nd Level] > 3999 AND " _
& "[CC Summary 2nd Level] <> 9500 AND " _
& "[CC Summary 2nd Level] Not Between 7700 And 7799)"
Case 2
strwhere1 = "([CC Summary 2nd Level] In (1350, 1800, 1801, 1805, 1905, 9500)) OR " _
& "([CC Summary 2nd Level] Between 3000 and 3999) OR " _
& "([CC Summary 2nd Level] Between 7700 and 7799)"
End Select

To:
strwhere1 = "[CC Summary 2nd Level] = 'Me.CCS1'"

But the formatting must be wrong.

CCS1 references an unbound text box on the form where the user can enter their CC Summary 2nd Lever number.

 

[Eupher]

Try this:

strwhere = "[CC Summary 2nd Level] = '" & Me.CCS1 & "'"

Ken S.

 

[pbrown77]

Works great, Thanks!