Calculate controls in VBA

  • Thread starter Thread starter haukalid
  • Start date Start date
H

haukalid

I have an order-database with a form based on an product-table for
registration and updating of the products.

When I receive an invoice, the prizes are sometimes incl VAT and sometimes
ex VAT. In my products table I've a field for both of those values.

In my productsform I then have a "exlvat" and "inclvat" textbox bound to
it's respectivly fields in the productstable.

I would like to have an AfterUpdate effect that calculates the exlvat when
input the inclvat on my form.

I've tryed:
Me!exlvat.value = Me!inclvat.value / 1,25
but I only get errormessages.

I need a way to divide "inclvat" on 1,25...
 
Problem solved:
Me!exlvat.value = (Me!inclvat.value) / "1,25"

I've tried with [brackets] and other different solutions except this one...
But this solved my case.
 
I have not much experience with other language editions of Access, but I
believe that VBA only understands "." as a decimal separator, so you should
use:
Me!exlvat.value = Me!inclvat.value / 1.25

Also, a small tip:

There is no need to store both the prices in your table. All you need is a
single price field and a boolean (yes/no) field for "InclVAT".

Then, in a query, you can calculate both prices from the base price:

PriceInclVAT: [Price] * IIf( [InclVAT], 1, 1.25 )
PriceExclVAT: [Price] / IIf( [InclVAT], 1.25, 1 )
 
PriceInclVAT: [Price] * IIf( [InclVAT], 1, 1.25 )
PriceExclVAT: [Price] / IIf( [InclVAT], 1.25, 1 )
Click to expand...

Too quick with the send button there :-)

PriceInclVAT: [Price] * IIf( [InclVAT], 1, 1.25 )
PriceExclVAT: [Price] / IIf( [ExclVAT], 1.25, 1 )
 
Hi Stuart

Sorry, but you're mistaken ;-)

There is no ExclVAT field in the design I suggested. If the base price
includes VAT then PriceInclVAT is unchanged, but PriceExclVAT is reduced by
a factor of 1.25.

Likewise, if the base price does NOT include VAT then PriceExclVAT is
unchanged, but PriceInclVAT is increased by a factor of 1.25.
--
Cheers :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Stuart McCall said:
PriceInclVAT: [Price] * IIf( [InclVAT], 1, 1.25 )
PriceExclVAT: [Price] / IIf( [InclVAT], 1.25, 1 )
Click to expand...

Too quick with the send button there :-)

PriceInclVAT: [Price] * IIf( [InclVAT], 1, 1.25 )
PriceExclVAT: [Price] / IIf( [ExclVAT], 1.25, 1 )
Click to expand...
 
Back
Top