Countif

[happyhungarian]

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?

 

[Joe4]

In Access, the equivalent function is DCOUNT.
See: DCount Function - Microsoft Support.

 

[happyhungarian]

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"

 

[happyhungarian]

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

 

[Joe4]

Yeah, I think it is syntax.
Is Unique ID a string/text field, or numeric field?

If string/text, try something like:

DCount("*","Current","UniqueID='" & [UniqueID] & "'")

 

[happyhungarian]

It's numeric. It's asking me to enter a parameter for UniqueID

Thanks for the help. I'm an extreme Access novice.

 

[Micron]

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.

 

[happyhungarian]

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)

 

[Micron]

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.

 

[welshgasman]

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.