Those darn zero values

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

Guest

I'm making an invoice that I need help with. I have a subform with parts,
descriptions, and prices. On my main form I have additional item that will be
priced out also (labor, mics supplies, etc.). The first entry I have on the
main form is the sub total from the sub sheet. To continue with the addition
and to get a final total I need a $0.00 in the sub total text box. I'd tried
several variation of IIF and Nz with no luck. Any Help would be greatly
appreciated. I've messed with it for a day and looked at a lot of help files
with no solution. The expression I have on the main form is "
PartsTotal=ProductsSubform!SubformTotal " The next one that I use to total
everything is "
TotalB4Tax=[PartsTotal]+[Labor]+[MiscShopSupplies]+[Inspection]+[Towing] "
When I have parts involved I have no problem. When I'm just billing out (for
example) Towing, I don't get a Total. The TotalB4Tax needs to see a value in
[PartsTotal]. One of the several expressions I have tried is
Nz([PartsTotal],0) Nz(PartsTotal,0).......Thanks in advance...Lyndac
 
Are they actually Zero values? If the Parts total is Zero it should all still
add up fine. However, if there is no value entered into the Parts total field
then it will be null and the addition wont work.

If they are values simply stored in a single billing table, try setting the
default Parts total to '0' in the table design view.

Also run a query to update all the existing null values to 0's as well

something like: UPDATE tablename SET partstotal = 0 WHERE partstotal is null

Hope that helps!
 
Such a fool I be, I overlooked the line that said you get the parts total
from the subtotal of the subform.

The problem remains the same - its going to be a null value. Have you tested
it with something along the lines of

if isnull(partstotal) then msgbox "It's negative"

It could be because you are not using the full name of the subform perhaps?

Try using forms!parentform!subform.form.subtotal to set the parts total...



Jono said:
Are they actually Zero values? If the Parts total is Zero it should all still
add up fine. However, if there is no value entered into the Parts total field
then it will be null and the addition wont work.

If they are values simply stored in a single billing table, try setting the
default Parts total to '0' in the table design view.

Also run a query to update all the existing null values to 0's as well

something like: UPDATE tablename SET partstotal = 0 WHERE partstotal is null

Hope that helps!

lyndac said:
I'm making an invoice that I need help with. I have a subform with parts,
descriptions, and prices. On my main form I have additional item that will be
priced out also (labor, mics supplies, etc.). The first entry I have on the
main form is the sub total from the sub sheet. To continue with the addition
and to get a final total I need a $0.00 in the sub total text box. I'd tried
several variation of IIF and Nz with no luck. Any Help would be greatly
appreciated. I've messed with it for a day and looked at a lot of help files
with no solution. The expression I have on the main form is "
PartsTotal=ProductsSubform!SubformTotal " The next one that I use to total
everything is "
TotalB4Tax=[PartsTotal]+[Labor]+[MiscShopSupplies]+[Inspection]+[Towing] "
When I have parts involved I have no problem. When I'm just billing out (for
example) Towing, I don't get a Total. The TotalB4Tax needs to see a value in
[PartsTotal]. One of the several expressions I have tried is
Nz([PartsTotal],0) Nz(PartsTotal,0).......Thanks in advance...Lyndac
Click to expand...
Click to expand...
 
Lyndac,

In a standard module, place this code...

Public Function nnz(testvalue As Variant) As Variant
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Function

Then, replace the Control Source setting of your PartsTotal textbox with...
=nnz([ProductsSubform]![SubformTotal])

You can't use the Nz() function in this instance. If there are no
records in the subform, then [SubformTotal] is not Null, it's non-existent.
 
Thanks Steve, That did the trick first time..I wasn't dealing with a null or
zero it "was" a non exsistent.

Steve Schapel said:
Lyndac,

In a standard module, place this code...

Public Function nnz(testvalue As Variant) As Variant
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Function

Then, replace the Control Source setting of your PartsTotal textbox with...
=nnz([ProductsSubform]![SubformTotal])

You can't use the Nz() function in this instance. If there are no
records in the subform, then [SubformTotal] is not Null, it's non-existent.

--
Steve Schapel, Microsoft Access MVP

I'm making an invoice that I need help with. I have a subform with parts,
descriptions, and prices. On my main form I have additional item that will be
priced out also (labor, mics supplies, etc.). The first entry I have on the
main form is the sub total from the sub sheet. To continue with the addition
and to get a final total I need a $0.00 in the sub total text box. I'd tried
several variation of IIF and Nz with no luck. Any Help would be greatly
appreciated. I've messed with it for a day and looked at a lot of help files
with no solution. The expression I have on the main form is "
PartsTotal=ProductsSubform!SubformTotal " The next one that I use to total
everything is "
TotalB4Tax=[PartsTotal]+[Labor]+[MiscShopSupplies]+[Inspection]+[Towing] "
When I have parts involved I have no problem. When I'm just billing out (for
example) Towing, I don't get a Total. The TotalB4Tax needs to see a value in
[PartsTotal]. One of the several expressions I have tried is
Nz([PartsTotal],0) Nz(PartsTotal,0).......Thanks in advance...Lyndac
Click to expand...
Click to expand...
 
Back
Top