Summing Null Fields

[serviceenvoy]

When I try to set up an automatic calculation to add multiple fields
together and give me the results in another field, I get no results
unless each of the fields has some value in it. I can of course, set
the default value to zero, but I would rather create an expression
that will still add the fields with values and either skip (or assume
null fields are zero) in a formula.

Current Setup:
unbound field with this string as the control source:
=([total1]+[totallaborcost]+[totalpartscost]+[total2]+[total3])

I know I shouldn't store this in the record but right now I have no
choice until I can redesign my database.

 

[John W. Vinson]

When I try to set up an automatic calculation to add multiple fields
together and give me the results in another field, I get no results
unless each of the fields has some value in it. I can of course, set
the default value to zero, but I would rather create an expression
that will still add the fields with values and either skip (or assume
null fields are zero) in a formula.

Current Setup:
unbound field with this string as the control source:
=([total1]+[totallaborcost]+[totalpartscost]+[total2]+[total3])

I know I shouldn't store this in the record but right now I have no
choice until I can redesign my database.

NZ([total1]) + NZ([totallaborcost]) + NZ([totalpartscost]) + ...

should do the trick for you. NZ() - Null To Zero - will return the value of
the argument if it has one, and 0 if it is NULL.

Note that if you're storing totals in a Table, you're violating a fundamental
tenet of relational databases. Sometimes you need to break the rule... but you
should be sure that it's OK to do so, and know THAT you're breaking the rule
and WHY you're breaking the rule!

John W. Vinson [MVP]

 

[serviceenvoy]

When I try to set up an automatic calculation to add multiple fields
together and give me the results in another field, I get no results
unless each of the fields has some value in it. I can of course, set
the default value to zero, but I would rather create an expression
that will still add the fields with values and either skip (or assume
null fields are zero) in a formula.

Current Setup:
unbound field with this string as the control source:
=([total1]+[totallaborcost]+[totalpartscost]+[total2]+[total3])

I know I shouldn't store this in the record but right now I have no
choice until I can redesign my database.

NZ([total1]) + NZ([totallaborcost]) + NZ([totalpartscost]) + ...

should do the trick for you. NZ() - Null To Zero - will return the value of
the argument if it has one, and 0 if it is NULL.

Note that if you're storing totals in a Table, you're violating a fundamental
tenet of relational databases. Sometimes you need to break the rule... but you
should be sure that it's OK to do so, and know THAT you're breaking the rule
and WHY you're breaking the rule!

John W. Vinson [MVP]

Thanks. Also, is there a way to round fields to certain numbers?
For labor, we like to round to the next highest $25 increment. So if
the amount was $267, we would want to round it to $275
On parts, we like to round to the next highest .99 so if the part was
$7.23, we would want to round that to $7.99.
Can that be done very easily?

 

[Powderfinger]

There's no built-in function that does what you want but here are two that
you can add into one of your modules:

Public Function RoundNext25(curLabor As Currency)
Dim curRounded As Currency, lngWholePart As Long, lngDiff As Long

lngWholePart = CLng(curLabor)

lngDiff = 25 - (lngWholePart Mod 25)
If lngDiff < 25 Then
curRounded = CCur(lngWholePart + lngDiff)
Else
curRounded = CCur(lngWholePart)
End If

RoundNext25 = curRounded

End Function

Public Function NextHighest99Cents(curPart As Currency)
Dim curNextHighest As Currency, lngDollars As Long

lngDollars = CLng(curPart)
If lngDollars > curPart Then lngDollars = lngDollars - 1
curNextHighest = lngDollars + 0.99

NextHighest99Cents = curNextHighest

End Function

When I try to set up an automatic calculation to add multiple fields
together and give me the results in another field, I get no results
unless each of the fields has some value in it. I can of course, set
the default value to zero, but I would rather create an expression
that will still add the fields with values and either skip (or assume
null fields are zero) in a formula.

Current Setup:
unbound field with this string as the control source:
=([total1]+[totallaborcost]+[totalpartscost]+[total2]+[total3])

I know I shouldn't store this in the record but right now I have no
choice until I can redesign my database.

NZ([total1]) + NZ([totallaborcost]) + NZ([totalpartscost]) + ...

should do the trick for you. NZ() - Null To Zero - will return the value of
the argument if it has one, and 0 if it is NULL.

Note that if you're storing totals in a Table, you're violating a fundamental
tenet of relational databases. Sometimes you need to break the rule... but you
should be sure that it's OK to do so, and know THAT you're breaking the rule
and WHY you're breaking the rule!

John W. Vinson [MVP]

Thanks. Also, is there a way to round fields to certain numbers?
For labor, we like to round to the next highest $25 increment. So if
the amount was $267, we would want to round it to $275
On parts, we like to round to the next highest .99 so if the part was
$7.23, we would want to round that to $7.99.
Can that be done very easily?