It doesn't Add up

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

Guest

Hello,

I have a report with at sub report in it. The main report is of employees
and the sub report is of their overtime. This is a one to many relationship.
I need to add various fields to come to a total field. Several of the fields
are on the main report and one is on the sub report. The sub report field is
a fiels that may not have a value. For example; if an employee does not have
overtime in a given week then there is no value to add. When this is the case
the calculation field returns an #error. I do not know how to fix that. I
also do not know exactly what the #error message is telling me. Any help
would be appreciated.

Joe
Papi
 
In your Sum field in the subreport, you need something to turn Null values
(or no records returned) into zeros. Look at the NZ function. This will take
nulls and return zeros. Then your calculation will the the sum of overtime
hours as zero instead of null. This should prevent the #error.



Barry
 
Hello,

I have a report with at sub report in it. The main report is of employees
and the sub report is of their overtime. This is a one to many relationship.
I need to add various fields to come to a total field. Several of the fields
are on the main report and one is on the sub report. The sub report field is
a fiels that may not have a value. For example; if an employee does not have
overtime in a given week then there is no value to add. When this is the case
the calculation field returns an #error. I do not know how to fix that. I
also do not know exactly what the #error message is telling me. Any help
would be appreciated.

Joe
Papi
Click to expand...

Look up the Nz() function in VBA help.
=Sum(Nz([FieldName]))
or
= Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
 
Fred,

Does this have to be in a VB statement? I have been using a text box
control. Could you please give some direction if it has to be VB

Thanks

Joe
Papi


fredg said:
Hello,

I have a report with at sub report in it. The main report is of employees
and the sub report is of their overtime. This is a one to many relationship.
I need to add various fields to come to a total field. Several of the fields
are on the main report and one is on the sub report. The sub report field is
a fiels that may not have a value. For example; if an employee does not have
overtime in a given week then there is no value to add. When this is the case
the calculation field returns an #error. I do not know how to fix that. I
also do not know exactly what the #error message is telling me. Any help
would be appreciated.

Joe
Papi
Click to expand...

Look up the Nz() function in VBA help.
=Sum(Nz([FieldName]))
or
= Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
Click to expand...
 
Fred,

Does this have to be in a VB statement? I have been using a text box
control. Could you please give some direction if it has to be VB

Thanks

Joe
Papi

fredg said:
Hello,

I have a report with at sub report in it. The main report is of employees
and the sub report is of their overtime. This is a one to many relationship.
I need to add various fields to come to a total field. Several of the fields
are on the main report and one is on the sub report. The sub report field is
a fiels that may not have a value. For example; if an employee does not have
overtime in a given week then there is no value to add. When this is the case
the calculation field returns an #error. I do not know how to fix that. I
also do not know exactly what the #error message is telling me. Any help
would be appreciated.

Joe
Papi
Click to expand...

Look up the Nz() function in VBA help.
=Sum(Nz([FieldName]))
or
= Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
Click to expand...
Click to expand...

In a Text control is fine.
Directly in an Unbound control Control Source, type it as:
= Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
Make sure th name of the control is NOT the same as the name of any
field used in the expression.

In VBA it would be:
[ControlName] = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
or
VariableName = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
 
Fred,

I am not doing something right. Here is what I have for a control source:

=Sum(Nz([OT rate],0))

Just be clear, this control is on a sub form and, when added to other
controls on the main form it will give me a total.

The resulting expression is on the main form with this sub form field added
to several main form fields.

fredg said:
Fred,

Does this have to be in a VB statement? I have been using a text box
control. Could you please give some direction if it has to be VB

Thanks

Joe
Papi

fredg said:
On Tue, 29 Aug 2006 11:29:03 -0700, Joe C wrote:

Hello,

I have a report with at sub report in it. The main report is of employees
and the sub report is of their overtime. This is a one to many relationship.
I need to add various fields to come to a total field. Several of the fields
are on the main report and one is on the sub report. The sub report field is
a fiels that may not have a value. For example; if an employee does not have
overtime in a given week then there is no value to add. When this is the case
the calculation field returns an #error. I do not know how to fix that. I
also do not know exactly what the #error message is telling me. Any help
would be appreciated.

Joe
Papi

Look up the Nz() function in VBA help.
=Sum(Nz([FieldName]))
or
= Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
Click to expand...
Click to expand...

In a Text control is fine.
Directly in an Unbound control Control Source, type it as:
= Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
Make sure th name of the control is NOT the same as the name of any
field used in the expression.

In VBA it would be:
[ControlName] = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
or
VariableName = Nz([FieldA]) + Nz([FieldB]) + Nz(etc...)
Click to expand...
 
Back
Top