Running Total Text Box!

[Bob]

I am trying to have a running total text box on my report, but the credits
are just adding up the total and not crediting it.
What should I have in the Source Control of my [tbRunningInvoice] I have got
it set at (Running Sum: Over Group)
This is the Source Control in my [tbInvoiceAmount]
=IIf([Flag]=0,[AmountSummary],"<" &
Format([AmountSummary],"$#,##0.00;($#,##0.00)") & ">")
Thanks for any help

 

[John Vinson]

I am trying to have a running total text box on my report, but the credits
are just adding up the total and not crediting it.
What should I have in the Source Control of my [tbRunningInvoice] I have got
it set at (Running Sum: Over Group)
This is the Source Control in my [tbInvoiceAmount]
=IIf([Flag]=0,[AmountSummary],"<" &
Format([AmountSummary],"$#,##0.00;($#,##0.00)") & ">")
Thanks for any help

Your Format expression isn't returning a number - it's returning a
text xtring (which you can't sum).

Set the Format Property of the textbox on your report, and set its
Control Source to the field that you want to sum. I have NO idea what
you're trying to accomplish with your IIF statement.

John W. Vinson[MVP]

 

[Bob]

I am trying to have a running total text box on my report, but the credits
are just adding up the total and not crediting it.
What should I have in the Source Control of my [tbRunningInvoice] I have
got
it set at (Running Sum: Over Group)
This is the Source Control in my [tbInvoiceAmount]
=IIf([Flag]=0,[AmountSummary],"<" &
Format([AmountSummary],"$#,##0.00;($#,##0.00)") & ">")
Thanks for any help

Your Format expression isn't returning a number - it's returning a
text xtring (which you can't sum).

Set the Format Property of the textbox on your report, and set its
Control Source to the field that you want to sum. I have NO idea what
you're trying to accomplish with your IIF statement.

John W. Vinson[MVP]

Thanks for the help John, I did that but it just increases the total even
when there is credits showing in totals, it is based on this query:
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo);

 

[Bob]

I am trying to have a running total text box on my report, but the
credits
are just adding up the total and not crediting it.
What should I have in the Source Control of my [tbRunningInvoice] I have
got
it set at (Running Sum: Over Group)
This is the Source Control in my [tbInvoiceAmount]
=IIf([Flag]=0,[AmountSummary],"<" &
Format([AmountSummary],"$#,##0.00;($#,##0.00)") & ">")
Thanks for any help

Your Format expression isn't returning a number - it's returning a
text xtring (which you can't sum).

Set the Format Property of the textbox on your report, and set its
Control Source to the field that you want to sum. I have NO idea what
you're trying to accomplish with your IIF statement.

John W. Vinson[MVP]

Thanks for the help John, I did that but it just increases the total even
when there is credits showing in totals, it is based on this query:
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo);

Oops this is a subReport does that make a difference.............Thanx Bob

 

[John Vinson]

I am trying to have a running total text box on my report, but the credits
are just adding up the total and not crediting it.
What should I have in the Source Control of my [tbRunningInvoice] I have got
it set at (Running Sum: Over Group)
This is the Source Control in my [tbInvoiceAmount]
=IIf([Flag]=0,[AmountSummary],"<" &
Format([AmountSummary],"$#,##0.00;($#,##0.00)") & ">")
Thanks for any help

Ok, you'll have to explain your meaning here. What do you mean by "the
credits are just adding up the total and not crediting it"?

What data do you have?

What result do you want?

John W. Vinson[MVP]

 

[Bob]

Thanks John, Its a Statement that lists a Clients Totals Owing for each
Invoice and Payments made by that Client
I am trying to put a running balance next to these totals , I do have a
final balance at the bottom of the report
Yes the payment Credits are adding up the running balance when they should
be deducting
Thanks for your Help..............Bob

I am trying to have a running total text box on my report, but the credits
are just adding up the total and not crediting it.
What should I have in the Source Control of my [tbRunningInvoice] I have
got
it set at (Running Sum: Over Group)
This is the Source Control in my [tbInvoiceAmount]
=IIf([Flag]=0,[AmountSummary],"<" &
Format([AmountSummary],"$#,##0.00;($#,##0.00)") & ">")
Thanks for any help

Ok, you'll have to explain your meaning here. What do you mean by "the
credits are just adding up the total and not crediting it"?

What data do you have?

What result do you want?

John W. Vinson[MVP]

 

[John Vinson]

Thanks John, Its a Statement that lists a Clients Totals Owing for each
Invoice and Payments made by that Client
I am trying to put a running balance next to these totals , I do have a
final balance at the bottom of the report
Yes the payment Credits are adding up the running balance when they should
be deducting

Bob...

You can see your table. You understand your data. I cannot, and do
not.

All I can suggest is that you multiply the credit by -1 prior to
adding it.

John W. Vinson[MVP]

 

[Bob]

Bob...

You can see your table. You understand your data. I cannot, and do
not.

All I can suggest is that you multiply the credit by -1 prior to
adding it.

John W. Vinson[MVP]

John this is what gets the total at the bottom of the report

=IIf(IsNumeric([Reports]![rptOwnerPaymentMethod]![subChildOwnerInvoiceAmount].[Report].[tbTotalInvoiceAmount])=True,[Reports]![rptOwnerPaymentMethod]![subChildOwnerInvoiceAmount].[Report].[tbTotalInvoiceAmount],0)
Thanks for any Help......Bob

 

[John Vinson]

Thanks for the help John, I did that but it just increases the total even
when there is credits showing in totals, it is based on this query:

Ok... let's try this: editing the layout of the query somewhat for
readability:

SELECT tblInvoice.OwnerID,
tblInvoice.OwnerName,
tblInvoice.InvoiceDate AS OnDate,
iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary,
tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,
0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION
SELECT tblAccountStatus.OwnerID,
NULL,
tblAccountStatus.BillDate AS OnDate,
tblAccountStatus.ModeOfPayment AS Description,
-1 * tblAccountStatus.PaidAmount AS Credit,
NULL,
BillID,
-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom
And
Forms!frmBillStatement!tbDateTo);

Note that I'm just treating PaidAmount in the second query as a
negative value. You don't need the Flag at all.


John W. Vinson[MVP]

 

[Bob]

Ok... let's try this: editing the layout of the query somewhat for
readability:

SELECT tblInvoice.OwnerID,
tblInvoice.OwnerName,
tblInvoice.InvoiceDate AS OnDate,
iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary,
tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,
0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION
SELECT tblAccountStatus.OwnerID,
NULL,
tblAccountStatus.BillDate AS OnDate,
tblAccountStatus.ModeOfPayment AS Description,
-1 * tblAccountStatus.PaidAmount AS Credit,
NULL,
BillID,
-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom
And
Forms!frmBillStatement!tbDateTo);

Note that I'm just treating PaidAmount in the second query as a
negative value. You don't need the Flag at all.


John W. Vinson[MVP]

John that added every charge and payment together to give me a total of all
charges and payments
Thanks Bob

 

[John Vinson]

John that added every charge and payment together to give me a total of all
charges and payments
Thanks Bob

Again, Bob...

I DO NOT KNOW WHAT IS IN YOUR TABLE.

Sorry for yelling, but this is getting frustrating!

I have no way to know a) what numbers are in your table, b) what sum
you're getting, or even - based on what I can understand from this
thread - c) what you want.

Could you give a six-row example of the data, and what running sum you
want to see? Please?

John W. Vinson[MVP]

 

[Bob]

Again, Bob...

I DO NOT KNOW WHAT IS IN YOUR TABLE.

Sorry for yelling, but this is getting frustrating!

I have no way to know a) what numbers are in your table, b) what sum
you're getting, or even - based on what I can understand from this
thread - c) what you want.

Could you give a six-row example of the data, and what running sum you
want to see? Please?

John W. Vinson[MVP]

John is there any where I can post up a jpeg file......Thanks Bob

 

[John Vinson]

John is there any where I can post up a jpeg file......Thanks Bob

No.

Type six rows, with two numbers in each: the value in your query, and
the desired running sum.


John W. Vinson[MVP]

 

[Bob]

No.

Type six rows, with two numbers in each: the value in your query, and
the desired running sum.


John W. Vinson[MVP]

Overdue $100.00
Running Balance
1 Jul 06 Inv 23 $12.00 $112.00
20 July 06 Payment $12.00 $100.00
1 Mar 06 Inv 24 $24.00 $124.00
20 mar 06 Payment $124.00 $.0.00
1 Apr 06 Inv 36 $78.00 $78.00
20 Apr 06 Payment $50.00 $28.00

This is what I am trying to achieve, Thanks for your Help....Bob

 

[Bob]

No.

Type six rows, with two numbers in each: the value in your query, and
the desired running sum.


John W. Vinson[MVP]

Overdue $100.00
Running Balance
1 Jul 06 Inv 23 $12.00 $112.00
20 July 06 Payment $12.00 $100.00
1 Mar 06 Inv 24 $24.00 $124.00
20 mar 06 Payment $124.00 $.0.00
1 Apr 06 Inv 36 $78.00 $78.00
20 Apr 06 Payment $50.00 $28.00

This is what I am trying to achieve, Thanks for your Help....Bob

 

[John Vinson]

1 Jul 06 Inv 23 $12.00 $112.00
20 July 06 Payment $12.00 $100.00
1 Mar 06 Inv 24 $24.00 $124.00
20 mar 06 Payment $124.00 $.0.00
1 Apr 06 Inv 36 $78.00 $78.00
20 Apr 06 Payment $50.00 $28.00

This is what I am trying to achieve, Thanks for your Help....Bob

The code I posted should do exactly that, IF...

the currency amount is negative for credits, positive for debits;
you're displaying it on a Report textbox; and that textbox has the Sum
Over Group set. What is the Control Source of the textbox?

Perhaps you could (again) post the SQL view of your query; also try
setting the running sum property of the textbox to none to see if the
numeric values are in fact what you expect (positive and negative as
appropriate).

John W. Vinson[MVP]

 

[Bob]

The code I posted should do exactly that, IF...

the currency amount is negative for credits, positive for debits;
you're displaying it on a Report textbox; and that textbox has the Sum
Over Group set. What is the Control Source of the textbox? ****AmountSummary*****

Perhaps you could (again) post the SQL view of your query; also try
setting the running sum property of the textbox to none to see if the
numeric values are in fact what you expect (positive and negative as
appropriate).

John W. Vinson[MVP]

John, thanks for any help, where my credits are showing they are actually
working in running total as debits, I made a debit payment in my credit
payments and it did minus the running total, plus I forgot I will have to
add in [tbOverdueAmount]
I could not find this: running sum property of the textbox to none
Query:
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo);

 

[John Vinson]

John, thanks for any help, where my credits are showing they are actually
working in running total as debits, I made a debit payment in my credit
payments and it did minus the running total, plus I forgot I will have to
add in [tbOverdueAmount]
I could not find this: running sum property of the textbox to none
Query:
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo);

Ok.

You ignored my suggestion, which WOULD have worked.

Try again:

SELECT tblInvoice.OwnerID, tblInvoice.OwnerName,
tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate
AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,


-1 * tblAccountStatus.PaidAmount AS Credit,



NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom
And
Forms!frmBillStatement!tbDateTo);

Note the difference? There's a -1 * in the Credit calculation. This
will treat the Credit AS A NEGATIVE NUMBER, so that it will subtract
rather than adding.

When I posted this before, I actually meant for you to try it, not
just to look at it and rerun the query which we already know does not
work.

John W. Vinson[MVP]

 

[Bob]

John, thanks for any help, where my credits are showing they are actually
working in running total as debits, I made a debit payment in my credit
payments and it did minus the running total, plus I forgot I will have to
add in [tbOverdueAmount]
I could not find this: running sum property of the textbox to none
Query:
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo);

Ok.

You ignored my suggestion, which WOULD have worked.

Try again:

SELECT tblInvoice.OwnerID, tblInvoice.OwnerName,
tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate
AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,


-1 * tblAccountStatus.PaidAmount AS Credit,



NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom
And
Forms!frmBillStatement!tbDateTo);

Note the difference? There's a -1 * in the Credit calculation. This
will treat the Credit AS A NEGATIVE NUMBER, so that it will subtract
rather than adding.

When I posted this before, I actually meant for you to try it, not
just to look at it and rerun the query which we already know does not
work.

John W. Vinson[MVP]

Sorry John, I considered myself told off
Worked BRILLIANT, 2 Problems Running Total should also be picking up an
Overdue Amount at top of form
[tbOverdueAmount]
Control Source: =NZ(DSum("OwnerPercentAmount","tblInvoice","OwnerID =" &
tbOwnerID.Value & " and InvoiceDate <" &
Format("Forms!frmBillStatement![tbDateFrom]","mm/dd/yyyy")),0)-NZ(DSum("PaidAmount","tblAccountStatus","OwnerID
=" & tbOwnerID.Value & " and BillDate <" &
Format("Forms!frmBillStatement![tbDateFrom]","mm/dd/yyyy")),0)
And now the Total Amount at the Bottom of the form is adding everything
Credits and Debits
[tbInvoiceAmount]
Control
Source:=IIf(IsNumeric(Reports!rptOwnerPaymentMethod!subChildOwnerInvoiceAmount.Report.tbTotalInvoiceAmount)=True,Reports!rptOwnerPaymentMethod!subChildOwnerInvoiceAmount.Report.tbTotalInvoiceAmount,0)

Thanks for your effort ........Bob

 

[Bob]

John, thanks for any help, where my credits are showing they are actually
working in running total as debits, I made a debit payment in my credit
payments and it did minus the running total, plus I forgot I will have to
add in [tbOverdueAmount]
I could not find this: running sum property of the textbox to none
Query:
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate
AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate
AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo);

Ok.

You ignored my suggestion, which WOULD have worked.

Try again:

SELECT tblInvoice.OwnerID, tblInvoice.OwnerName,
tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate
AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,


-1 * tblAccountStatus.PaidAmount AS Credit,



NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom
And
Forms!frmBillStatement!tbDateTo);

Note the difference? There's a -1 * in the Credit calculation. This
will treat the Credit AS A NEGATIVE NUMBER, so that it will subtract
rather than adding.

When I posted this before, I actually meant for you to try it, not
just to look at it and rerun the query which we already know does not
work.

John W. Vinson[MVP]

Sorry John, I considered myself told off
Worked BRILLIANT, 2 Problems Running Total should also be picking up an
Overdue Amount at top of form

Oops Report

[tbOverdueAmount]
Control Source: =NZ(DSum("OwnerPercentAmount","tblInvoice","OwnerID =" &
tbOwnerID.Value & " and InvoiceDate <" &
Format("Forms!frmBillStatement![tbDateFrom]","mm/dd/yyyy")),0)-NZ(DSum("PaidAmount","tblAccountStatus","OwnerID
=" & tbOwnerID.Value & " and BillDate <" &
Format("Forms!frmBillStatement![tbDateFrom]","mm/dd/yyyy")),0)
And now the Total Amount at the Bottom of the form is adding everything
Credits and Debits

oops report

[tbInvoiceAmount]
Control
Source:=IIf(IsNumeric(Reports!rptOwnerPaymentMethod!subChildOwnerInvoiceAmount.Report.tbTotalInvoiceAmount)=True,Reports!rptOwnerPaymentMethod!subChildOwnerInvoiceAmount.Report.tbTotalInvoiceAmount,0)

Thanks for your effort ........Bob