Calculation Question

Hello,

I have a sub form with various fields including Quantity, Labor, Unit Price
and Total price. Every works fine except if I have a zero in labor and or
unit price my total price field doesn't calculate. here is what I am using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ formula but
don't know what it is. Any help would be appreciated!!

Thanks!!

I think you want:
=Nz([Quantity]*[UnitPrice],0) + Nz([Labor],0)

Or, if the labor applies to each unit, perhaps:
=[Quantity] * (Nz([UnitPrice],0) + Nz([Labor],0))
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stockwell43" wrote in message
...
Hello,

I have a sub form with various fields including Quantity, Labor, Unit
Price
and Total price. Every works fine except if I have a zero in labor and or
unit price my total price field doesn't calculate. here is what I am
using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ formula
but
don't know what it is. Any help would be appreciated!!

Thanks!!

Hope this didn't double post, server problems.

How can I get the labor to multiple with the quantity field and total in the
total price field along with the unit price without adding another field?

Currently, if quantity is 1 then labor is $125, if quantity is 2 then labor
should be $250 but reads $125 and doesn't change the total price?

"Stockwell43" wrote:

Hello,

I have a sub form with various fields including Quantity, Labor, Unit Price
and Total price. Every works fine except if I have a zero in labor and or
unit price my total price field doesn't calculate. here is what I am using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ formula but
don't know what it is. Any help would be appreciated!!

Thanks!!

Help has more information about Nz.

How would you determine Total Price without a unit price? Would you
substitute 0 for Labor if it is null?

Assuming that the formula you want is:
(Quantity * UnitPrice) + Labor
rather than:
Quantity * (UnitPrice + Labor)
you could use Nz to substitute a 0 for Labor if it is null:
=([Quantity]*[UnitPrice]) + Nz([Labor],0)

Access will do the calculation without the parentheses around:
([Quantity]*[UnitPrice])

I added the parentheses to make it clearer here. You do need the
parentheses with Nz.

I have no suggestion about how to determine a TotalPrice without a
UnitPrice. What rule guides that situation? What would you substitute for
Null in UnitPrice? By the way, if you are storing TotalPrice, don't.
Calculate it on the fly as needed.

"Stockwell43" wrote in message
...
Hello,

I have a sub form with various fields including Quantity, Labor, Unit
Price
and Total price. Every works fine except if I have a zero in labor and or
unit price my total price field doesn't calculate. here is what I am
using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ formula
but
don't know what it is. Any help would be appreciated!!

Thanks!!

Hi Allen, thank you so much for replying. Let me just explain one thing.

I am using the quantity field for both labor and unit price depaneding on
whther the user is createing a service invoice or a purchase invoice. I know
this is not the proper way but I didn't want to add another field. If I need
to change something please let me know. Otherwise, I am going to try both you
code now and let you know how I make out.

Thank you Allen!!

"Allen Browne" wrote:

I think you want:
=Nz([Quantity]*[UnitPrice],0) + Nz([Labor],0)

Or, if the labor applies to each unit, perhaps:
=[Quantity] * (Nz([UnitPrice],0) + Nz([Labor],0))
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stockwell43" wrote in message
...
Hello,

I have a sub form with various fields including Quantity, Labor, Unit
Price
and Total price. Every works fine except if I have a zero in labor and or
unit price my total price field doesn't calculate. here is what I am
using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ formula
but
don't know what it is. Any help would be appreciated!!

Thanks!!

The problem is that Access treats your expression this way:
=([Quantity]*[UnitPrice]) + [Labor]
With real numbers:
= (4*2) + 4
which is:
= 8 + 4
which comes out to 12

Is this the expression you want?
=[Quantity]*([UnitPrice]+[Labor])
It will give you a different result:
= 4 * (2 + 4)
which is:
= 4 * 6
which comes out to 24

If you apply Nz to UnitPrice and Labor, substituting 0 for null, and both
are Null, the result of the calculation will be 0. Is that your intention?

"Stockwell43" wrote in message
...
Hope this didn't double post, server problems.

How can I get the labor to multiple with the quantity field and total in
the
total price field along with the unit price without adding another field?

Currently, if quantity is 1 then labor is $125, if quantity is 2 then
labor
should be $250 but reads $125 and doesn't change the total price?

"Stockwell43" wrote:

Hello,

I have a sub form with various fields including Quantity, Labor, Unit
Price
and Total price. Every works fine except if I have a zero in labor and or
unit price my total price field doesn't calculate. here is what I am
using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ formula
but
don't know what it is. Any help would be appreciated!!

Thanks!!

Hi Bruce,

No, I am not storing it on the table it is an unbound texbox that I am doing
the calculating in the report.

Well, your correct. If I change the quantity to 2 the labor doesn't change
and if unit price is null the total price stays the same. Do I need to add
another field for hours? and then somehow have the total price add the sum of
Hours*Labor + Quantity*UnitPrice?

I sure wish I had knowledge of you folks, I would had this figured out three
days ago. )

Any help would be most appreciated!!!!

"BruceM" wrote:

Help has more information about Nz.

How would you determine Total Price without a unit price? Would you
substitute 0 for Labor if it is null?

Assuming that the formula you want is:
(Quantity * UnitPrice) + Labor
rather than:
Quantity * (UnitPrice + Labor)
you could use Nz to substitute a 0 for Labor if it is null:
=([Quantity]*[UnitPrice]) + Nz([Labor],0)

Access will do the calculation without the parentheses around:
([Quantity]*[UnitPrice])

I added the parentheses to make it clearer here. You do need the
parentheses with Nz.

I have no suggestion about how to determine a TotalPrice without a
UnitPrice. What rule guides that situation? What would you substitute for
Null in UnitPrice? By the way, if you are storing TotalPrice, don't.
Calculate it on the fly as needed.

"Stockwell43" wrote in message
...
Hello,

I have a sub form with various fields including Quantity, Labor, Unit
Price
and Total price. Every works fine except if I have a zero in labor and or
unit price my total price field doesn't calculate. here is what I am
using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ formula
but
don't know what it is. Any help would be appreciated!!

Thanks!!

Bruce,

This code worked:Quantity * (UnitPrice + Labor), but the NZ didn't.

Also, how do I get the sum? If I use =Sum Quantity * (UnitPrice + Labor) it
doesn't do anything?

I'm sorry, but I do appreciate the help

"BruceM" wrote:

Help has more information about Nz.

How would you determine Total Price without a unit price? Would you
substitute 0 for Labor if it is null?

Assuming that the formula you want is:
(Quantity * UnitPrice) + Labor
rather than:
Quantity * (UnitPrice + Labor)
you could use Nz to substitute a 0 for Labor if it is null:
=([Quantity]*[UnitPrice]) + Nz([Labor],0)

Access will do the calculation without the parentheses around:
([Quantity]*[UnitPrice])

I added the parentheses to make it clearer here. You do need the
parentheses with Nz.

I have no suggestion about how to determine a TotalPrice without a
UnitPrice. What rule guides that situation? What would you substitute for
Null in UnitPrice? By the way, if you are storing TotalPrice, don't.
Calculate it on the fly as needed.

"Stockwell43" wrote in message
...
Hello,

I have a sub form with various fields including Quantity, Labor, Unit
Price
and Total price. Every works fine except if I have a zero in labor and or
unit price my total price field doesn't calculate. here is what I am
using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ formula
but
don't know what it is. Any help would be appreciated!!

Thanks!!

You know your business rules. We do not. If you are building boats and the
labor cost for each boat is the same, you do not need hours. However, in
that case I would expect the labor to be part of the UnitPrice. Some
explanation of the situation is needed before I or anybody else can say if
you need an Hours field.

"Stockwell43" wrote in message
...
Hi Bruce,

No, I am not storing it on the table it is an unbound texbox that I am
doing
the calculating in the report.

Well, your correct. If I change the quantity to 2 the labor doesn't change
and if unit price is null the total price stays the same. Do I need to add
another field for hours? and then somehow have the total price add the sum
of
Hours*Labor + Quantity*UnitPrice?

I sure wish I had knowledge of you folks, I would had this figured out
three
days ago. )

Any help would be most appreciated!!!!

"BruceM" wrote:

Help has more information about Nz.

How would you determine Total Price without a unit price? Would you
substitute 0 for Labor if it is null?

Assuming that the formula you want is:
(Quantity * UnitPrice) + Labor
rather than:
Quantity * (UnitPrice + Labor)
you could use Nz to substitute a 0 for Labor if it is null:
=([Quantity]*[UnitPrice]) + Nz([Labor],0)

Access will do the calculation without the parentheses around:
([Quantity]*[UnitPrice])

I added the parentheses to make it clearer here. You do need the
parentheses with Nz.

I have no suggestion about how to determine a TotalPrice without a
UnitPrice. What rule guides that situation? What would you substitute
for
Null in UnitPrice? By the way, if you are storing TotalPrice, don't.
Calculate it on the fly as needed.

"Stockwell43" wrote in message
...
Hello,

I have a sub form with various fields including Quantity, Labor, Unit
Price
and Total price. Every works fine except if I have a zero in labor and
or
unit price my total price field doesn't calculate. here is what I am
using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ
formula
but
don't know what it is. Any help would be appreciated!!

Thanks!!

It's basically an invoice received by a company that maintence the equipment.
So if it's a service invoice they will charge by the hour and any parts.

So if they repair a printer and it took 1.5 hours labor should be $187.50
and if the part was new rollers then unit price would be $109. So my subform
will have two lines, one to show the labor and one to show the part. My total
price should be 296.50. which should show on the main form in an unbound
textbox. All was working fine but I need to total the labor field if more
than one hour AND if no unit price is inserted.

Is this making sense or am I making this too confusing?

Thanks!

"BruceM" wrote:

You know your business rules. We do not. If you are building boats and the
labor cost for each boat is the same, you do not need hours. However, in
that case I would expect the labor to be part of the UnitPrice. Some
explanation of the situation is needed before I or anybody else can say if
you need an Hours field.

"Stockwell43" wrote in message
...
Hi Bruce,

No, I am not storing it on the table it is an unbound texbox that I am
doing
the calculating in the report.

Well, your correct. If I change the quantity to 2 the labor doesn't change
and if unit price is null the total price stays the same. Do I need to add
another field for hours? and then somehow have the total price add the sum
of
Hours*Labor + Quantity*UnitPrice?

I sure wish I had knowledge of you folks, I would had this figured out
three
days ago. )

Any help would be most appreciated!!!!

"BruceM" wrote:

Help has more information about Nz.

How would you determine Total Price without a unit price? Would you
substitute 0 for Labor if it is null?

Assuming that the formula you want is:
(Quantity * UnitPrice) + Labor
rather than:
Quantity * (UnitPrice + Labor)
you could use Nz to substitute a 0 for Labor if it is null:
=([Quantity]*[UnitPrice]) + Nz([Labor],0)

Access will do the calculation without the parentheses around:
([Quantity]*[UnitPrice])

I added the parentheses to make it clearer here. You do need the
parentheses with Nz.

I have no suggestion about how to determine a TotalPrice without a
UnitPrice. What rule guides that situation? What would you substitute
for
Null in UnitPrice? By the way, if you are storing TotalPrice, don't.
Calculate it on the fly as needed.

"Stockwell43" wrote in message
...
Hello,

I have a sub form with various fields including Quantity, Labor, Unit
Price
and Total price. Every works fine except if I have a zero in labor and
or
unit price my total price field doesn't calculate. here is what I am
using:
=[Quantity]*[UnitPrice]+[Labor]. I know I should be using the NZ
formula
but
don't know what it is. Any help would be appreciated!!

Thanks!!