Oh yeah! that will work, thanks a lot Duane.
OK... last question then, how do I get at the value that is in
GroupHeader0
and pass it in to DCount?
I tried the Sum(abs()) again, but get this error:
"Expressiong is invalid. Aggregate functions are only allowed on
output
fields of the Record Source."
It is in the Group header/footer, and other parameters below are met.
Thanks.
_Greg
:
I am not even going to help send you down the code path when it is not
necessary.
You should be able to use either of the other two methods. If you want
to
limit your DCount() you might be able to use something like:
=DCount("*","qryPSM_YTD","Cycle='Cancelled' And [SalesManager]=""" &
[SalesManager] & """")
You haven't provided enough information that I am totally confident
with
the
above expression.
Also the
=Sum(Abs(Cycle="Cancelled"))
should work as long as:
- you don't use it in a Page header or footer
- your text box name is not the name of a field
- you have a text field in your report record source named Cycle
--
Duane Hookom
MS Access MVP
--
Thanks a lot, Duane. Great Reply. Yes, getting aggregates that
maintain
the
report grouping integrity in this was the ultimate goal/obstacle.
I started out trying dcount, but since it wasn't maintaining the
group
the
way other aggregate functions do (because of the full table/qry as
parameter)
it just shows the same value in each grouping. In my case, setting
the
control of a text box in my report to
=DCount("*","qryPSM_YTD","Cycle='Cancelled'") just returns 97 in
each
grouping of Sales Managers. (Of which, I am one, so forgive the
lack
of
experience - just bucking for a raise here). Dcount definitely
works
if
there is another way to crack the grouping issue.
The underlying query for the report is the same, so I tried what you
suggested setting the control source to
Sum(Abs(Cycle="Cancelled")) --
this just returned an error for me.
So went back to the code route -- I assume this will have the same
grouping
issue as dcount, but was hoping I could suck in a group parameter
from
the
report, and add it to the where clause. (I think I need to do the
same
thing
to maintain a graph in this same grouping - seperate question
perhaps)
The snippet below didn't work for me as DAO - so spent some time
digging
and
rewrote this in ADO. Its not working for me currently at runtime,
tossing
an
error at or near the "Select" statement. But seems close:
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As
Integer)
Dim rs As ADODB.Recordset
Dim strSQL As String
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT COUNT(*) AS Cancels FROM qryPSM_YTD WHERE (Cycle
=
'Cancelled')"
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
'If Not rs.EOF Then
Me.Text83 = rs!Cancels
'End If
rs.Close
End Sub
--------------------------------------------------------------------
:
That's not the method that I would use, however, you would use the
On
Format
event of the report section containing the text box.
Dim db as DAO.Database
Dim rs As DAO.Recordset
SET db =CurrentDb
Set rs = db.OpenRecordSet("SELECT COUNT(*) as Cancels " & _
"FROM qryPSM_YTD WHERE Cycle = 'Cancelled'")
If not rs.EOF Then
Me.txtCancelled = rs!Cancels
End If
rs.Close
Set rs nothing
Set db = Nothing
The entire above code could be replaced with a control source in
the
text
box of:
=DCount("*","qryPSM_YTD","Cycle='Cancelled'")
If your qryPSM_YTD is the same as the Record Source of your report,
then
you
might get away with the much more efficient solution of setting the
Control
Source to:
=Sum(Abs(Cycle="Cancelled"))
I expect there is some grouping value that needs to included but I
don't
see
anything in your replies that provides any information about this.
--
Duane Hookom
MS Access MVP
Thanks for the response
- lets say I take the code route,
since
it
would
be potentially the most flexible. Will the steps be:
1) I set a textcontrol named 'txtCancelled' in my report.
2) I need a code snippet to now write to that variable that:
a) maintains the group integrity in the report
b) runs the subquery against the main report query
c) needs some sort of trigger event like report print
Could you point me to any samples of something like this?
-----------------
:
I'm sticking by my first reply.
You can use subqueries in queries but not as control sources.
--
Duane Hookom
MS Access MVP
--
Thanks - I got the impression that it could because when I put
the
query
into
the control source, I got an error message saying I needed to
include
parenthesis around the subquery. And the full report doesn't
error
out
when
I run it.
The database is in SQL, using Access as the report writer /
inpot
forms
if
that makes any difference.
Thanks
-Greg
:
You can't use SQL statements as control sources. You might be
able
to
use
standard Sum() or Count() or domain aggregate functions or
code
or
subreports but not SQL in control sources.
--
Duane Hookom
MS Access MVP
I have a sales report based on a query. It is grouped by
sales
manager
at
the top level. I am running a bunch of calculations on the
data
for
each
salesperson.
Two of the items I am trying to calculate over the
recordset
are:
1) Count of cancelled projects by salesperson
2) Count of unique clients
I have used the std aggregate functions for a lot of the
report
so
far,
and
can't get DCOUNT to work, since it requires the main Query
as
a
parameter -
so same result for each grouping.
have tried to write SQL subquery statements. I built my
statements
in
the
query builder and they return the expected results.
However
when
I
put
them
into the control source of the text box on the report they
return
#NAME?.
Will putting subqueries into a text control even work, or
am I
barking
up
the wrong tree? Will it maintain the grouping integrity?
My subqueries look like this in the control source:
=(SELECT COUNT(*) as 'Cancels' FROM qryPSM_YTD WHERE (Cycle
=
'Cancelled'))
=(SELECT DiSTINCT COUNT(ClientID) as 'uniqueclients' FROM
qryPSM_YTD)
~~~from memory, not 100% this one is right....
Getting this to work. opens up a world of other options for
my
report -
so
I'd like to figure it out using subqueries on the main
recordset.
Thanks
-Greg
