Report Concept

[BLD21]

access2007,



I have a report that is using a crosstab query as the source. I have a row heading field showing the sum of "Postage Billed". All good.

What I am trying to come up with is a field called "Postage Balance". Which will subtract the "Postage Billed" from day to day for the month.

At the beginning of every month the Postage Balance will vary. How and where should this field be stored and how can I pull this into my report.

Example of how the report should look like.

Beginning Balance
$1,000.00

Date Postage Billed Postage Balance
5/2 $100.00 $900.00
5/3 $50.00 $850.00
5/4 $10.00 $840.00
____________________________________

Beginning Balance
$500.00

6/1 $10.00 $490.00
6/2 $20.00 $470.00

_____________________________________


My dilemma is how to create and store the beginning balance and also show this on the report. Once stored in the report I can setup a calcuation to subtract the "Postage Billed" from that.

Any ideas?

Thanks to everyone,
Bruce

[apr pillai]

I had a trial run of your problem with some sample data that I had. The image of the Report Design and Report Preview are attached.

I have created a new column SrtDt in the Report Query besides the data fields from Table5. The SQL is given below(some of the fields in the table may not be relevant to you):

SELECT Table5.*, Format([CaseDate],"mm") AS SortDt
FROM Table5
ORDER BY Format([CaseDate],"mm");

You may take the CaseDate as Postage Date and only month is extracted from it for grouping the Report on.

The above Query is used as Report Source and designed the Report with two Unbound text boxes (See the attached image).

The top Unbound Textbox name is OB (stands for Opening Balance) and the second one on the Details Section is named as BAL (for Balance).

The Report Header/Footer Sections must be visible. There are two VBA Sub-Routines with few lines of code to run. You may copy the following VBA Code in the Report Module and save the Report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
[BAL] = [OB] - [Postage]
[OB] = [BAL]
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
[OB] = 10000
End Sub

The initial Billed Amount 10000 is set here as constant. You may save this value into a temporary Table and load into the Program with a Dlookup() Function like:

[OB]=Dlookup("Billed","ParamTable")

The User can update this table when Billed Value changes.