How do you filter two calculated text boxes in a report?

[wwhit]

I have a report in access that I need to only have employees (“Employee Number”) who have a specific criteria in two text boxes (and/or). So instead of pulling all the employees I would like to only pull employees that have an amount in these two text boxes. I have put the following code on the "Command6_Click" button and it is not working.

Dim strFilter As String
strFilter = "[Text1] >0 or [Text0]>0"
DoCmd.OpenReport "2011 Sick Abusers Letter Union Tbl 10~11 MM Report redo", acViewPreview, , strFilter

The Text Boxes are called Text1 and Text0 and I need the information greater than zero.

Also tried:

For Each [Employee Number] In Reports![2011 Sick Abusers Letter Union Tbl 10~11 MM Report redo]
If (Sum(Nz([1], 0)) + Sum(Nz([2], 0)) + Sum(Nz([3], 0)) + Sum(Nz([4], 0)) + Sum(Nz([5], 0)) + Sum(Nz([6], 0)) + Sum(Nz([7], 0)) + Sum(Nz([8], 0)) + Sum(Nz([9], 0)) + Sum(Nz([10], 0)) + Sum(Nz([11], 0)) + Sum(Nz([12], 0))) - 40 > 0 Or (Sum(Nz([7], 0)) + Sum(Nz([8], 0)) + Sum(Nz([9], 0)) + Sum(Nz([10], 0)) + Sum(Nz([11], 0)) + Sum(Nz([12], 0))) - 24 > 0 Then
Me![Employee Number].Visible = True
Else
Me![Employee Number].Visible = False
Next
End If

The query is a cross tab query which sets the date like this:

PIVOT Year([Date of Occurrence])*12+Format([Date of Occurrence],"mm")-(Year([Forms]![Sick Abusers List Pick From Form]![Start Date])*12+Format([Forms]![Sick Abusers List Pick From Form]![Start Date],"mm"))+1 In (1,2,3,4,5,6,7,8,9,10,11,12);

And then the Text Boxes are calculations from the Start Date to the End Date as follows:

=(Sum(Nz([7],0))+Sum(Nz([8],0))+Sum(Nz([9],0))+Sum(Nz([10],0))+Sum(Nz([11],0))+Sum(Nz([12],0)))-24

=(Sum(Nz([1],0))+Sum(Nz([2],0))+Sum(Nz([3],0))+Sum(Nz([4],0))+Sum(Nz([5],0))+Sum(Nz([6],0))+Sum(Nz([7],0))+Sum(Nz([8],0))+Sum(Nz([9],0))+Sum(Nz([10],0))+Sum(Nz([11],0))+Sum(Nz([12],0)))-40

Should I do a For Each statement? And how should I write the code? Not real good with VBA but am trying. Please help!!!

 

[pbaldy]

I know I dropped the ball on your other thread, as nothing was coming to me. I just had a thought, which would be code like this in the detail section of your report:

If (Sum(Nz([7],0))+Sum(Nz([8],0))+Sum(Nz([9],0))+Sum(Nz([10],0))+Sum(Nz([11],0))+Sum(Nz([12],0)))-24 > 0 Then
  Me.Detail.Visible = True
Else
  Me.Detail.Visible = False
End If

Play with that and see if it works. It's not actually filtering the report, but it is hiding the records from the user.

 

[wwhit]

Tried that already in the GroupFooter_1 section because that is where the text boxes are located. Already have the code as such:

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As Integer)
Const WHITE = 16777215
Const RED = 255
Const BLACK = 0
If (Me![Text1]) > 0 Then
Me![Label44].ForeColor = RED
Me![Text9].ForeColor = BLACK
Me![Text48].ForeColor = WHITE
Me![Text9].Visible = True
Me![Text48].Visible = False



Else
Me![Label44].ForeColor = WHITE
Me![Text9].ForeColor = WHITE
Me![Text48].ForeColor = BLACK
Me![Text48].Visible = True
Me![Text9].Visible = False


End If

If (Me![Text0]) > 0 Then
Me![Label45].ForeColor = RED
Me![Text12].ForeColor = BLACK
Me![Text49].ForeColor = WHITE
Me![Text12].Visible = True
Me![Text49].Visible = False



Else
Me![Label45].ForeColor = WHITE
Me![Text12].ForeColor = WHITE
Me![Text49].ForeColor = BLACK
Me![Text12].Visible = False
Me![Text49].Visible = True



End If



End Sub

 

[wwhit]

I also tried to do another crosstab query on the original crosstab query so that I could do the calculations there then have it filter criteria with greater than zero (as you suggested to do the filter in a query). Still not working. I am doing something so wrong. It is driving me up the wall. Please help

 

[pbaldy]

Can you post a sample db here, with the expected result?

 

[wwhit]

I have the sample database ready. It should be attached.

 

[wwhit]

When you open the report I only want the information for the one employee (Jane Doe) and no info for the other (John Doe). Let me know if it makes sense. Thanks.

 

[pbaldy]

That's the only record coming up when I run it. What criteria should I be using?

 

[wwhit]

You should have two pages of records. One for John and one for Jane. Use a year in the criteria. Start Date = 09/01/2010 End Date = 08/31/2011 and DepartmentName as "Finance".

 

[pbaldy]

This appears to get us on the right track:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
  If (Me![Text1]) > 0 Then
    Me.GroupFooter1.Visible = True
  Else
    Me.GroupFooter1.Visible = False
  End If
End Sub


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
  If (Me![Text1]) > 0 Then
    Me.GroupHeader0.Visible = True
  Else
    Me.GroupHeader0.Visible = False
  End If
End Sub

 

[wwhit]

Where is the Text1 in the GroupHeader0?

 

[pbaldy]

It doesn't need to be there. The code is just looking for the textbox, wherever it may be.

 

[wwhit]

That worked!!! Thank you so much!!! Much appreciative and thanks for sticking with me...

 

[pbaldy]

No problem, glad we finally found a solution. I assume you're going to do the same thing with the page header.

 

[wwhit]

It is working so well. The only thing I get is one empty page at the end. I did put the code in the page header too. Don't really care about that one empty page as long as it is working. Again thank you so much!!!

 

[pbaldy]

Try changing the Force New Page property of the group footer to none. It's currently After Section, which I think is where the blank page is coming from.

 

[wwhit]

That worked. No more blank page if the report has information. What about if it doesn't have information? I have put this code in the Report:

Private Sub Report_NoData(Cancel As Integer)
MsgBox ("There are No Employees that have a Notification of Excessive Use of Sick Leave in the " & [Forms]![Sick Abusers List Pick From Form]![DepartmentName] & " Department."), vbOKOnly
Cancel = True
End Sub

This works only if no criteria is met. What do you think?

 

[pbaldy]

The No Data event is the common way of aborting the report if there are no records. You do have to trap for the 2501 error that will be thrown back to the code that opened the report. If you're not familiar with error trapping:

http://www.baldyweb.com/ErrorTrap.htm

 

[wwhit]

I had this code to "trap" the error:

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "2011 Sick AbuserTbl MM Report"
DoCmd.OpenReport stDocName, acPreview
Exit_Command6_Click:
Exit Sub
Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click
End Sub
Is that the same as trapping the 2501? Not sure. It isn't giving an error but it is pulling one page that is blank.

 

[pbaldy]

That's a generic error trap, so I assume when there are no records you get two message boxes. First the one from your No Data code, then the generic error from the error trap.