DCount criteria

**TomBP** · 10-31-2010, 01:34 PM

Hi,

When using the DCount function it counts the criteria for all the records. I want it to be limited to count for a specific value in a field.

My current build looks as followed:
AutoCountBE25: DCount("[POrg]='BE25'";"[Automation Ratio - Purchasing organisations BE25 and BE05]";"[Auto/Man]='auto'")

As you can see in the attached Datasheet View it still counts "auto" for all the records instead of those for BE25 in the POrg field. AutoCountBE25 and ManCountBE25 should total BE25Count.

**MarvinP** · 10-31-2010, 02:14 PM

Hi TomBP,

Why not use a =CountIf instead?

**alansidman** · 10-31-2010, 08:46 PM

Originally Posted by MarvinP

Hi TomBP,

Why not use a =CountIf instead?

This is a question regarding Access. There is no countif function in Access; only in Excel.

Now to the question of the solution. Change your DCount to read
AutoCountBE25: DCount("[POrg]";"[Automation Ratio - Purchasing organisations BE25 and BE05]";"[Auto/Man]='auto'")

Now in the QBE, in the criteria for the field POrg, put =BE25. It will bring back only the records which have BE25 in the field POrg.

Alan

**MarvinP** · 10-31-2010, 10:13 PM

Hey Alan,
Sorry about my stupid reply. My mindset is always Excel based on how many questions we get on Access. I was wondering why he needed DCount and Advanced Formula type formatting. Now that you point out the question is about Access it makes a lot more sense. I'll read harder before making silly suggestions.

**TomBP** · 11-01-2010, 07:19 AM

Originally Posted by alansidman

Now to the question of the solution. Change your DCount to read
AutoCountBE25: DCount("[POrg]";"[Automation Ratio - Purchasing organisations BE25 and BE05]";"[Auto/Man]='auto'")

Now in the QBE, in the criteria for the field POrg, put =BE25. It will bring back only the records which have BE25 in the field POrg.

Alan

Alan,

I've tried your solution (Sample Database - Solution Alan) and it works. I'm however trying to combine more counts into one query (Datasheet View 2.jpg). This to reduce the number of query's.

Is this even possible in Access?

P.S. the Datasheet View 2 is a result of Query1 in the Sample Database.

**alansidman** · 11-01-2010, 10:56 AM

Tom;
Is this what you are looking for? Look at Query 4 in the db Attached. I created two separate queries, 2 and 3 that gave me the counts for each BE05 created by each and BE25 created for each. I then renamed the fields that contained the BE05 and BE25 information so that I could then create a union query based upon both Query 2 and Query 3. Note that Union Queries cannot be built in the QBE but only using SQL.

Alan

**TomBP** · 11-11-2010, 07:09 AM

Alan,

That's not what I'm looking for. Even if the numbers come out right, the quantity/difficulty of query's is too much hassle.

Instead I'd rather make two seperate query's. One for BE25 and one for BE05.

P.S. Sorry for the late response. Was out of the country.