Dcount?

  • Thread starter Thread starter Damien McBain
  • Start date Start date
D

Damien McBain

I'm trying to use the Dcount function in the Control Source of a text box on
a report.
The database has 4 tables which are all linked:
Customers
ProductHierarchy
Sales
Territories

Like this:

Territories Customers Sales ProductHierarchy
----------- --------- ----- ----------------
AccNo 1--> AccNo
CustName PH2 <----1 PH2
Class ActTonnes Description
Terr 1-----> Terr ActSale
Description Status etc etc
SOff

In the Customers table the "Status" is either "Not Started", "In Progress"
or "Complete".

The record source for the report is a query called "ReportQuery" (original
hey?). It contains all of the fields in the 4 tables.

The report has 2 grouping levels, Territory and PH2 (it's a summary report).
I'm trying to create a text box in the Terr group header that will return
the number of Customers assigned to the "current" Territory (that is, the
Territory which is displayed in the Terr group header) have the value
"Complete" in the Status field.

I'm stumped though. I'm trying:
=DCount("[Status]","Customers",[Terr]=[Reports]![Terr-Hier]!Terr And
[Status]="Complete")

Sorry about the verbose explanation. Any help very much appreciated, I don't
know what I'm doing on this dcount thing.

Damien
 
Concatenate the values into the string:

=DCount("[Status]", "Customers",
"([Terr] = " & [Terr] & ") And ([Status] ='Complete')")

If "Terr" is a Text type field, you need extra quotes:


=DCount("[Status]", "Customers", "([Terr] = """ & [Terr] & """) And
([Status] ='Complete')")
 
Thanks Allen.

That gives me an error dialog which says "You may have entered a comma
without a preceeding value or ifentifier"

I'm using it in the property sheet, not VBA, does that make a difference?

Allen said:
Concatenate the values into the string:

=DCount("[Status]", "Customers",
"([Terr] = " & [Terr] & ") And ([Status] ='Complete')")

If "Terr" is a Text type field, you need extra quotes:


=DCount("[Status]", "Customers", "([Terr] = """ & [Terr] & """) And
([Status] ='Complete')")


Damien McBain said:
I'm trying to use the Dcount function in the Control Source of a
text box on a report.
The database has 4 tables which are all linked:
Customers
ProductHierarchy
Sales
Territories

Like this:

Territories Customers Sales ProductHierarchy
----------- --------- ----- ----------------
AccNo 1--> AccNo
CustName PH2 <----1 PH2
Class ActTonnes Description
Terr 1-----> Terr ActSale
Description Status etc etc
SOff

In the Customers table the "Status" is either "Not Started", "In
Progress" or "Complete".

The record source for the report is a query called "ReportQuery"
(original hey?). It contains all of the fields in the 4 tables.

The report has 2 grouping levels, Territory and PH2 (it's a summary
report). I'm trying to create a text box in the Terr group header
that will return the number of Customers assigned to the "current"
Territory (that is, the Territory which is displayed in the Terr
group header) have the value "Complete" in the Status field.

I'm stumped though. I'm trying:
=DCount("[Status]","Customers",[Terr]=[Reports]![Terr-Hier]!Terr And
[Status]="Complete")

Sorry about the verbose explanation. Any help very much appreciated,
I don't know what I'm doing on this dcount thing.

Damien
Click to expand...
Click to expand...
 
Should work in the Control Source property of the text box.

Are you sure you have the quote marks right (i.e. Access does not think the
commas are inside the quotes)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Damien McBain said:
Thanks Allen.

That gives me an error dialog which says "You may have entered a comma
without a preceeding value or ifentifier"

I'm using it in the property sheet, not VBA, does that make a difference?

Allen said:
Concatenate the values into the string:

=DCount("[Status]", "Customers",
"([Terr] = " & [Terr] & ") And ([Status] ='Complete')")

If "Terr" is a Text type field, you need extra quotes:


=DCount("[Status]", "Customers", "([Terr] = """ & [Terr] & """) And
([Status] ='Complete')")


Damien McBain said:
I'm trying to use the Dcount function in the Control Source of a
text box on a report.
The database has 4 tables which are all linked:
Customers
ProductHierarchy
Sales
Territories

Like this:

Territories Customers Sales ProductHierarchy
----------- --------- ----- ----------------
AccNo 1--> AccNo
CustName PH2 <----1 PH2
Class ActTonnes Description
Terr 1-----> Terr ActSale
Description Status etc etc
SOff

In the Customers table the "Status" is either "Not Started", "In
Progress" or "Complete".

The record source for the report is a query called "ReportQuery"
(original hey?). It contains all of the fields in the 4 tables.

The report has 2 grouping levels, Territory and PH2 (it's a summary
report). I'm trying to create a text box in the Terr group header
that will return the number of Customers assigned to the "current"
Territory (that is, the Territory which is displayed in the Terr
group header) have the value "Complete" in the Status field.

I'm stumped though. I'm trying:
=DCount("[Status]","Customers",[Terr]=[Reports]![Terr-Hier]!Terr And
[Status]="Complete")

Sorry about the verbose explanation. Any help very much appreciated,
I don't know what I'm doing on this dcount thing.

Damien
Click to expand...
Click to expand...
Click to expand...
 
I got it:

=DCount("[AccNo]","Customers","[Status]='Complete' And [Terr]=" & [Terr])

AccNo could have been any field in the customers table where there's a value
in every record.

cheers for the help Allen, you put me on the right tram.

Damien

Damien said:
Thanks Allen.

That gives me an error dialog which says "You may have entered a comma
without a preceeding value or ifentifier"

I'm using it in the property sheet, not VBA, does that make a
difference?

Allen said:
Concatenate the values into the string:

=DCount("[Status]", "Customers",
"([Terr] = " & [Terr] & ") And ([Status] ='Complete')")

If "Terr" is a Text type field, you need extra quotes:


=DCount("[Status]", "Customers", "([Terr] = """ & [Terr] & """) And
([Status] ='Complete')")


Damien McBain said:
I'm trying to use the Dcount function in the Control Source of a
text box on a report.
The database has 4 tables which are all linked:
Customers
ProductHierarchy
Sales
Territories

Like this:

Territories Customers Sales ProductHierarchy
----------- --------- ----- ----------------
AccNo 1--> AccNo
CustName PH2 <----1 PH2
Class ActTonnes Description
Terr 1-----> Terr ActSale
Description Status etc etc
SOff

In the Customers table the "Status" is either "Not Started", "In
Progress" or "Complete".

The record source for the report is a query called "ReportQuery"
(original hey?). It contains all of the fields in the 4 tables.

The report has 2 grouping levels, Territory and PH2 (it's a summary
report). I'm trying to create a text box in the Terr group header
that will return the number of Customers assigned to the "current"
Territory (that is, the Territory which is displayed in the Terr
group header) have the value "Complete" in the Status field.

I'm stumped though. I'm trying:
=DCount("[Status]","Customers",[Terr]=[Reports]![Terr-Hier]!Terr And
[Status]="Complete")

Sorry about the verbose explanation. Any help very much appreciated,
I don't know what I'm doing on this dcount thing.

Damien
Click to expand...
Click to expand...
Click to expand...
 
* is even safer ..
=DCount(*,"Customers","[Status]='Complete' And [Terr]=" & [Terr])
code is even faster ..
ie
sub Groupheader_Format(....)
Dim Rs As DAO.Recordset
If FormatCount>1 Then exit sub
Set Rs = Access.CurrentDb.OpenRecordset("SELECT COUNT(*) FROM CUSTOMERS
WHERE TERR='" & Me!Terr & "' AND Status='Complete'",DbOpenSnapshot)
Me!CountComplete = Rs.Fields(0).Value
Rs.Close : Set Rs = Nothing
End If
end sub

Pieter

Damien McBain said:
I got it:

=DCount("[AccNo]","Customers","[Status]='Complete' And [Terr]=" & [Terr])

AccNo could have been any field in the customers table where there's a value
in every record.

cheers for the help Allen, you put me on the right tram.

Damien

Damien said:
Thanks Allen.

That gives me an error dialog which says "You may have entered a comma
without a preceeding value or ifentifier"

I'm using it in the property sheet, not VBA, does that make a
difference?

Allen said:
Concatenate the values into the string:

=DCount("[Status]", "Customers",
"([Terr] = " & [Terr] & ") And ([Status] ='Complete')")

If "Terr" is a Text type field, you need extra quotes:


=DCount("[Status]", "Customers", "([Terr] = """ & [Terr] & """) And
([Status] ='Complete')")


I'm trying to use the Dcount function in the Control Source of a
text box on a report.
The database has 4 tables which are all linked:
Customers
ProductHierarchy
Sales
Territories

Like this:

Territories Customers Sales ProductHierarchy
----------- --------- ----- ----------------
AccNo 1--> AccNo
CustName PH2 <----1 PH2
Class ActTonnes Description
Terr 1-----> Terr ActSale
Description Status etc etc
SOff

In the Customers table the "Status" is either "Not Started", "In
Progress" or "Complete".

The record source for the report is a query called "ReportQuery"
(original hey?). It contains all of the fields in the 4 tables.

The report has 2 grouping levels, Territory and PH2 (it's a summary
report). I'm trying to create a text box in the Terr group header
that will return the number of Customers assigned to the "current"
Territory (that is, the Territory which is displayed in the Terr
group header) have the value "Complete" in the Status field.

I'm stumped though. I'm trying:
=DCount("[Status]","Customers",[Terr]=[Reports]![Terr-Hier]!Terr And
[Status]="Complete")

Sorry about the verbose explanation. Any help very much appreciated,
I don't know what I'm doing on this dcount thing.

Damien
Click to expand...
Click to expand...
Click to expand...
 
Back
Top