Expressions

[Guest]

I have a column of autopopulated subtotal fields on a form, (Laborer1 through
Laborer6). After these I have another field with an expression to total up
all the subtotals, (Labor_Total). My problem is that unless all six of my
subtotal fields have the value of $0.00 the expression in the total field
will not execute. I can create a blank value in the combo box (Laborer-Name)
that controls the values used in the expression that populates the subtotal
fields but then I have to choose it each time for all six items. To avoid
this can I set up this blank value in the Laborer# fields to be the default
value until it is changed? Or is there another method I can use to
automatically force the default value to be $0.00 in every subtotal row even
before a calculation has been attempted?

Another post on this subject suggested using the Nz value on the control
sources of my Labor_Total field but I didn't fully understand the answer and
my control source is already filled with the expression that calculates the
six subtotals across the rows.

Confused --->

 

[Guest]

Ridnaway,

You can only perform a sum on a table field, not on a calculated one. So if
your row subtotal was named, say, Labor_Total,

=Sum(Labor_Total) will not work. Instead, add up the fields explicitly:

=Sum([Labor1]+[Labor2]+[Labor3]+[Labor4]+[Labor5]+[Labor6])

Hope that helps.
Sprinks

 

[Rick B]

NZ replaces a null value. To replace it with "0" you'd use...


Nz([SomeField],0)


For example....

=Nz([SomeField1],0) + Nz([SomeField2],0) + Nz([SomeField3],0)

 

[Guest]

Ridnaway,

My error. You have to convert nulls to zeros. Since zero is the default
value for the 2nd parameter, it is not explicitly required:

=Sum(Nz([Labor1],0)+Nz([Labor2],0)+Nz([Labor3],0)+Nz([Labor4],0)+Nz([Labor5],0)+Nz([Labor6],0))

Or:

=Sum(Nz([Labor1])+Nz([Labor2])+Nz([Labor3])+Nz([Labor4])+Nz([Labor5])+Nz([Labor6]))

Hope that helps.
Sprinks

 

[Guest]

Rick's is the correct way to do this. As to default values, why not just
specify 0 as the default value of each control?

NZ replaces a null value. To replace it with "0" you'd use...


Nz([SomeField],0)


For example....

=Nz([SomeField1],0) + Nz([SomeField2],0) + Nz([SomeField3],0)



--
Rick B

I have a column of autopopulated subtotal fields on a form, (Laborer1 through
Laborer6). After these I have another field with an expression to total up
all the subtotals, (Labor_Total). My problem is that unless all six of my
subtotal fields have the value of $0.00 the expression in the total field
will not execute. I can create a blank value in the combo box (Laborer-Name)
that controls the values used in the expression that populates the subtotal
fields but then I have to choose it each time for all six items. To avoid
this can I set up this blank value in the Laborer# fields to be the default
value until it is changed? Or is there another method I can use to
automatically force the default value to be $0.00 in every subtotal row even
before a calculation has been attempted?

Another post on this subject suggested using the Nz value on the control
sources of my Labor_Total field but I didn't fully understand the answer and
my control source is already filled with the expression that calculates the
six subtotals across the rows.

Confused --->

 

[Guest]

Thank you all for answering so promptly. I think I've not explained my
problem sufficiently so I'll try to get detailed...

The database is populated via the form. A row of fields reading from left
to right on the form lists [Laborer1_Name] then [Laborer1_Hours] then
[Laborer1_Rate] then [Laborer1_Subtotal]

My Control Source for the Laborer Names is a combo box. As soon as I choose
a name from the combo box that person's Rate is autopopulated in the
[Laborer1_Rate] and when choose a name the [Laborer1_Subtotal] field shows
$0.00. When I add the number of hours into the [Laborer1_Hours] field the
expression executes and does the simple multiplication of Hours times Rate
and places the correct value in the [Laborer1_Subtotal] field.

The problem I'm having is that I have 6 rows available for this and on some
jobs only 2 or 3 laborers are used. Therefore the remaining rows are left
blank and without data the expression for each of the blank rows will not
execute because there are no values in their [LaborerX_Subtotal] field.

I've tried simply making the default values in all the subtotal fields = 0
but it doesn't work. I'm assuming this is because the value is tied to the
expression. If I can simply have all the unused subtotal fields show $0.00 I
think my problem will be solved.

Thanks again I really appreciate your help on this.

Ridnaway,

My error. You have to convert nulls to zeros. Since zero is the default
value for the 2nd parameter, it is not explicitly required:

=Sum(Nz([Labor1],0)+Nz([Labor2],0)+Nz([Labor3],0)+Nz([Labor4],0)+Nz([Labor5],0)+Nz([Labor6],0))

Or:

=Sum(Nz([Labor1])+Nz([Labor2])+Nz([Labor3])+Nz([Labor4])+Nz([Labor5])+Nz([Labor6]))

Hope that helps.
Sprinks

I have a column of autopopulated subtotal fields on a form, (Laborer1 through
Laborer6). After these I have another field with an expression to total up
all the subtotals, (Labor_Total). My problem is that unless all six of my
subtotal fields have the value of $0.00 the expression in the total field
will not execute. I can create a blank value in the combo box (Laborer-Name)
that controls the values used in the expression that populates the subtotal
fields but then I have to choose it each time for all six items. To avoid
this can I set up this blank value in the Laborer# fields to be the default
value until it is changed? Or is there another method I can use to
automatically force the default value to be $0.00 in every subtotal row even
before a calculation has been attempted?

Another post on this subject suggested using the Nz value on the control
sources of my Labor_Total field but I didn't fully understand the answer and
my control source is already filled with the expression that calculates the
six subtotals across the rows.

Confused --->

 

[Guest]

We understood the question, I think, now, you understand the answers.

Thank you all for answering so promptly. I think I've not explained my
problem sufficiently so I'll try to get detailed...

The database is populated via the form. A row of fields reading from left
to right on the form lists [Laborer1_Name] then [Laborer1_Hours] then
[Laborer1_Rate] then [Laborer1_Subtotal]

My Control Source for the Laborer Names is a combo box. As soon as I choose
a name from the combo box that person's Rate is autopopulated in the
[Laborer1_Rate] and when choose a name the [Laborer1_Subtotal] field shows
$0.00. When I add the number of hours into the [Laborer1_Hours] field the
expression executes and does the simple multiplication of Hours times Rate
and places the correct value in the [Laborer1_Subtotal] field.

The problem I'm having is that I have 6 rows available for this and on some
jobs only 2 or 3 laborers are used. Therefore the remaining rows are left
blank and without data the expression for each of the blank rows will not
execute because there are no values in their [LaborerX_Subtotal] field.

I've tried simply making the default values in all the subtotal fields = 0
but it doesn't work. I'm assuming this is because the value is tied to the
expression. If I can simply have all the unused subtotal fields show $0.00 I
think my problem will be solved.

Thanks again I really appreciate your help on this.

Ridnaway,

My error. You have to convert nulls to zeros. Since zero is the default
value for the 2nd parameter, it is not explicitly required:

=Sum(Nz([Labor1],0)+Nz([Labor2],0)+Nz([Labor3],0)+Nz([Labor4],0)+Nz([Labor5],0)+Nz([Labor6],0))

Or:

=Sum(Nz([Labor1])+Nz([Labor2])+Nz([Labor3])+Nz([Labor4])+Nz([Labor5])+Nz([Labor6]))

Hope that helps.
Sprinks

I have a column of autopopulated subtotal fields on a form, (Laborer1 through
Laborer6). After these I have another field with an expression to total up
all the subtotals, (Labor_Total). My problem is that unless all six of my
subtotal fields have the value of $0.00 the expression in the total field
will not execute. I can create a blank value in the combo box (Laborer-Name)
that controls the values used in the expression that populates the subtotal
fields but then I have to choose it each time for all six items. To avoid
this can I set up this blank value in the Laborer# fields to be the default
value until it is changed? Or is there another method I can use to
automatically force the default value to be $0.00 in every subtotal row even
before a calculation has been attempted?

Another post on this subject suggested using the Nz value on the control
sources of my Labor_Total field but I didn't fully understand the answer and
my control source is already filled with the expression that calculates the
six subtotals across the rows.

Confused --->