Showing previous balance and current billed balance

[CAM]

Hello,


I not quite sure to handle this, I would appreciate some professional help
here. I making a monthly accounts receivable billing report that will show
the previous balance of an insured on one column and show any new billing
activity for the month on the next column with the total balance on another
column, but I not sure in how to do this. For example:


Previous Balance New Activity Current Balance

500.00 50.00 550.00


Now my table is setup this way:

Table 1
Account Number - text field
Amount - Currency field
Period - Date

Should I have another table just for the current billing period then later
append at month end to the cumulutative balance in table 1? I just need
some guidance. Any tips or website to vist will be appreciate. Thank you
in advance.

 

[Allen Browne]

In your report, add the Amount text box twice.
Set these properties for the 2nd instance:
Running Sum Over Group
Format Currency
Name txtAmountRS

The text box now gives you a running sum, which is the current balance. If
you wish to show the previous balance as well, the Control Source would be:
=[txtAmountRS] - [Amount]

Once you get that working, you want this broken down by month. In report
design view, open the Sorting And Grouping dialog (View menu.) Presumably
you already have a grouping on the [Account Number] field, so in the next
row of the dialog, choose Period. In the lower pane of the dialog, set the
grouping interval to: Month. The report now shows the activity for the
month.

However, you need the opening balance for the month before this works
correctly. There are a couple of ways to get this, but I suggest a DSum()
because that will work without printing all months. So, in the Period group
header, add a text box with a Control Source something like this:
=DSum("Amount", "Table1", "([Account Number] = """ & [Account Number] &
""") AND ([Period] < " & Format([Period], "\#mm\/dd\/yyyy\#") & ")"

If the Account Number is a Text field (not a Number type), drop the extra
quotes, i.e.:
=DSum("Amount", "Table1", "([Account Number] = " & [Account Number] & ")
AND ([Period] < " & Format([Period], "\#mm\/dd\/yyyy\#") & ")"

This text box gives you the opening balance for the month. Now you need
another text box to show the Current Balance for the month. Its Control
Source will be:
=[txtOpeningBalance] + [txtAmountRS]