Countif

happyhungarian

Active Member
Joined
Jul 19, 2011
Messages
252
Office Version
  1. 365
Platform
  1. Windows
Hi, I found the following solution to do a quasi countif in Access =count(Iif([fieldname]="A",1)). However, instead of defining "A" as what to count I would like it to use the value that's in that record's fieldname. So in Excel parlance "=countif(A:A,A1)". Is this possible? Is it also possible to have multiple criteria for this?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Thank you. I tried giving this a shot but all records come back with "0". Expr1: DCount("*","Current","UniqueID"=[UniqueID]). For each record I'm trying to count how many times the UniqueID is present in Table "Current"
 
Upvote 0
I assuming I have an incorrect syntax on the "UniqueID" portion... assuming this is making the query think that I'm specifically wanting to search for "UniqueID" rather than it using the UniqueID from that particular record to search the other UniqueIDs
 
Upvote 0
Yeah, I think it is syntax.
Is Unique ID a string/text field, or numeric field?

If string/text, try something like:
VBA Code:
DCount("*","Current","UniqueID='" & [UniqueID] & "'")
 
Upvote 0
It's numeric. It's asking me to enter a parameter for UniqueID

1713389813412.png


Thanks for the help. I'm an extreme Access novice.
 
Upvote 0
The op doesn't make sense to me. A field name does not contain a value. Anyway, the criteria looks funny. Isn't UniqueID=UniqueID like 6=6 (different UniqueID value in every record, but that seems to me to be what you're doing there)?
I'm trying to count how many times the UniqueID is present in Table "Current"
That suggests UniqueID is what you're using to describe a particular value. In that case it would be like "UniqueID = 6"
Or if you're counting records where it has a value, then maybe "Field2 Is Not Null" or maybe "IsNull(Field2)" . Fairly certain you cannot count nulls any other way with DCount. By that I mean you can't use the same field by name (apparently that's OK if you use the wildcard *).
Another approach would be to use a recordset.
Maybe you should show some data and the expected output.
 
Upvote 0
I tried to give the example of Excel to illustrate my point. Again, Excel is =countif(a:a,a1) which will count the number of times that value in a1 appears in column a. Again, being a novice in Access I thought maybe "UniqueID"=[UniqueID] was like establishing a range and a reference so, in Excel parlance, it's almost like =countif([UniqueID],a1)
 
Upvote 0
In Access, UniqueID would be a field (think column, but not really) and UniqueID would be the name of that field (think column label, like "A"). Based on what you're saying, you need to count how many times a value is in UniqueID field. If that value is constant (e.g. Apple) you can search the field for "Apple" and do a count. However, you want to use the same field for counting values as the field to get the value to count. Access has no such thing as a row number so there is no such thing as the "1" in any field, as Excel (as in A1). You will need a way to locate that value to count.

Easiest way is to use DCount without trying to look up the value: DCount("UniqueID,"Current","UniqueID='Apple'"). Note the nested single quotes for string criteria only.

You might be able to look up the value to count by nesting a DLookup function inside your DCount. However, you will need a field with Unique Values. Obviously that cannot be the field you're trying to count on. If your table is properly designed, you have a primary key - usually an autonumber. Then you probably can refer to the particular row using the PK value that contains your value to count. Say the PK field is named myPK and the value to lookup is in record with pk value of 1. Maybe the DLookup would be like DLookup("UniqueID","Current","myPK = 1"). In English that means "return the value from the UniqueID field in Current table where the PK field value is 1". So the combination might be
DCount("UniqueID","Current",DLookup("UniqueID","Current","myPK = 1))

I have no idea if that will work and I still recommend moving the seed value out of the field being counted instead of convoluted approaches. BTW, it may be good to know where you are intending to use this because using DCount in queries can be a bad idea. If there are a lot of records to count things can get slow. Besides it's considered bad form and that the proper approach is to use sub queries because a DCount function basically does the same thing as a query, but not as efficiently.
 
Upvote 0
I assume that UniqueID is on a form record?
I always use Me. to indicate form control, so concatenate Me.UniqueID in your Dcount().
That will then replace it with the value of that control.
 
Upvote 0

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top