DSum on a report


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.
 
Ad

Advertisements

G

Guest

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.
 
K

Ken Snell [MVP]

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.
 
G

Guest

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.
 
Ad

Advertisements

G

Guest

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top