Problem with function to calculate postage value

  • Thread starter Thread starter Richard Lawson
  • Start date Start date
R

Richard Lawson

I have a database which tracks the cost of postage by quanity of items and
cost of postage.

With bulk mailing, $ 0.39 costs $0.371, etc. The database has the true cost
of postage. However we bill clients at the $0.39 cost and need a monthly
summary report for the billing cost.

So, I wrote a function for the report that changes the 0.371 cost to 0.390
in the report window and this works great.

Since the report will list many items for each client, I need to have a
summary value at the bottom of the report. When I try to do a =Sum([Text1]),
of the just calculated postage, the reference is not understood. I have
tried everything to make this work.

Rich
 
I have a database which tracks the cost of postage by quanity of items and
cost of postage.

With bulk mailing, $ 0.39 costs $0.371, etc. The database has the true cost
of postage. However we bill clients at the $0.39 cost and need a monthly
summary report for the billing cost.

So, I wrote a function for the report that changes the 0.371 cost to 0.390
in the report window and this works great.

Since the report will list many items for each client, I need to have a
summary value at the bottom of the report. When I try to do a =Sum([Text1]),
of the just calculated postage, the reference is not understood. I have
tried everything to make this work.

Rich
Click to expand...

Do the calculation as a calculated field in the Query upon which the
report is based, rather than in the controlsource of a textbox on the
report. Then you'll have two fields in the query, a "real" postage
cost and a "charged" postage cost; you can sum them independently.

John W. Vinson[MVP]
 
Rich:

You can't aggregate a computed control, you need to repeat the expression as
the argument of the Sum function. Lets say your function is called
BilledPostageRate and the field who's value is passed into it is called
PostageRate then the expression to Sum the values would be:

=Sum(BilledPostageRate([PostageRate]))

However a better way of doing this than using a function would be to have a
table, PostageRates say, with two columns PostageRate and BilledPostageRate.
For each PostageRate the BilledPostageRate column would have the appropriate
value. You should store the BilledPostageRate value in each invoice record
along with the true postage rate because rates will change over time. This
can be done when you enter the data for each record in a form by looking up
the BilledPostageRate value for the postage rate entered and assigning it to
the BilledPostageRate column with the following code in the Postagerate
control's AfterUpdate event procedure:

Me.BilledPostageRate = DLookup("BilledPostageRate","PostageRates",
"PostageRate = " & Me.PostageRate)

When you edit the PostageRates table as rates change the data in the
invoices will retain the values from the time they record was created,
whereas computing the BilledPostageRate on the fly would mean the compouted
values would change and earlier invoices would show incorrect values. You'll
find an analogous situation to this in the sample Northwind database where
the UnitPrice of a product is looked up in the Products table when a row is
inserted into the Order Details table in the Order Details Subform.

With billed rates stored in a column in the table all you then need to do in
the report is bind a control to that column and you can then aggregate the
values in the normal way.

Ken Sheridan
Stafford, England
 
Back
Top