Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DCount in a report (97)

    Hi all. I have a report (based on a query) that is grouped first by year, then by month (both are grp headers). The problem is that i can't seem to get the data to breakdown by month correctly in a calculated control. The calculated control uses the DCount function with criteria specified. The other calculated control uses the Count function and works well. Is there something i'm missing??? thanks. Van <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount in a report (97)

    We might be able to help if you gave some details of what you're trying to do.

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount in a report (97)

    well, i want the report to breakdown by year , then by month and count the number of referrrals by say .....pensioners (-1) vs non-pensioners (0). would look something like this for example if all worked well:

    2003 pensioner non-pensioner
    July 20 5
    June 15 4

    hope this helps.
    Van

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount in a report (97)

    Is Pensioner a field in the record source of the report?

  5. #5
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount in a report (97)

    yes Hans. it's value is either 0 or -1. the Count function works (to show monthly total of pensioners + non-pensioners), but not the DCount.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount in a report (97)

    Why don't you use

    =Abs(Sum([Pensioner]))

    to count the number of pensioners, and

    =Count(*)-Abs(Sum([Pensioner]))

    to count the number of non-pensioners? No DCount needed, and since the calculations use the aggregation functions Sum and Count, they will respect the grouping automatically.

  7. #7
    Star Lounger
    Join Date
    Nov 2002
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount in a report (97)

    thanks heaps Hans....that's got it. doesn't DCount respect report groupings?
    thanks, Van

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount in a report (97)

    Aggregation functions like Count, Sum, Max etc. work with the data from the record source of the report. If the report has grouping levels, Access "feeds" only the records in the current group to these functions.

    Statistical domain functions like DCount, DSum, DMax etc. work with data from a table or query; if you want to restrict them to certain records, you will have to specify the where condition (the third argument of these functions) explicitly.

Posting Permissions

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