PLEASE PLEASE HELP WITH BALANCE QUESTION

[Cyndi]

I have a database everything works fine but report

I have Invoice Amount, Payment or maybe partial payment

on the report I HAVE TO have it print line by line detail so that



Before any payments are made
Invoice Number Amount Payment
1 10,000 0.00

1st payment is made
1 5,000 5,000

2nd payment is made
1 0.00 5,000

So at each report printing I can see the correct amount due and the
1st payment was 5 and then after the second payment the total is 0.00
this is needed this way because Amount is summed at the end to show
total due as payments are made.

Please please help me

Right Now I have a form where I post payments and the field of amount
is on there(from a query) and I post payments on a subform, ideally
what I would like to happen is when I post payment it would
immediately go up to amount field and adjust amount to new balance
based on payment I just posted and this is the field that would pick
up on reports

Thanks in advance

 

[Allen Browne]

Use a hidden text box with the RunningSum property set, to collect the
progressive total payments made, and therefore the balance remaining.

1. Open your report in Design view.

2. Open the Sorting And Grouping box (View menu).

3. Choose the [Invoice Number] field, and in the lower pane, set Group
Header to Yes. Access adds an Invoice Number header to the report.

4. In the Detail section, add a text box.
Right-click and choose Properties.
Set these properties:
Name txtPaymentsMade
Control Source Payment
Running Sum Over Group
Format Currency
Visible No

5. In the Detail section, add another text box, and set these properties:
Control Source =[Amount] - [txtPaymentsMade]
Format Currency

The running-sum box at step 4 collects the total of payments made for the
group (i.e. for the invoice). Subtrating the payments made from the amount
of the invoice gives the balance due.

 

[Cyndi]

Thank you very much I had spent hours pulling my hair out over this -
I am an accountant trying to do this and I am teaching myself by help
sites and a book. Can I modify this formula to put in an expression
such as IIf {field}='value, [Amount]-[txtfield], 0

Use a hidden text box with the RunningSum property set, to collect the
progressive total payments made, and therefore the balance remaining.

1. Open your report in Design view.

2. Open the Sorting And Grouping box (View menu).

3. Choose the [Invoice Number] field, and in the lower pane, set Group
Header to Yes. Access adds an Invoice Number header to the report.

4. In the Detail section, add a text box.
Right-click and choose Properties.
Set these properties:
Name txtPaymentsMade
Control Source Payment
Running Sum Over Group
Format Currency
Visible No

5. In the Detail section, add another text box, and set these properties:
Control Source =[Amount] - [txtPaymentsMade]
Format Currency

The running-sum box at step 4 collects the total of payments made for the
group (i.e. for the invoice). Subtrating the payments made from the amount
of the invoice gives the balance due.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a database everything works fine but report

I have Invoice Amount, Payment or maybe partial payment

on the report I HAVE TO have it print line by line detail so that



Before any payments are made
Invoice Number Amount Payment
1 10,000 0.00

1st payment is made
1 5,000 5,000

2nd payment is made
1 0.00 5,000

So at each report printing I can see the correct amount due and the
1st payment was 5 and then after the second payment the total is 0.00
this is needed this way because Amount is summed at the end to show
total due as payments are made.

Please please help me

Right Now I have a form where I post payments and the field of amount
is on there(from a query) and I post payments on a subform, ideally
what I would like to happen is when I post payment it would
immediately go up to amount field and adjust amount to new balance
based on payment I just posted and this is the field that would pick
up on reports

Thanks in advance

 

[Allen Browne]

Yes, in the context of the report, use the Immediate If function
=IIf([SomeField] = 999, [Amount] - [txtField], 0)

If you are attempting to do a progressive balance selectively, do it at the
query level. Create a calculated field in the query, by typing an expression
such as this into the Field row:
MyConditionalField: IIf([SomeField] = 999, [Amount], 0)
You can now get a running sum on MyConditionalField in the report (i.e. the
runninng sum includes the Amount only from the rows where MyField is 999).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you very much I had spent hours pulling my hair out over this -
I am an accountant trying to do this and I am teaching myself by help
sites and a book. Can I modify this formula to put in an expression
such as IIf {field}='value, [Amount]-[txtfield], 0



"Allen Browne" <[email protected]> wrote in message

Use a hidden text box with the RunningSum property set, to collect the
progressive total payments made, and therefore the balance remaining.

1. Open your report in Design view.

2. Open the Sorting And Grouping box (View menu).

3. Choose the [Invoice Number] field, and in the lower pane, set Group
Header to Yes. Access adds an Invoice Number header to the report.

4. In the Detail section, add a text box.
Right-click and choose Properties.
Set these properties:
Name txtPaymentsMade
Control Source Payment
Running Sum Over Group
Format Currency
Visible No

5. In the Detail section, add another text box, and set these properties:
Control Source =[Amount] - [txtPaymentsMade]
Format Currency

The running-sum box at step 4 collects the total of payments made for the
group (i.e. for the invoice). Subtrating the payments made from the amount
of the invoice gives the balance due.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a database everything works fine but report

I have Invoice Amount, Payment or maybe partial payment

on the report I HAVE TO have it print line by line detail so that



Before any payments are made
Invoice Number Amount Payment
1 10,000 0.00

1st payment is made
1 5,000 5,000

2nd payment is made
1 0.00 5,000

So at each report printing I can see the correct amount due and the
1st payment was 5 and then after the second payment the total is 0.00
this is needed this way because Amount is summed at the end to show
total due as payments are made.

Please please help me

Right Now I have a form where I post payments and the field of amount
is on there(from a query) and I post payments on a subform, ideally
what I would like to happen is when I post payment it would
immediately go up to amount field and adjust amount to new balance
based on payment I just posted and this is the field that would pick
up on reports

Thanks in advance