minus discount on invoice

[Gazza2]

What should have been a simple task is now a pain in the rear end.

I have an invoice form(InvoiceHeader) with a continuous subform(InvoiceSub) to enter goods into which works great. After i enter a product the retail price is automatically filled in TxtRetailPrice and then i enter a quantity that the customer wants, after that there is a discount field where i enter an amount which is shown in percent format.

All works fine so far until it comes to calculating the line total.

so if the price of part1 is £12 and quantity is 2 total is £24

If discount = 5% then line total should be £22.80

the totals i get is £23.50 ,£0.01 or £0.00 depending on which of the following formula i have in the txtLineTotal textboxes control source:-

= [discount]/100 * ([TxtRetail] * [Quantity])

= (([discount]/100) *([txtretail]*[Quantity]))

= ([txtretail] * [Quantity]) * ([discount]/100)

obviously im doing something wrong but for the life of me i cant find out where.

Thanks in advance for any help
Gareth

 

[SteveSchapel]

Gareth,

Try it like this:
= [txtretail] * [Quantity] * (1 - [discount]/100)

 

[boblarson]

Try this out:

= (1-([discount]/100)) * ([Quantity] * [txtRetail])

so for 5% you would have

1 - (5/100) * (2 * 12)

(1 - .05) * (24)

.95 * 24 = 22.80

 

[boblarson]

Darn, I'm just too slow typing today.

 

[Gazza2]

Thanks for the quick replies steve and bob

I tried both options and now its getting stranger as it just takes £0.01 of the total giving me the value £23.99.

 

[boblarson]

Thanks for the quick replies steve and bob

I tried both options and now its getting stranger as it just takes £0.01 of the total giving me the value £23.99.

Post what you used EXACTLY, please.

 

[boblarson]

Post what you used EXACTLY, please.

Oh, and in your discount is it really stored as .05 but displays as 5? That could be one reason.

 

[Gazza2]

here is the exact code i used in the txtlinetotal textboxes control source

= [txtretail] * [Movement] * (1 - [discount]/100)

AND

= (1-([discount]/100)) * ([Movement] * [txtRetail])

I had to change the name of quantity to movement as this is the name of my textbox and table field, i just used quantity to try and better explain myself.

As for the discount, it is stored as 5.00% when i look at the table.

Thanks again

 

[GaryPanic]

check you brackets...

 

[boblarson]

As it is showing as 5% in the table, then elminate the /100. So try this:

= [txtretail] * [Movement] * (1 - [discount])

 

[SteveSchapel]

Gareth,

As for the discount, it is stored as 5.00% when i look at the table.

I think Bob has put his finger on the problem here. If you see 5% then the actual value of the field is 0.05

Therefore, try it like this:
= [txtretail] * [Quantity] * (1 - [discount])

 

[GaryPanic]

Item quanity Total Discount discount amount
10 2 20.00 5%



((10*2)/100) *(100-discount)
that should do it --


oops slow fingers

 

[SteveSchapel]

Darn, I'm just too slow typing today.

Probably the anaesthetic wearing off, Bob!

 

[boblarson]

Probably the anaesthetic wearing off, Bob!

My mouth feels so much better today.

 

[Gazza2]

thanks for the replies

= [txtretail] * [Movement] * (1 - [discount])

works like a charm

 

[SteveSchapel]

My mouth feels so much better today.

Happy to hear it!

 

[boblarson]

thanks for the replies

= [txtretail] * [Movement] * (1 - [discount])

works like a charm