Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Returning a Count in a Report (2000)

    I am creating a report that needs to return the count of active vs inactive accouts. The account active status is tracked with a yes/no control called "Active". When I use =count([account]) in the data section or the properties, I get the count for all the records. I need to be able to get a breakdown for each "yes" and each "no".

    Thanks,
    Leesah

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

    Re: Returning a Count in a Report (2000)

    The Count function just counts the number of records, not the number of records satisfying a condition. There are several ways to do what you want.

    1. Use the IIf function: for a count of records with "Yes", use a text box with control source =Sum(IIf([Active],1,0)) and for a count of records with "No", use a text box with control source =Sum(IIf([Active],0,1)).

    2. Use DCount: use text boxes with control source =DCount("*","[tlbDemographics]","[Active]=True") and =DCount("*","[tlbDemographics]","[Active]=False") where tblDemographics is the record source of the report.

    3. Use the fact that True = -1 and False = 0: for the count of "Yes", use a text box with control source =Abs(Sum([Active])) and for the count of "No", use a text box with control source =Abs(Sum(Not [Active])).

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning a Count in a Report (2000)

    WOW!! I'm glad I didn't spend hours this time on my own trying to get that as I NEVER stood a chance. I think I'm going to start with option 3 as the code makes sense to me. I tried using an If/than statement but couldn't get it to work for a report.

    One my quick question while I have you. I am trying need to have a msgbox pop up to aler the user when the ActivationDate is blank, but can't seem to get it work. When I use similare code to prompt the user that there is a discrepancy between dates (as in inactive date being before activation date) the message box comes up and the code is halted till the user fixes the issue. It is only the code that applies to the box being empty. This is the code I've been using:

    If Me.ActivationDate.Value = "" Then
    MsgBox "This account has not been activated"
    Exit Sub
    Else: GoTo 1

    End If

    'Closes the form

    1 DoCmd.Close

    I tried playing around with using Is Null but kept getting an error message saying an object was required, but I wasn't sure what object they were referring to.

    Thanks!
    Leesha

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

    Re: Returning a Count in a Report (2000)

    Try If IsNull(Me.ActivationDate) Then - not that I don't use .Value here, just the name of the control.

    Note: constructions like GoTo 1 are frowned upon - they can lead to hard-to-follow and hard-to-maintain unstructured code. By using If ... Then ... Else ... End If efficiently, you should be able to avoid using GoTo (except in error handling)

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning a Count in a Report (2000)

    Option 3 worked without a hitch!!!! Now I am trying to do a similar thing using AccountType. I need to get a count of the types of accounts (choices are "Individual" and "Business"). I used =DCount("*","[tlbDemographics]","[AccountType]=individual") but got a sytax error. I'm sure I'm totally off, but figured I'd give it a shot.

    Thanks,
    Leesha

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

    Re: Returning a Count in a Report (2000)

    Because "Individual" and "Business" are strings, you must put quotes around them. Since you are already using double quotes in the function, surround the value in single quotes:<pre>=DCount("*","[tlbDemographics]","[AccountType]='Individual'")</pre>


  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning a Count in a Report (2000)

    AH!!! I was trying brackets!

    BTW, the ISNull suggested worked great!! RE GOTO statements being frowned upon - thanks for the advise. Actually, I use them a lot when programming in Excel, which is truly where I spend most of my time. Honeslty, I find Excel soooooooooooooo much easier to "get" than Access. Must be how my brain thinks. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Leesha

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

    Re: Returning a Count in a Report (2000)

    It would lead too far to go into it here, but GoTo is a remnant from the earliest versions of BASIC, when the language had no provisions for structured programming. For years now, Visual Basic has had adequate alternatives for GoTo, so it shouldn't be necessary to use it (except in On Error GoTo SomeLabel); this holds for all applications that use Visual Basic (Word, Excel, PowerPoint, Access, Outlook, ...)

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning a Count in a Report (2000)

    Well, since I heed all of your input and advice, I will attempt to refrain from using it so as not to be frowned upon! Tis the price of being self taught.

    Leesha

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

    Re: Returning a Count in a Report (2000)

    >> so as not to be frowned upon <img src=/S/argue.gif border=0 alt=argue width=50 height=25>

    In itself, that is not sufficient reason for avoiding GoTo. But in the end, your code will be more robust and easier to maintain if you do.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning a Count in a Report (2000)

    Many of us, perhaps most, are largely self-taught. The reason GoTos are discouraged is because it is terribly hard to debug code using GoTos, since it is very easy for the jump to bypass tests and error checks that would otherwise make the code behave as expected. Before BASIC supported conditional branching (If-End IF, etc.), there was no option. The old Excel macro language made fairly extensive use of them but once VBA became the macro language of Office, it is unnecessary to use them in code and they make the code very hard to follow, especially when they have been used to implement the equivalent of a loop. I'm maintaining some code written like this (by someone else) from back in Access 1.1 days and it drives me crazy.
    Charlotte

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning a Count in a Report (2000)

    I tried the correction you gave me
    =DCount("*","[tlbDemographics]","[AccountType]='Individual'")
    =DCount("*","[tlbDemographics]","[AccountType]='Business'")
    There are no errors however there are no numbers returned. What did I do wrong?

    Leesha

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning a Count in a Report (2000)

    What a relief to find out most people are self taught. I am always sooooo amazed at the knowledge level of those of you who respond that I feel like a total newbie!! It gives me hope to know that most others are self taught and had to go through the same growing pains!!!

    Leesha

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning a Count in a Report (2000)

    I didn't say all of us were self-taught, but many of us go back to the earliest days of PCs when there wasn't a lot of training available. The programming that was taught for the big iron machines didn't translate well to PC languages, so we learned as we went along. I'm sure that others have also bought a ton of books and taken classes along the way, as I have. When you work with programming PCs, it is a constant learning process. You never arrive at the point where you know it all because the languages and their structures and capabilities keep changing.
    Charlotte

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Returning a Count in a Report (2000)

    Well given the fortune I've spent on books, and the few classes I've taken, it is truly the expertise of those of you on this forum that have kept me sane!!!!

    Thanks!
    Leesha

Page 1 of 2 12 LastLast

Posting Permissions

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