Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2009-02-18
Medium Priority
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
  • 3
  • 2
LVL 75
ID: 23676620
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

ID: 23676724
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

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 23676798
"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

ID: 31548557
Absolutely perfect!  Thank you!
LVL 75
ID: 23677335
" Grade:  A - "Absolutely perfect!  Thank you!"  

You are welcome.


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

572 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