Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    27

    Unanswered: Dcount on a Report to get specific records

    MS Access 2007

    Hello,

    I am trying to get a report to count the number of questions that were answered yes to by different store numbers on a report.
    I created a query that shows the unique store numbers and filtered by "Yes". (stores can have more than one entry but I don't need to report a single store 10 times)

    Here is what the report looks like

    Store Question # of ques (yes) Report Page Header
    200 Yes text box Report Detail
    205 Yes text box
    210 Yes text box

    I can get the dcount to work if I specify the store number in the formula. The only issue with that is that every store shows the same result.

    Store field is named Store
    Tbl is named Data
    text box is named Result

    dcount("*","Data","[store]='210'",)
    This gives me the number of results for store 210 but not the Yes ones.

    dcount("*","Data","[q1]='Yes'")
    This gives me all the Yes results for Question 1.

    I need to know if I get set it up to look at the store field and give me the result based on that value so each store is different.

    Store Question # of ques (yes) Report Page Header
    200 Yes 2 Report Detail
    205 Yes 5
    210 Yes 17

    Any thoughts, suggestions or answers are greatly appreciated.

    Thanks in advance for your help.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    dcount("*","Data","[store]='210' and [q1]='Yes')

    but you really shouldnt use Dcount in a report , or a query.
    The query should be doing the counting, and the report just shows it.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the third parameter of a domain function such as dcount is effectively a SQL WHERE clause, but omits the word WHERE. so that parameter can be as complex as you see fit

    eg
    Code:
    dcount("*","Data","[store]='210'" and [q1]='Yes'")
    however I'd caution against 'overusing' domain functions, especially in report domain functions have their place in VBA and Access, but they come at a cost. occasional use great, but NOT if your report refers to them constantly or frequently.
    one of the things that many people don't appreciate is that you can run VBA code behind the scens of an Access report. so if this data is present in the report already, you can do whatever domain function yourself (including calculating averages, min / max and so on).
    'all that you need do is
    create an empty control where you want to report the value. you can use one control per value, or stuff all the values into one or few controls

    create some variables in the report (in the reports global declarations section)

    then zero those variables in whatever relevant header section (be that page, group, wahtever)
    add to those variables in the relevant report detail (or header/footer sections)
    then transfer the value of those variables, suitably formatted to the control(s) defined in the first step

    ...why would you want to go through all this malarkey when the domain function could do this sort of thing for you, and you dont' have to worry about writing, testing code...

    well its simple really
    speed. each call to a domain function costs some time and resources. if you are sucking data from the network it can choke a network connection. if the data is already in your report essentially there is no need to request it again from the data source. although not entirely 'free' it carries virtually no performance penalty compared to using a domain function.
    you can be as creative as you need. ferintance say your users want to know the number answering yes to q1,2 & 3 as as well as individually. well thats 4 domain functions compared to a few lines of VBA

    domain functions still have a role but be very very wary of using lots in the same report / form section.

    its ancient history but IIRC I knocked around 4 minutes of a reports generation time by getting rid of all the domain functions in a report that used averages, SD and other functions. I think the execution time came down from over 5 minutes to less than 30 seconds


    just a comment if the store is a number than you shouldnt' delimit the value
    Code:
    dcount("*","Data","[store] = '210'" and [q1]='Yes'")
    becomes
    Code:
    dcount("*","Data","[store] = 210" and [q1]='Yes'")
    equally althoiugh Access displkays data as yes/no, (usually the underlyign datatype is true or false
    Code:
    dcount("*","Data","[store] = 210" and [q1] = " & vbtrue )
    Last edited by healdem; 05-22-15 at 09:44.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2012
    Posts
    27
    WOW. Thank you very much for the great information. It is a lot to think about. I am clearly not as advanced as you guys, but I would love to try what you are saying about the VBA. It would make much more sense to do that and cut down the processing time. There will be thousands of records in this db and I now worry that I may be creating a problem down the road.

    I still have one part of my question that I am hoping someone can help with.
    In the report, it lists the store numbers from the table. If I were to put this formula in the text box, it will only give me results for store 210. I need to know how to get it to read the field store and give me the count based on that field and a different count for the next, etc.

    This is what I have in my head (it doesn't work)

    code:
    dcount("*","Data","[store] = [store]" and [q1]='Yes'")


    I may only use this until I learn enough about what healdem mentioned as a better solution.

    Thanks for the great responses!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Well what you could do is set a filter on the report when opening it. That filter could be based on a value selected from a list box.
    When you open a report effectively you run a macro called docks.openreport. One of the parameters that you can pass as part of the openreport is to set a filter. Read up on how to use openreport.

    If it helps think of a firm or report as a window on your data. But through query and filter design you can alter the data displayed in the form or report but not the layout.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2012
    Posts
    27
    Thanks for all the help. I have worked out a solution.
    So thankful this site exists.

  7. #7
    Join Date
    Apr 2012
    Posts
    27
    My apologies, I am not sure how to close this out or mark as solved.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •