Returning a Count in a Report (2000)

Topic

New Reply

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

Reply | Quote

Replies

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])).

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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:

=DCount("*","[tlbDemographics]","[AccountType]='Individual'")

Reply | Quote

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.

Leesha

Reply | Quote

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, …)

Reply | Quote

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

Reply | Quote

>> so as not to be frowned upon

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Check all names in the control source carefully, even the smallest typo will result in either an error or in a return value of 0. For instance, I introduced an error a few replies ago – I wrote tlbDemographics instead of tblDemographics .

Reply | Quote

Hi Hans!

That was it. I had copied your code and didn’t catch the tlb vs tbl. I tried to “think like Hans” and did actually check to be sure I hadn’t messed up the spelling but I missed that part. Eye strain! Been at this too long!

Thanks!
Leesha

Reply | Quote