DSum on a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that lists dues invoices and payments based on a query that
I'm trying to add a DSum function to at the end of the report.

The report has the following filter on it:
([Total_Amount_Paid] > 0 AND [Date_Paid] Between [Enter starting date:] AND
[Enter ending date:])

When the user opens the report, they're prompted to enter the starting and
ending date they want to view.

The DSum field in question is found in the Report Footer. The field formula
is:
=DSum("[Total_Amount_Paid]","[qryDuesInvoiceInd]","[Payment_Method] = 'Ck'")

Basically it is supposed to total the [Total_Amount_Paid] field for every
record with "Ck" in the [Payment_Method] field. The problem is that it gives
me the complete total from [qryDuesInvoiceInd] -- I want it to give me the
total with the filter applied. Because the filter requires a user input
value, I can't figure out how to pass that info. along to the DSum function,
or make the DSum function only look at my report data (instead of the full
query data).

Thanks.
 
But I need to filter it based on the Payment_Method.
I'm looking for a subset of Sum([Total_Amount_Paid]), namely the sum where
[Payment_Method] = 'Ck'
That's why I used DSum.



Ken Snell said:
Change the Control Source expression to this:
=Sum([Total_Amount_Paid])

--

Ken Snell
<MS ACCESS MVP>


BrianS said:
I have a report that lists dues invoices and payments based on a query that
I'm trying to add a DSum function to at the end of the report.

The report has the following filter on it:
([Total_Amount_Paid] > 0 AND [Date_Paid] Between [Enter starting date:]
AND
[Enter ending date:])

When the user opens the report, they're prompted to enter the starting and
ending date they want to view.

The DSum field in question is found in the Report Footer. The field
formula
is:
=DSum("[Total_Amount_Paid]","[qryDuesInvoiceInd]","[Payment_Method] =
'Ck'")

Basically it is supposed to total the [Total_Amount_Paid] field for every
record with "Ck" in the [Payment_Method] field. The problem is that it
gives
me the complete total from [qryDuesInvoiceInd] -- I want it to give me the
total with the filter applied. Because the filter requires a user input
value, I can't figure out how to pass that info. along to the DSum
function,
or make the DSum function only look at my report data (instead of the full
query data).

Thanks.
Click to expand...
Click to expand...
 
I was understanding that the report's query was doing that filtration for
you. Sorry.
=Sum(IIf([Payment_Method]="Ck",[Total_Amount_Paid],0)

--

Ken Snell
<MS ACCESS MVP>

BrianS said:
But I need to filter it based on the Payment_Method.
I'm looking for a subset of Sum([Total_Amount_Paid]), namely the sum where
[Payment_Method] = 'Ck'
That's why I used DSum.



Ken Snell said:
Change the Control Source expression to this:
=Sum([Total_Amount_Paid])

--

Ken Snell
<MS ACCESS MVP>


BrianS said:
I have a report that lists dues invoices and payments based on a query
that
I'm trying to add a DSum function to at the end of the report.

The report has the following filter on it:
([Total_Amount_Paid] > 0 AND [Date_Paid] Between [Enter starting date:]
AND
[Enter ending date:])

When the user opens the report, they're prompted to enter the starting
and
ending date they want to view.

The DSum field in question is found in the Report Footer. The field
formula
is:
=DSum("[Total_Amount_Paid]","[qryDuesInvoiceInd]","[Payment_Method] =
'Ck'")

Basically it is supposed to total the [Total_Amount_Paid] field for
every
record with "Ck" in the [Payment_Method] field. The problem is that it
gives
me the complete total from [qryDuesInvoiceInd] -- I want it to give me
the
total with the filter applied. Because the filter requires a user input
value, I can't figure out how to pass that info. along to the DSum
function,
or make the DSum function only look at my report data (instead of the
full
query data).

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the help.
One thing --
The [Payment_Method] field contains the text "Ck" plus the check number. So
what I really need is to add [Total_Amount_Paid] where [Payment_Method] =
"Ck*". But that wild card syntax doesn't work. What do I need to make it fly?
-Brian


Ken Snell said:
I was understanding that the report's query was doing that filtration for
you. Sorry.
=Sum(IIf([Payment_Method]="Ck",[Total_Amount_Paid],0)

--

Ken Snell
<MS ACCESS MVP>

BrianS said:
But I need to filter it based on the Payment_Method.
I'm looking for a subset of Sum([Total_Amount_Paid]), namely the sum where
[Payment_Method] = 'Ck'
That's why I used DSum.



Ken Snell said:
Change the Control Source expression to this:
=Sum([Total_Amount_Paid])

--

Ken Snell
<MS ACCESS MVP>


I have a report that lists dues invoices and payments based on a query
that
I'm trying to add a DSum function to at the end of the report.

The report has the following filter on it:
([Total_Amount_Paid] > 0 AND [Date_Paid] Between [Enter starting date:]
AND
[Enter ending date:])

When the user opens the report, they're prompted to enter the starting
and
ending date they want to view.

The DSum field in question is found in the Report Footer. The field
formula
is:
=DSum("[Total_Amount_Paid]","[qryDuesInvoiceInd]","[Payment_Method] =
'Ck'")

Basically it is supposed to total the [Total_Amount_Paid] field for
every
record with "Ck" in the [Payment_Method] field. The problem is that it
gives
me the complete total from [qryDuesInvoiceInd] -- I want it to give me
the
total with the filter applied. Because the filter requires a user input
value, I can't figure out how to pass that info. along to the DSum
function,
or make the DSum function only look at my report data (instead of the
full
query data).

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Nevermind on that last post. I got it. I'm a little slow this morning.


BrianS said:
Thanks for the help.
One thing --
The [Payment_Method] field contains the text "Ck" plus the check number. So
what I really need is to add [Total_Amount_Paid] where [Payment_Method] =
"Ck*". But that wild card syntax doesn't work. What do I need to make it fly?
-Brian


Ken Snell said:
I was understanding that the report's query was doing that filtration for
you. Sorry.
=Sum(IIf([Payment_Method]="Ck",[Total_Amount_Paid],0)

--

Ken Snell
<MS ACCESS MVP>

BrianS said:
But I need to filter it based on the Payment_Method.
I'm looking for a subset of Sum([Total_Amount_Paid]), namely the sum where
[Payment_Method] = 'Ck'
That's why I used DSum.



:

Change the Control Source expression to this:
=Sum([Total_Amount_Paid])

--

Ken Snell
<MS ACCESS MVP>


I have a report that lists dues invoices and payments based on a query
that
I'm trying to add a DSum function to at the end of the report.

The report has the following filter on it:
([Total_Amount_Paid] > 0 AND [Date_Paid] Between [Enter starting date:]
AND
[Enter ending date:])

When the user opens the report, they're prompted to enter the starting
and
ending date they want to view.

The DSum field in question is found in the Report Footer. The field
formula
is:
=DSum("[Total_Amount_Paid]","[qryDuesInvoiceInd]","[Payment_Method] =
'Ck'")

Basically it is supposed to total the [Total_Amount_Paid] field for
every
record with "Ck" in the [Payment_Method] field. The problem is that it
gives
me the complete total from [qryDuesInvoiceInd] -- I want it to give me
the
total with the filter applied. Because the filter requires a user input
value, I can't figure out how to pass that info. along to the DSum
function,
or make the DSum function only look at my report data (instead of the
full
query data).

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top