Results 1 to 9 of 9
  1. #1
    tgall is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Location
    na
    Posts
    38

    Can I Make Access DCount Emulate Count?

    Short Trip: Is there a way to make Access DCount act like Access Count, which acts like Excel Subtotal 3?

    Scenic View: I'm building Access data entry forms that Laboratory Technicians fill in.
    The Tech fills in the Test Result and Access query formulas determine if each result is a Pass or Fail.

    In the Form Header I have unbound text boxes populated with functions to calculate the overall stats:
    Specimen Count: =Count([SpecimenID]) Acts like Excel subtotal function 3; unfiltered counts all records; filtered counts only filtered records What I want!
    Pass Count: =DCount("[PF]","qryTM00444R1","[PF]='Pass'") Acts like Excel CountIf; counts all records whether or not the form is filtered.
    Fail Count: =DCount("[PF]","qryTM00444R1","[PF]='Fail'") Ditto
    Percent Pass: =[PassCount]/[SpecimenCount] Works perfect when the form is unfiltered. When the form is filtered, the percentages are outrageous.

    Examples: Unfiltered Filtered
    Specimen Count: 2,870 14
    Pass Count: 2,338 2,338
    Fail Count: 532 532
    Percent Pass: 81 16700 (I don't think so)



    DCount was the only function that counted Pass and Fail.
    I'd like DCount to act like Count so that when the form is filtered, only the filtered records are counted and calculated.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of the database?
    And/or the SQL for qryTM00444R1.
    Can you show us the record source for the form(not sure it will help)?

    Tell us more about your Form's filter.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Looks like the calculations are correct.

    Unfiltered
    Filtered
    Specimen Count: 2,870 14
    Pass Count: 2,338 2,338
    Fail Count: 532 532
    Percent Pass: 81 16700

    What were you expecting?

    The calculation for the unfiltered Percent Pass is 2338/2870 = 81
    The calculation for the filtered Percent Pass is 2338/14 = 16700

    Why isn't the filtered pass count <= 14?
    Why isn't the filtered fail count <= 14?


    I would add the Specimen Count criteria to the DCount() function: (untested)
    Code:
    Pass Count: =DCount("[PF]","qryTM00444R1","[PF]='Pass' AND [SpecimenDate] = " & Date() )
    Change the red to your criteria that filters the form......

  4. #4
    tgall is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Location
    na
    Posts
    38
    Steve, thank you for responding.

    You're a walkin' in my shoes. Why isn't the filtered pass count <= 14? That's my challenge. DCount seems impervious to filters.
    The correct response to the filtered-to-14 records is:
    Specimen Count = 14
    Pass = 13
    Fail = 1
    Percent Pass = 93%

    I tried an AND construct, but I got a blinking ERROR#. Also I need the ability to see all the records, or three of n IDs, or any other possible combination.

    I attached a modified database in my reply to Orange's response. Please take a look.

  5. #5
    tgall is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Location
    na
    Posts
    38

    Can I Make Access DCount Emulate Count? Sample Attached.

    Orange, thank you for responding. Attached is a zip file of the highly modified database.
    Fingers crossed.
    Attached Files Attached Files

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was able to come up with two methods to calculate the percentage.... and 20 ways not to calculate the percentage.

    I have never used the right click event to filter a form. I have always used text boxes/combo boxes and a button to create a filter. I learned something new.....


    Method 1: see "LabTest text box.accdb".
    This uses two text boxes (normally hidden. I use a yellow to designate hidden controls when in design view) that holds the criteria for the DCount() function.


    Method 2: see "LabTest code.accdb".
    In this method, the two unbound text boxes (Pass/Fail) have the control source removed. VBA code executes the DCount() function and pushes the values into the Pass/Fail text box controls.
    Attached Files Attached Files

  7. #7
    tgall is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Location
    na
    Posts
    38
    Steve, thank you. Both are excellent solutions. I'm using the code version.

    Concerning your "I learned something new," most of my clients like to right click and filter. I'm fortunate to
    have really cool clients.

    giggles concerning your "20 ways not to calculate". I, too, tried me.filter in text boxes and VBA,
    but created mostly errors. You, however, succeeded.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    Lightbulb And a 3rd method...... (Best)

    So I thought about your dB over the weekend and come up with another method that does not require any code. Just a modification of the query and the formulas in the text boxes. (plus a little faster)

    Try filtering on ID = 105, then ID = 120

    Then look at the query and form..
    Remember that I use yellow to indicate hidden controls on a form
    Attached Files Attached Files

  9. #9
    tgall is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Location
    na
    Posts
    38
    ROTFL!!! How simple! I'm putting a bucket on my head and sitting in the corner.

    Thank you, Steve.

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

Similar Threads

  1. Replies: 6
    Last Post: 11-05-2014, 03:00 PM
  2. Count or DCount Not Working Properly
    By Chris1112 in forum Forms
    Replies: 3
    Last Post: 05-09-2012, 02:51 PM
  3. Count Vs Dcount
    By SWG in forum Access
    Replies: 1
    Last Post: 03-30-2012, 02:08 PM
  4. Emulate Excel VBA in Access
    By JohnBoy in forum Programming
    Replies: 14
    Last Post: 02-23-2010, 06:44 PM
  5. Using count or Dcount
    By ddcook in forum Access
    Replies: 0
    Last Post: 07-22-2009, 03:35 PM

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