How to calculate OrderTotal in Form?

[bsarman]

I want to calculate “OrderTotal” by multiplying “Price” by “Quantity” minus “DiscountPercentage”

OrderTotal = (Price*Quantity)-DiscountPercentage

-- Price is already populated in the form and is a text box.
-- Quantity will be filled out by salesrep. (Should it be text box or unbound?)
-- DiscountPercentage will be filled out by salesrep as well and should be from 1 to 20. (Should it be a text box or unbound?)

How do I do this?

Thanks for your generosity in sharing the knowledge.

 

[tomaustin]

I have some of the ansers, not all!

I know that fot the discunt part, In a query you will need s an if statement looking something like:

IFF("Discount"=Yes,(price*quantity)-dscount,
(price*quantity)

Something like that, but you may run into a problem, if the rep doesn't enter 0 if there is no discount. I would make that a default value

I can't answer the one about unbound text fields, I have only used them in a very different context.

Dont know if that helps at all, sorry!

Tom

 

[bsarman]

Thank you. But I don't think that's the answer I am looking for.

 

[tomaustin]

no worries, sorry!

Tom (i'll leave it up to the pro's, i only know some stuff)

 

[willir]

In your unbound field, try...

= (NZ(CNUM([Price]),0))*(NZ(CNUM([Quantity]),0))-NZ(CNUM(DiscountPercentage),0)

Use NZ to accommodate null / no entry fields where NZ will substitute 0 in the equation.

CNUM returns a number in case there is a text entry in the referenced field.

Richard

 

[bsarman]

This almost works.

Private Sub Quantity_AfterUpdate()
OrderTotal.Value = Nz(Me.Price.Column(1, Me.Price.ListIndex), 0) * Quantity.Value
End Sub

When I use this the same OrderTotal appears on all the records.

How can I correct it?

Thanks.

 

[willir]

bsarman

You need to use the REQUERY method, or REFRESH.

When will depend on your situation, but say ON Current Record...

Me.Requery

or, more specifically

Me.OrderTotal.Requery

 

[bsarman]

The solution was different.
I just had to add the code to the Current tab on the form to make it work.

Thanks.