DCOUNT??

  • Thread starter Thread starter GD
  • Start date Start date
G

GD

I'm having a MAJOR brain cramp, and I can't seem to be able to walk it off!!

I'm trying to get a count of how many times a specific PO# appears in the
PONo field of my CurrentStatement table, where the ARCode field equals 4.
All are formatted as text.

That's a DCount expression, right? But how does the syntax go??

Thanks for the help!!!
 
From Help:
DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

DCount("[PONo]", "CurrentStatement", "[ARCode] = '4'")
 
For some reason, it's bring back no results, when there should be 784 records
returned (i.e. all ARCode 4s). What could be causing that?

--
GD


M Skabialka said:
From Help:
DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

DCount("[PONo]", "CurrentStatement", "[ARCode] = '4'")


GD said:
I'm having a MAJOR brain cramp, and I can't seem to be able to walk it
off!!

I'm trying to get a count of how many times a specific PO# appears in the
PONo field of my CurrentStatement table, where the ARCode field equals 4.
All are formatted as text.

That's a DCount expression, right? But how does the syntax go??

Thanks for the help!!!
Click to expand...
Click to expand...
 
For some reason, it's bring back no results, when there should be 784 records
returned (i.e. all ARCode 4s). What could be causing that?
Click to expand...

Please post the datatypes (NOT the Format, which is irrelevant) of the
relevant fields and the actual Dcount expression that you are using.
 
GD said:
For some reason, it's bring back no results, when there should be 784
records
returned (i.e. all ARCode 4s). What could be causing that?
Click to expand...

If ARCode is a numeric field, don't use the apostrophes

DCount("[PONo]", "CurrentStatement", "[ARCode] = 4")

Tom Lake
 
PONo: Text
ARCode: Text
Table is CurrentStatement

DCount("[PONo]","CurrentStatement","[ARCode]='4'")

I'm trying to get a count of each unique PONo, where the ARCode field equals
4.
 
Even if it was returning values,
DCount("[PONo]","CurrentStatement","[ARCode]='4'") will NOT give you a
"count of each unique PONo". It will give you a count of how many rows in
CurrentStatement have a value of '4' for ARCode and a non-null value for
PONo. In other words, if you've got 423 rows in the table with ARCode equal
to '4' and all 423 of those rows have a non-null value for PONo, DCount
should return 423. If 418 of those rows have non-null values, and 5 have
Null for for the field, DCount should return 418.

To count Distinct values, you'll need to create a query that returns unique
combinations of PONo and ARCode and run DCount on that:

SELECT DISTINCT PONo, ARCode FROM CurrentStatement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


GD said:
PONo: Text
ARCode: Text
Table is CurrentStatement

DCount("[PONo]","CurrentStatement","[ARCode]='4'")

I'm trying to get a count of each unique PONo, where the ARCode field
equals
4.

--
GD


John W. Vinson said:
Please post the datatypes (NOT the Format, which is irrelevant) of the
relevant fields and the actual Dcount expression that you are using.
Click to expand...
Click to expand...
 
Just realized my previous note may be confusing.

I gave your SQL for a query that will return all unique combinations of PONo
and ARCode. You need to create a query that uses that SQL and save it: you
cannot use SQL with DCount. Let's assume you saved the query as
qryUniqueCombinationsInCurrentStatement. You'd then change your DCount
statement to

DCount("*","qryUniqueCombinationsInCurrentStatement","[ARCode]='4'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas J. Steele said:
Even if it was returning values,
DCount("[PONo]","CurrentStatement","[ARCode]='4'") will NOT give you a
"count of each unique PONo". It will give you a count of how many rows in
CurrentStatement have a value of '4' for ARCode and a non-null value for
PONo. In other words, if you've got 423 rows in the table with ARCode
equal to '4' and all 423 of those rows have a non-null value for PONo,
DCount should return 423. If 418 of those rows have non-null values, and 5
have Null for for the field, DCount should return 418.

To count Distinct values, you'll need to create a query that returns
unique combinations of PONo and ARCode and run DCount on that:

SELECT DISTINCT PONo, ARCode FROM CurrentStatement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


GD said:
PONo: Text
ARCode: Text
Table is CurrentStatement

DCount("[PONo]","CurrentStatement","[ARCode]='4'")

I'm trying to get a count of each unique PONo, where the ARCode field
equals
4.

--
GD


John W. Vinson said:
For some reason, it's bring back no results, when there should be 784
records
returned (i.e. all ARCode 4s). What could be causing that?

Please post the datatypes (NOT the Format, which is irrelevant) of the
relevant fields and the actual Dcount expression that you are using.
Click to expand...
Click to expand...
Click to expand...
 
Allen Browne has an article:
ECount() - an extended DCount()
at:
http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but has an extra flag you can
set to retrieve the distinct count.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top