DSum on a report

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

 

[Ken Snell [MVP]]

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

 

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

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.

 

[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>

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.

 

[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

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>

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.

 

[Guest]

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

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

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>

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.