Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249

    How do you display a field that shows the percentage of times a word is in a table?


    I'm not sure if this should be in the query or Reports subform, so if it is in the wrong place please move it as needed.

    I have a form, SystemPerformance_frm, that allows the user to select and event and open the report, EventSystemPerformance_rpt, filtered to show only the data from the selected Event. Two of the pieces of data that the report displays is BOARunResults and SBIRSRunResults. The possible records for those two fields are Nominal, Off Nominal, and Did Not Participate. I would like to have fields on EventSystemPerformance_rpt that shows the percentage of each of those. Is there a way to have Access count the total number of entries returned and then calculate the percentage of those that are Nominal, Off Nominal, and Did Not Participate.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Should be fairly easy to to using dCount in the report:
    =dCount("*","[YourTable]","[EventID] = " & Me.EventID & " AND [BOARunResults] = 'Nominal')/dCount("*","[YourTable]","[EventID] = " & Me.EventID)

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by Gicu View Post
    Should be fairly easy to to using dCount in the report:
    =dCount("*","[YourTable]","[EventID] = " & Me.EventID & " AND [BOARunResults] = 'Nominal')/dCount("*","[YourTable]","[EventID] = " & Me.EventID)

    Cheers,
    Thanks Vlad. Is that code going to be in the Control Source for my field or some type of Event to get it to populate when the report is open? I tried putting it in as the Control Source, but it kicked back syntax errors.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It would go in the control source of each of the 6 textboxes (3 for BOA and 3 for SBIR results). Have you adjust it for names? Can you post what you tried?

    Cheers.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    I simply added a Text Box and entered this code into it:

    Code:
    =dCount("*","[RunResultData]","[Event] = " & Me.Event & " AND [BOARunResults] = 'Nominal')/dCount("*","[RunResultData]","[Event] = " & Me.Event)
    RunResultData is the table that all the data is stored in.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    What data type is Me.Event? If it is numerical it should work, but if it is the text you need to add single quotes:


    =dCount("*","[RunResultData]","[Event] = '" & Me.Event & "' AND [BOARunResults] = 'Nominal')/dCount("*","[RunResultData]","[Event] = '" & Me.Event & "'")
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Event is text so I tried that last code you posted and I still get an Invalid Syntax error.

  8. #8
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Oh, and for text boxes it is only giving me the option to use the Expression Builder and not VBA. Is that normal?

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Anyway you can post a sample with this report? You can also try to use the calculations in the query feeding the report, but that might slow it down depending on how much data your report is showing.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't use Me. qualifier in ControlSource. Me. works in VBA as alias for object code is behind. Remove Me. .

    Yes, ControlSource uses expression builder or just type whatever.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Of cource June7 is right, try =dCount("*","[RunResultData]","[Event] = '" & [Event] & "' AND [BOARunResults] = 'Nominal')/dCount("*","[RunResultData]","[Event] = '" & [Event] & "'") (assuming you have a textbox on the report named Event with Event as the control source (sometimes you might get a circular reference error in such cases and you need to rename the control).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    I do have a text box on this report named Event with its control source set as Event. With the last code you posted I still get a syntax error. I'll see if I can dumb down a db with just this in it to post for you.

  13. #13
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Here is a stripped down database you can use to see how I have things setup.

    StrippedDatabase V1.0.zip

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you go.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Thanks Vlad, that of course did the trick. I'll have to dive into that and see what I was missing. I also liked your idea of adding the event name in with the percentage results. I moved all that stuff up to the header area and have the event name shown as part of the report title.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Display a percentage indicator in a field.
    By Chris6789 in forum Access
    Replies: 3
    Last Post: 07-14-2019, 07:58 AM
  2. Replies: 1
    Last Post: 03-11-2019, 04:42 PM
  3. Replies: 3
    Last Post: 08-25-2015, 02:26 PM
  4. Replies: 14
    Last Post: 02-23-2012, 06:32 PM
  5. Replies: 5
    Last Post: 08-15-2011, 07:12 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