Results 1 to 9 of 9
  1. #1
    wwhit is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    18

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

    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"
    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.





    Any help is greatly appreciated.
    Last edited by wwhit; 08-08-2011 at 04:00 PM.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If that apostrophe is really in the code then the wherecondition (strFilter) is not being used. For two textboxes:

    strFilter = "[Text1] >0 AND [text0] > 0"

    Or "OR" as appropriate to your needs.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    wwhit is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    18
    No the apostrophe is not being used in the code. Just took it out to test other options. I was thinking I had to write code to first look at the Employee Number. Any help is greatly appreciated.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would help to see the actual code, and what "not working" means. Do you get an error, unexpected result, etc?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    wwhit is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    18
    It is asking for the user to enter a parameter value for the text box. Here is the actual code:

    Private Sub Command6_Click()

    Dim strFilter As String

    strFilter = "[Text1] >0"

    DoCmd.OpenReport "2011 Sick Abusers Letter Union Tbl 10~11 MM Report redo", acViewPreview, , strFilter

    End Sub

    Also the Text Boxes are in the GroupFooter1. If I move the text boxes to "detail" then it will filter correctly but give incorrect information in the text boxes.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, you want the name of a field in the data source there, not the name of a textbox.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    wwhit is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    18
    If I understand you correctly, I can't filter on a calculation in a report. If this is true, then is there another way around the situation?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is it feasible to move the calculation to the query that is the report's source, which would give you a field to filter on?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    wwhit is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2011
    Posts
    18
    I will try that however 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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  2. One main report from multiple reports ?
    By skocev in forum Reports
    Replies: 4
    Last Post: 03-31-2011, 02:30 PM
  3. Subform Filter based on Main Form Information
    By james.carpenter2 in forum Forms
    Replies: 0
    Last Post: 02-16-2011, 09:55 PM
  4. filter report via bound text box
    By JeremyPSU in forum Reports
    Replies: 2
    Last Post: 11-04-2010, 11:03 AM
  5. Creating Report using Text boxes & db fields
    By Nancy J. in forum Reports
    Replies: 13
    Last Post: 07-20-2009, 07:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums