Calc in Reports = Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

1) I am generating a report which includes the following calc field in the
detail section but I get an Error result:

[Transport Charge] * [Fuel Surcharge]

Transport Charge is currency, Fuel Surharge is a percent.

What am I doing wrong?

2) On the same report, the total of these two generates a Blank result:
[Transport Charge] +([Transport Charge]*[Fuel Surcharge])
 
Are you quite confident that there are no "Null" values?

Any time you are doing math with fields, a null in any of the fields will
mess up your day.

You have to decide if you wish to reject a record with a null (use the
criterion to exclude), or if you want to include a record with a null by
converting it (the null) to a different value. For the latter, see the Nz()
function. Or I suppose you could also go through every field and ensure
there are no nulls...

Depending on your choice and your data, your formula might be changed to
something like:

Nz([Transport Charge],0) * Nz([Fuel Surcharge],0)

This would result in a calculated value of "0" if either were null.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Two other common mistakes:
-Not prefacing the expression with "="
-The name of the control is the same as the name of a field
--
Duane Hookom
MS Access MVP

Jeff Boyce said:
Are you quite confident that there are no "Null" values?

Any time you are doing math with fields, a null in any of the fields will
mess up your day.

You have to decide if you wish to reject a record with a null (use the
criterion to exclude), or if you want to include a record with a null by
converting it (the null) to a different value. For the latter, see the
Nz() function. Or I suppose you could also go through every field and
ensure there are no nulls...

Depending on your choice and your data, your formula might be changed to
something like:

Nz([Transport Charge],0) * Nz([Fuel Surcharge],0)

This would result in a calculated value of "0" if either were null.

Regards

Jeff Boyce
Microsoft Office/Access MVP

aMack said:
1) I am generating a report which includes the following calc field in
the
detail section but I get an Error result:

[Transport Charge] * [Fuel Surcharge]

Transport Charge is currency, Fuel Surharge is a percent.

What am I doing wrong?

2) On the same report, the total of these two generates a Blank result:
[Transport Charge] +([Transport Charge]*[Fuel Surcharge])
Click to expand...
Click to expand...
 
Back
Top