percentages

[sdel_nevo]

hi All

i am trying to add a discount as a percentage to an orders form, if my order total is £100 and i give a discount of 10% the order is correctly priced at £90

if i then add vat @ 20% to this im getting £110??

it should be £108

the formula i am using for this is

=[Orders Details Subform].[Form]![Running Total Txt Box]*[VAT Rate TXT Box]+[Orders Details Subform].[Form]![Running Total Txt Box]*(1-[Discount])

[running total txt box] calculates the total order price, vat rate txt box holds the vat percentage, in this case 20% and *(1-[Discount]) then deducts the discount percentage from the order in this case 10%

any ideas as to what i'm doing wrong.

many thanks



steve

[Bob Fitz]

Bit of a guess, but try:
=(([Orders Details Subform].[Form]![Running Total Txt Box]*[VAT Rate TXT Box])+[Orders Details Subform].[Form]![Running Total Txt Box])*(1-[Discount])

[sdel_nevo]

Hi Bob

tried that, still get the same result

Steve

[Bob Fitz]

How about:
=(([Orders Details Subform].[Form]![Running Total Txt Box]*(1-[Discount]))*[VAT Rate TXT Box])

[sdel_nevo]

Hi Bob

tried that and this

=(([Orders Details Subform].[Form]![Running Total Txt Box]*(1-[Discount])*[VAT Rate TXT Box]+[Orders Details Subform].[Form]![Running Total Txt Box])) still no joy

i have also changed the feild type from single to double but that mad no difference either.

Steve

[Dal Jeanis]

Step by step, I hope.

=[Orders Details Subform].[Form]![Running Total Txt Box] is your taxable total, pre-discount. (L100.00)

=[VAT Rate TXT Box] is your VAT rate. (0.20)

=[Discount] is your discount rate (0.10)

=(1-[Discount]) is the percentage remaining after discount. (0.90)

=[Orders Details Subform].[Form]![Running Total Txt Box]*(1-[Discount]) is the taxable total, post-discount. (L90.00)

=[Orders Details Subform].[Form]![Running Total Txt Box]*(1-[Discount])*[VAT Rate TXT Box] is the discounted VAT amount. (L18.00)

=[Orders Details Subform].[Form]![Running Total Txt Box]*(1-[Discount])+[Orders Details Subform].[Form]![Running Total Txt Box]*(1-[Discount])*[VAT Rate TXT Box] is the total amount due. (L108.00)

=[Orders Details Subform].[Form]![Running Total Txt Box]*(1-[Discount])*(1+[VAT Rate TXT Box]) is also the total amount due. (L108.00)

[Bob Fitz]

I've stopped guessing. This works for me:
=(([Orders Details Subform].[Form]![Running Total Txt Box]*[VAT Rate TXT Box]+[Orders Details Subform].[Form]![Running Total Txt Box]))-(([Orders Details Subform].[Form]![Running Total Txt Box]*[VAT Rate TXT Box]+[Orders Details Subform].[Form]![Running Total Txt Box])*[Discount])

[Dal Jeanis]

Bob's should work. It amounts to this

= T*V+T -(T*V+T)*D

which becomes this

= (T*V+T)*(1-D)
= T*(1+V)*(1-D)

which is mathematically the same as my last two

=T*(1-D) + T*V*(1-D)
=T*(1-D)*(1+V)

Bet you never thought you'd use algebra again...

[sdel_nevo]

Hi Bob and Dal

sorry for not getting back sooner,

been tearing my hair out over this, i inherited this database from a new client, and the table structure and calculations on a large percentage of the forms have proved to be very dubious to say the least. "not sure i would rely on them for my VAT return!!!!!

any way, i have spent the most part of the day redesigning and redoing the calculations and the formula now works Woop Woop.

im just looking forward to the other nasty surprises lol

many thanks for all your replies

Steve

[Bob Fitz]

Bob's should work. It amounts to this

= T*V+T -(T*V+T)*D

which becomes this

= (T*V+T)*(1-D)
= T*(1+V)*(1-D)

which is mathematically the same as my last two

=T*(1-D) + T*V*(1-D)
=T*(1-D)*(1+V)

Bet you never thought you'd use algebra again...

Looks like I should have paid more attention to those lessons that were such a long time ago.

[Dal Jeanis]

I was wondering why the VAT amount calculated wasn't going actually into a field somewhere, since presumably you have to pass that money along to the government, as collected...