How count "Y" values and "N" values?

Posted on 2009-02-18
Last Modified: 2012-06-21
I have a report which in the detail section shows records that contain 2 date fields.  On of them is named "txtDateShpd" and the other is named "txtAck_Date".  The 2 control source fields from the underlying query are "DateShpd" and "Ack_Date".  On the report then for each record I display "Y" if the "txtDateShpd" is <= "txtAck_Date".  I display "N" if the "txtDateShpd" is > "txtAck_Date".  The syntax for this Y/N field on the report in the underlying query is... OnTime: IIf([DateShpd]<=[Ack_Date],"Y","N").  But I also show the qty shipped for each record on the report.  I also sum the qty shipped for all records in the footer of the report with a field named "txtTotalQtyShipped".  The syntax for this field is =Sum([ShipQty]) .   This detail and footer sections of the report are working just fine.

But my issue is that I need to sum the qtys for the "Y" records on the report in the footer section and also need to sum the qtys for the "N" records on the report.  How do I do this?

I'm attaching a jpg file of the report to show you what I'm talking about.
Question by:SteveL13
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Add two hidden text boxes on the report in the Detail section

    Control Source:
    =IIF([txtDateShpd] = "Y", 1 , 0)
    Set the Running Sum property to OverAll

    Control Source:
    =IIF([txtDateShpd] = "N", 1 , 0)
    Set the Running Sum property to OverAll

    In your footer where you are displaying the totals ... add text boxes for these totals ... and reference the text boxes above in the Control Source:




    Author Comment

    Almost correct but probably because I didn't explain it well.  Your solution is counting the "Y"'s and the "N"'s.  But wnat I need to sum is the shipment qtys for the "Y"'s and the "N"'s.  So using the above pasted example, the total "Y" count should be 102 and the total "N" count should be 27.  How do I get to that?

    Also, I had to change your code lines to:
    =IIf([txtOnTime]="Y",1,0) and =IIf([txtOnTime]="N",1,0)
    LVL 75

    Accepted Solution

    "Also, I had to change your code lines to:"

    OOPS!  Sorry.  Lets try this mod .... not sure what the name of the Qty Shipped control is ... so I've called txtQtyShpd :


     =IIf([txtOnTime]="N", [txtQtyShpd],0)


    Author Closing Comment

    Absolutely perfect!  Thank you!
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    " Grade:  A - "Absolutely perfect!  Thank you!"  

    You are welcome.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now