Vat and Zero Rated

[SG]

I have created a database which has a table called tblVat which has the VAT
rate of 17.5% stored. I use this table to calculate vat etc. What I also
need to incorporate is Zero Rated VAT but I am struggling to understand and
figure out how I can incorporate this in to my database for example if I am
selling an item and there is no vat on the item how do I them configure the
database not to calulate the vat and to also rememeber that the item has no
VAT added when I am not storing the VAT total?

Any suggestions much appreciated.

Kind Regards

S

 

[Allen Browne]

If some items have VAT and some do not, you probably need a ProductCategory
table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format = Percent.)
Now you can tell what VAT applies to a product by examining what category it
belongs to.

It is possible that the VAT rate will change over the years, or even that
some categories of product will have the VAT status changed. Governments do
these kinds of things, regardless of their promises. You therefore need to
store the VAT rate in each row of each order, so your existing records will
still be correct if these things change.

You probably have an Order table, with an OrderDetail to handle the row
items in the order. If some items have VAT and some do not, the OrderDetail
table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a subform
for OrderDetail. The subform has a combo for selecting the product. The
product combo's RowSource is a query that uses both the Product and
ProductCategory tables, so that the columns of the combo contains its
current price ex-VAT (from the Product table) and the VatRate (from the
ProductCategory table.) You can therefore use the AfterUpdate event of the
combo to assign the PriceEachEx and VatRate to the fields in the subform.

The subform will be based on a query that has some calculated fields, such
as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is based on,
you can sum these fields in the Form Footer section of your continuous
subform to get the order total.

 

[Keith Wilby]

I have created a database which has a table called tblVat which has the VAT
rate of 17.5% stored. I use this table to calculate vat etc. What I also
need to incorporate is Zero Rated VAT but I am struggling to understand and
figure out how I can incorporate this in to my database for example if I am
selling an item and there is no vat on the item how do I them configure the
database not to calulate the vat and to also rememeber that the item has no
VAT added when I am not storing the VAT total?

Any suggestions much appreciated.

Kind Regards

S

I'd suggest a combo box with tblVAT as it's row source. You could then put
whatever values of tax you wanted to in the table, in this example, 0 and
17.5. You can then use whichever rate is chosen as the multiplier and also
as the indicator of what rate has been applied. That seems too simple, have
I missed something?

Regards,
Keith.
www.keithwilby.com

 

[SG]

Allen,

Sorry I should ahve given more detailed information.

All Items are Vatable unless they are for example on military deployment
from overseas. So it wouldn't be until the item is sold I would want to zero
rate the vat.

I use the following to calculate the total including vat
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))

I also use the same to produce the reports.

Any further suggestions would be much appreciated.

Kind Regards

S

If some items have VAT and some do not, you probably need a
ProductCategory table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format = Percent.)
Now you can tell what VAT applies to a product by examining what category
it belongs to.

It is possible that the VAT rate will change over the years, or even that
some categories of product will have the VAT status changed. Governments
do these kinds of things, regardless of their promises. You therefore need
to store the VAT rate in each row of each order, so your existing records
will still be correct if these things change.

You probably have an Order table, with an OrderDetail to handle the row
items in the order. If some items have VAT and some do not, the
OrderDetail table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a subform
for OrderDetail. The subform has a combo for selecting the product. The
product combo's RowSource is a query that uses both the Product and
ProductCategory tables, so that the columns of the combo contains its
current price ex-VAT (from the Product table) and the VatRate (from the
ProductCategory table.) You can therefore use the AfterUpdate event of the
combo to assign the PriceEachEx and VatRate to the fields in the subform.

The subform will be based on a query that has some calculated fields, such
as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is based on,
you can sum these fields in the Form Footer section of your continuous
subform to get the order total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have created a database which has a table called tblVat which has the
VAT rate of 17.5% stored. I use this table to calculate vat etc. What I
also need to incorporate is Zero Rated VAT but I am struggling to
understand and figure out how I can incorporate this in to my database for
example if I am selling an item and there is no vat on the item how do I
them configure the database not to calulate the vat and to also rememeber
that the item has no VAT added when I am not storing the VAT total?

Any suggestions much appreciated.

Kind Regards

S

 

[Allen Browne]

Okay, so you don't need ProductCategory.VatRate.

Presumably this means the *whole* order either has VAT of 17.5% or 0%, i.e.
there won't ever be an order where some items have VAT and others don't. If
so, you need a VatRate field in your Order table (not in OrderDetail as
suggested previously.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Sorry I should ahve given more detailed information.

All Items are Vatable unless they are for example on military deployment
from overseas. So it wouldn't be until the item is sold I would want to
zero rate the vat.

I use the following to calculate the total including vat
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))

I also use the same to produce the reports.

Any further suggestions would be much appreciated.

Kind Regards

S

If some items have VAT and some do not, you probably need a
ProductCategory table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format =
Percent.)
Now you can tell what VAT applies to a product by examining what category
it belongs to.

It is possible that the VAT rate will change over the years, or even that
some categories of product will have the VAT status changed. Governments
do these kinds of things, regardless of their promises. You therefore
need to store the VAT rate in each row of each order, so your existing
records will still be correct if these things change.

You probably have an Order table, with an OrderDetail to handle the row
items in the order. If some items have VAT and some do not, the
OrderDetail table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a subform
for OrderDetail. The subform has a combo for selecting the product. The
product combo's RowSource is a query that uses both the Product and
ProductCategory tables, so that the columns of the combo contains its
current price ex-VAT (from the Product table) and the VatRate (from the
ProductCategory table.) You can therefore use the AfterUpdate event of
the combo to assign the PriceEachEx and VatRate to the fields in the
subform.

The subform will be based on a query that has some calculated fields,
such as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is based on,
you can sum these fields in the Form Footer section of your continuous
subform to get the order total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have created a database which has a table called tblVat which has the
VAT rate of 17.5% stored. I use this table to calculate vat etc. What I
also need to incorporate is Zero Rated VAT but I am struggling to
understand and figure out how I can incorporate this in to my database
for example if I am selling an item and there is no vat on the item how
do I them configure the database not to calulate the vat and to also
rememeber that the item has no VAT added when I am not storing the VAT
total?

Any suggestions much appreciated.

Kind Regards

S

 

[SG]

Okay,

I have added a field in tblinvoice called VatRate and added a combo box on
to frmsales. If I have a field called total including vat with the following
control source
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))
- how would I then make the database check the VatRate combo box and
calculate the vat dependant on the value selected here?

Many Thanks for you help!!

Okay, so you don't need ProductCategory.VatRate.

Presumably this means the *whole* order either has VAT of 17.5% or 0%,
i.e. there won't ever be an order where some items have VAT and others
don't. If so, you need a VatRate field in your Order table (not in
OrderDetail as suggested previously.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Sorry I should ahve given more detailed information.

All Items are Vatable unless they are for example on military deployment
from overseas. So it wouldn't be until the item is sold I would want to
zero rate the vat.

I use the following to calculate the total including vat
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))

I also use the same to produce the reports.

Any further suggestions would be much appreciated.

Kind Regards

S

If some items have VAT and some do not, you probably need a
ProductCategory table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format =
Percent.)
Now you can tell what VAT applies to a product by examining what
category it belongs to.

It is possible that the VAT rate will change over the years, or even
that some categories of product will have the VAT status changed.
Governments do these kinds of things, regardless of their promises. You
therefore need to store the VAT rate in each row of each order, so your
existing records will still be correct if these things change.

You probably have an Order table, with an OrderDetail to handle the row
items in the order. If some items have VAT and some do not, the
OrderDetail table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a
subform for OrderDetail. The subform has a combo for selecting the
product. The product combo's RowSource is a query that uses both the
Product and ProductCategory tables, so that the columns of the combo
contains its current price ex-VAT (from the Product table) and the
VatRate (from the ProductCategory table.) You can therefore use the
AfterUpdate event of the combo to assign the PriceEachEx and VatRate to
the fields in the subform.

The subform will be based on a query that has some calculated fields,
such as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is based
on, you can sum these fields in the Form Footer section of your
continuous subform to get the order total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have created a database which has a table called tblVat which has the
VAT rate of 17.5% stored. I use this table to calculate vat etc. What I
also need to incorporate is Zero Rated VAT but I am struggling to
understand and figure out how I can incorporate this in to my database
for example if I am selling an item and there is no vat on the item how
do I them configure the database not to calulate the vat and to also
rememeber that the item has no VAT added when I am not storing the VAT
total?

Any suggestions much appreciated.

Kind Regards

S

 

[Allen Browne]

You don't store the total amount.
You get Access to calculate it for you.
The methodology depends on your context.

In a form, you have a subform for the InvoiceDetail.
In the Form Footer section of the subform, you have a text box with Control
Source of:
=Sum([Amount])
On the main form, you can now show the Tax to add as:
=[InvoiceDetail].[Form].[txtTotalAmount] * [VatRate]

In a report, you do basically the same thing in the Invoice ID footer
section.

In a query, if you wanted to show the invoice totals (rather than details),
you would create a Totals query (depress the Total button on the toolbar in
query design.) In the Total row under:
InvoiceID VatRate Amount
Group By Group By Sum
Then in the next column type this calculate field:
Tax: CCur(Nz(Round([SumOfAmount] * [VatRate],2),0))
and in the total row under this, choose
Expression

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay,

I have added a field in tblinvoice called VatRate and added a combo box on
to frmsales. If I have a field called total including vat with the
following control source
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))
- how would I then make the database check the VatRate combo box and
calculate the vat dependant on the value selected here?

Many Thanks for you help!!

Okay, so you don't need ProductCategory.VatRate.

Presumably this means the *whole* order either has VAT of 17.5% or 0%,
i.e. there won't ever be an order where some items have VAT and others
don't. If so, you need a VatRate field in your Order table (not in
OrderDetail as suggested previously.)

Allen,

Sorry I should ahve given more detailed information.

All Items are Vatable unless they are for example on military deployment
from overseas. So it wouldn't be until the item is sold I would want to
zero rate the vat.

I use the following to calculate the total including vat
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))

I also use the same to produce the reports.

Any further suggestions would be much appreciated.

Kind Regards

S


If some items have VAT and some do not, you probably need a
ProductCategory table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format =
Percent.)
Now you can tell what VAT applies to a product by examining what
category it belongs to.

It is possible that the VAT rate will change over the years, or even
that some categories of product will have the VAT status changed.
Governments do these kinds of things, regardless of their promises. You
therefore need to store the VAT rate in each row of each order, so your
existing records will still be correct if these things change.

You probably have an Order table, with an OrderDetail to handle the row
items in the order. If some items have VAT and some do not, the
OrderDetail table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a
subform for OrderDetail. The subform has a combo for selecting the
product. The product combo's RowSource is a query that uses both the
Product and ProductCategory tables, so that the columns of the combo
contains its current price ex-VAT (from the Product table) and the
VatRate (from the ProductCategory table.) You can therefore use the
AfterUpdate event of the combo to assign the PriceEachEx and VatRate to
the fields in the subform.

The subform will be based on a query that has some calculated fields,
such as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is based
on, you can sum these fields in the Form Footer section of your
continuous subform to get the order total.

I have created a database which has a table called tblVat which has the
VAT rate of 17.5% stored. I use this table to calculate vat etc. What I
also need to incorporate is Zero Rated VAT but I am struggling to
understand and figure out how I can incorporate this in to my database
for example if I am selling an item and there is no vat on the item how
do I them configure the database not to calulate the vat and to also
rememeber that the item has no VAT added when I am not storing the VAT
total?

 

[SG]

I have added a text control and added to following as the control source
=InvoiceSubForm.Form!OrderSubtotal*[VAT Rate] but I get 0 all of the time?

Let me explain the setup I have a little better

I have the following tables

tblvat

ID
Description
VAT

tblInvoice
ID
VAT (This does not store the vat total only the code)


I have a form called frmsales on this form I have added a combo box with the
control source of VAT but the Row Source is set to tblvat

I have a field called Total with the following control source
=InvoiceSubForm.Form!OrderSubtotal

What I need to do is have my database deduct the VAT from the field Total
depending what the user has selected in the VAT Rate Combo box and display
the Total Vat amount in the Text control VAT Total.

Hope this makes more sense.

You don't store the total amount.
You get Access to calculate it for you.
The methodology depends on your context.

In a form, you have a subform for the InvoiceDetail.
In the Form Footer section of the subform, you have a text box with
Control Source of:
=Sum([Amount])
On the main form, you can now show the Tax to add as:
=[InvoiceDetail].[Form].[txtTotalAmount] * [VatRate]

In a report, you do basically the same thing in the Invoice ID footer
section.

In a query, if you wanted to show the invoice totals (rather than
details), you would create a Totals query (depress the Total button on the
toolbar in query design.) In the Total row under:
InvoiceID VatRate Amount
Group By Group By Sum
Then in the next column type this calculate field:
Tax: CCur(Nz(Round([SumOfAmount] * [VatRate],2),0))
and in the total row under this, choose
Expression

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay,

I have added a field in tblinvoice called VatRate and added a combo box
on to frmsales. If I have a field called total including vat with the
following control source
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))
- how would I then make the database check the VatRate combo box and
calculate the vat dependant on the value selected here?

Many Thanks for you help!!

Okay, so you don't need ProductCategory.VatRate.

Presumably this means the *whole* order either has VAT of 17.5% or 0%,
i.e. there won't ever be an order where some items have VAT and others
don't. If so, you need a VatRate field in your Order table (not in
OrderDetail as suggested previously.)

Allen,

Sorry I should ahve given more detailed information.

All Items are Vatable unless they are for example on military
deployment from overseas. So it wouldn't be until the item is sold I
would want to zero rate the vat.

I use the following to calculate the total including vat
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))

I also use the same to produce the reports.

Any further suggestions would be much appreciated.

Kind Regards

S


If some items have VAT and some do not, you probably need a
ProductCategory table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format =
Percent.)
Now you can tell what VAT applies to a product by examining what
category it belongs to.

It is possible that the VAT rate will change over the years, or even
that some categories of product will have the VAT status changed.
Governments do these kinds of things, regardless of their promises.
You therefore need to store the VAT rate in each row of each order, so
your existing records will still be correct if these things change.

You probably have an Order table, with an OrderDetail to handle the
row items in the order. If some items have VAT and some do not, the
OrderDetail table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a
subform for OrderDetail. The subform has a combo for selecting the
product. The product combo's RowSource is a query that uses both the
Product and ProductCategory tables, so that the columns of the combo
contains its current price ex-VAT (from the Product table) and the
VatRate (from the ProductCategory table.) You can therefore use the
AfterUpdate event of the combo to assign the PriceEachEx and VatRate
to the fields in the subform.

The subform will be based on a query that has some calculated fields,
such as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is based
on, you can sum these fields in the Form Footer section of your
continuous subform to get the order total.

I have created a database which has a table called tblVat which has
the VAT rate of 17.5% stored. I use this table to calculate vat etc.
What I also need to incorporate is Zero Rated VAT but I am struggling
to understand and figure out how I can incorporate this in to my
database for example if I am selling an item and there is no vat on
the item how do I them configure the database not to calulate the vat
and to also rememeber that the item has no VAT added when I am not
storing the VAT total?

 

[Allen Browne]

Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the 17.5% found
in one of the other columns? If it is in the 3rd column, then the expression
you need in the ControlSource of your text box would be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have added a text control and added to following as the control source
=InvoiceSubForm.Form!OrderSubtotal*[VAT Rate] but I get 0 all of the time?

Let me explain the setup I have a little better

I have the following tables

tblvat

ID
Description
VAT

tblInvoice
ID
VAT (This does not store the vat total only the code)


I have a form called frmsales on this form I have added a combo box with
the control source of VAT but the Row Source is set to tblvat

I have a field called Total with the following control source
=InvoiceSubForm.Form!OrderSubtotal

What I need to do is have my database deduct the VAT from the field Total
depending what the user has selected in the VAT Rate Combo box and display
the Total Vat amount in the Text control VAT Total.

Hope this makes more sense.

You don't store the total amount.
You get Access to calculate it for you.
The methodology depends on your context.

In a form, you have a subform for the InvoiceDetail.
In the Form Footer section of the subform, you have a text box with
Control Source of:
=Sum([Amount])
On the main form, you can now show the Tax to add as:
=[InvoiceDetail].[Form].[txtTotalAmount] * [VatRate]

In a report, you do basically the same thing in the Invoice ID footer
section.

In a query, if you wanted to show the invoice totals (rather than
details), you would create a Totals query (depress the Total button on
the toolbar in query design.) In the Total row under:
InvoiceID VatRate Amount
Group By Group By Sum
Then in the next column type this calculate field:
Tax: CCur(Nz(Round([SumOfAmount] * [VatRate],2),0))
and in the total row under this, choose
Expression

Okay,

I have added a field in tblinvoice called VatRate and added a combo box
on to frmsales. If I have a field called total including vat with the
following control source
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))
- how would I then make the database check the VatRate combo box and
calculate the vat dependant on the value selected here?

Many Thanks for you help!!


Okay, so you don't need ProductCategory.VatRate.

Presumably this means the *whole* order either has VAT of 17.5% or 0%,
i.e. there won't ever be an order where some items have VAT and others
don't. If so, you need a VatRate field in your Order table (not in
OrderDetail as suggested previously.)

Allen,

Sorry I should ahve given more detailed information.

All Items are Vatable unless they are for example on military
deployment from overseas. So it wouldn't be until the item is sold I
would want to zero rate the vat.

I use the following to calculate the total including vat
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))

I also use the same to produce the reports.

Any further suggestions would be much appreciated.

Kind Regards

S


If some items have VAT and some do not, you probably need a
ProductCategory table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format =
Percent.)
Now you can tell what VAT applies to a product by examining what
category it belongs to.

It is possible that the VAT rate will change over the years, or even
that some categories of product will have the VAT status changed.
Governments do these kinds of things, regardless of their promises.
You therefore need to store the VAT rate in each row of each order,
so your existing records will still be correct if these things
change.

You probably have an Order table, with an OrderDetail to handle the
row items in the order. If some items have VAT and some do not, the
OrderDetail table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a
subform for OrderDetail. The subform has a combo for selecting the
product. The product combo's RowSource is a query that uses both the
Product and ProductCategory tables, so that the columns of the combo
contains its current price ex-VAT (from the Product table) and the
VatRate (from the ProductCategory table.) You can therefore use the
AfterUpdate event of the combo to assign the PriceEachEx and VatRate
to the fields in the subform.

The subform will be based on a query that has some calculated fields,
such as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is based
on, you can sum these fields in the Form Footer section of your
continuous subform to get the order total.

I have created a database which has a table called tblVat which has
the VAT rate of 17.5% stored. I use this table to calculate vat etc.
What I also need to incorporate is Zero Rated VAT but I am struggling
to understand and figure out how I can incorporate this in to my
database for example if I am selling an item and there is no vat on
the item how do I them configure the database not to calulate the vat
and to also rememeber that the item has no VAT added when I am not
storing the VAT total?

 

[SG]

Ok if I do the first part I get the subtotal excluding vat but as soon as I
add - [InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2)) as
the control source I get #Error?


Any ideas?

Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the 17.5%
found in one of the other columns? If it is in the 3rd column, then the
expression you need in the ControlSource of your text box would be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have added a text control and added to following as the control source
=InvoiceSubForm.Form!OrderSubtotal*[VAT Rate] but I get 0 all of the time?

Let me explain the setup I have a little better

I have the following tables

tblvat

ID
Description
VAT

tblInvoice
ID
VAT (This does not store the vat total only the code)


I have a form called frmsales on this form I have added a combo box with
the control source of VAT but the Row Source is set to tblvat

I have a field called Total with the following control source
=InvoiceSubForm.Form!OrderSubtotal

What I need to do is have my database deduct the VAT from the field Total
depending what the user has selected in the VAT Rate Combo box and
display the Total Vat amount in the Text control VAT Total.

Hope this makes more sense.

You don't store the total amount.
You get Access to calculate it for you.
The methodology depends on your context.

In a form, you have a subform for the InvoiceDetail.
In the Form Footer section of the subform, you have a text box with
Control Source of:
=Sum([Amount])
On the main form, you can now show the Tax to add as:
=[InvoiceDetail].[Form].[txtTotalAmount] * [VatRate]

In a report, you do basically the same thing in the Invoice ID footer
section.

In a query, if you wanted to show the invoice totals (rather than
details), you would create a Totals query (depress the Total button on
the toolbar in query design.) In the Total row under:
InvoiceID VatRate Amount
Group By Group By Sum
Then in the next column type this calculate field:
Tax: CCur(Nz(Round([SumOfAmount] * [VatRate],2),0))
and in the total row under this, choose
Expression

Okay,

I have added a field in tblinvoice called VatRate and added a combo box
on to frmsales. If I have a field called total including vat with the
following control source
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))
- how would I then make the database check the VatRate combo box and
calculate the vat dependant on the value selected here?

Many Thanks for you help!!


Okay, so you don't need ProductCategory.VatRate.

Presumably this means the *whole* order either has VAT of 17.5% or 0%,
i.e. there won't ever be an order where some items have VAT and others
don't. If so, you need a VatRate field in your Order table (not in
OrderDetail as suggested previously.)

Allen,

Sorry I should ahve given more detailed information.

All Items are Vatable unless they are for example on military
deployment from overseas. So it wouldn't be until the item is sold I
would want to zero rate the vat.

I use the following to calculate the total including vat
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))

I also use the same to produce the reports.

Any further suggestions would be much appreciated.

Kind Regards

S


If some items have VAT and some do not, you probably need a
ProductCategory table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format =
Percent.)
Now you can tell what VAT applies to a product by examining what
category it belongs to.

It is possible that the VAT rate will change over the years, or even
that some categories of product will have the VAT status changed.
Governments do these kinds of things, regardless of their promises.
You therefore need to store the VAT rate in each row of each order,
so your existing records will still be correct if these things
change.

You probably have an Order table, with an OrderDetail to handle the
row items in the order. If some items have VAT and some do not, the
OrderDetail table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a
subform for OrderDetail. The subform has a combo for selecting the
product. The product combo's RowSource is a query that uses both the
Product and ProductCategory tables, so that the columns of the combo
contains its current price ex-VAT (from the Product table) and the
VatRate (from the ProductCategory table.) You can therefore use the
AfterUpdate event of the combo to assign the PriceEachEx and VatRate
to the fields in the subform.

The subform will be based on a query that has some calculated
fields, such as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is
based on, you can sum these fields in the Form Footer section of
your continuous subform to get the order total.

I have created a database which has a table called tblVat which has
the VAT rate of 17.5% stored. I use this table to calculate vat etc.
What I also need to incorporate is Zero Rated VAT but I am
struggling to understand and figure out how I can incorporate this
in to my database for example if I am selling an item and there is
no vat on the item how do I them configure the database not to
calulate the vat and to also rememeber that the item has no VAT
added when I am not storing the VAT total?

 

[Allen Browne]

Perhaps Access is not understanding the column correctly.

To break the problem down, open the form, and add a text box with
ControlSource of:
=Val(Nz([VatID].[Column](2), "0")
If that expression works, you can then use it in your main expression.

If not, and the column displays a % at the end, you may need to get the
field to show in the column as a double (i.e. without the percent.) The
RowSource would be:
SELECT VatID, VatRate, CDbl(Nz([VatRate], 0.175)) AS VatAsDouble FROM
....
Once you have it working, you can hide the 3rd column, but Access should be
able to recognise it as a number.

Note that Column() is zero-based: the first column is zero, the second is
one, and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ok if I do the first part I get the subtotal excluding vat but as soon as
I add - [InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))
as the control source I get #Error?


Any ideas?

Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the 17.5%
found in one of the other columns? If it is in the 3rd column, then the
expression you need in the ControlSource of your text box would be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have added a text control and added to following as the control source
=InvoiceSubForm.Form!OrderSubtotal*[VAT Rate] but I get 0 all of the
time?

Let me explain the setup I have a little better

I have the following tables

tblvat

ID
Description
VAT

tblInvoice
ID
VAT (This does not store the vat total only the code)


I have a form called frmsales on this form I have added a combo box with
the control source of VAT but the Row Source is set to tblvat

I have a field called Total with the following control source
=InvoiceSubForm.Form!OrderSubtotal

What I need to do is have my database deduct the VAT from the field
Total depending what the user has selected in the VAT Rate Combo box and
display the Total Vat amount in the Text control VAT Total.

Hope this makes more sense.

You don't store the total amount.
You get Access to calculate it for you.
The methodology depends on your context.

In a form, you have a subform for the InvoiceDetail.
In the Form Footer section of the subform, you have a text box with
Control Source of:
=Sum([Amount])
On the main form, you can now show the Tax to add as:
=[InvoiceDetail].[Form].[txtTotalAmount] * [VatRate]

In a report, you do basically the same thing in the Invoice ID footer
section.

In a query, if you wanted to show the invoice totals (rather than
details), you would create a Totals query (depress the Total button on
the toolbar in query design.) In the Total row under:
InvoiceID VatRate Amount
Group By Group By Sum
Then in the next column type this calculate field:
Tax: CCur(Nz(Round([SumOfAmount] * [VatRate],2),0))
and in the total row under this, choose
Expression

Okay,

I have added a field in tblinvoice called VatRate and added a combo
box on to frmsales. If I have a field called total including vat with
the following control source
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))
- how would I then make the database check the VatRate combo box and
calculate the vat dependant on the value selected here?

Many Thanks for you help!!


Okay, so you don't need ProductCategory.VatRate.

Presumably this means the *whole* order either has VAT of 17.5% or
0%, i.e. there won't ever be an order where some items have VAT and
others don't. If so, you need a VatRate field in your Order table
(not in OrderDetail as suggested previously.)

Allen,

Sorry I should ahve given more detailed information.

All Items are Vatable unless they are for example on military
deployment from overseas. So it wouldn't be until the item is sold I
would want to zero rate the vat.

I use the following to calculate the total including vat
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))

I also use the same to produce the reports.

Any further suggestions would be much appreciated.

Kind Regards

S


If some items have VAT and some do not, you probably need a
ProductCategory table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format =
Percent.)
Now you can tell what VAT applies to a product by examining what
category it belongs to.

It is possible that the VAT rate will change over the years, or
even that some categories of product will have the VAT status
changed. Governments do these kinds of things, regardless of their
promises. You therefore need to store the VAT rate in each row of
each order, so your existing records will still be correct if these
things change.

You probably have an Order table, with an OrderDetail to handle the
row items in the order. If some items have VAT and some do not, the
OrderDetail table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a
subform for OrderDetail. The subform has a combo for selecting the
product. The product combo's RowSource is a query that uses both
the Product and ProductCategory tables, so that the columns of the
combo contains its current price ex-VAT (from the Product table)
and the VatRate (from the ProductCategory table.) You can therefore
use the AfterUpdate event of the combo to assign the PriceEachEx
and VatRate to the fields in the subform.

The subform will be based on a query that has some calculated
fields, such as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is
based on, you can sum these fields in the Form Footer section of
your continuous subform to get the order total.

I have created a database which has a table called tblVat which has
the VAT rate of 17.5% stored. I use this table to calculate vat
etc. What I also need to incorporate is Zero Rated VAT but I am
struggling to understand and figure out how I can incorporate this
in to my database for example if I am selling an item and there is
no vat on the item how do I them configure the database not to
calulate the vat and to also rememeber that the item has no VAT
added when I am not storing the VAT total?

 

[SG]

Allen,

Sorry for the delay in responding! I now have this working but I have 2
problems, 1 being that using the following
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1))))) when I
set the vat rate to 0 I get the following displayed in the Sub Total
Excluding Vat #Div/0! and #Error in the VAT field which has the following
set as the control source =[subtotal]-[sub total] how do I stop these
messages being displayed when I select the 0 Rate VAT.

The Second problem I have is prior to making the above changes I had a VAT
report which showed the VAT for items sold this was simple as I only had 1
VAT Rate Setup but now I have 2 VAT Rates 1.175 and 0.

I'm at a bit of a loss how to now go about doing the report??!!

Any advice would be much appreciated!

Thank you in advance.

S

Perhaps Access is not understanding the column correctly.

To break the problem down, open the form, and add a text box with
ControlSource of:
=Val(Nz([VatID].[Column](2), "0")
If that expression works, you can then use it in your main expression.

If not, and the column displays a % at the end, you may need to get the
field to show in the column as a double (i.e. without the percent.) The
RowSource would be:
SELECT VatID, VatRate, CDbl(Nz([VatRate], 0.175)) AS VatAsDouble FROM
...
Once you have it working, you can hide the 3rd column, but Access should
be able to recognise it as a number.

Note that Column() is zero-based: the first column is zero, the second is
one, and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ok if I do the first part I get the subtotal excluding vat but as soon as
I add - [InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))
as the control source I get #Error?


Any ideas?

Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the 17.5%
found in one of the other columns? If it is in the 3rd column, then the
expression you need in the ControlSource of your text box would be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have added a text control and added to following as the control source
=InvoiceSubForm.Form!OrderSubtotal*[VAT Rate] but I get 0 all of the
time?

Let me explain the setup I have a little better

I have the following tables

tblvat

ID
Description
VAT

tblInvoice
ID
VAT (This does not store the vat total only the code)


I have a form called frmsales on this form I have added a combo box
with the control source of VAT but the Row Source is set to tblvat

I have a field called Total with the following control source
=InvoiceSubForm.Form!OrderSubtotal

What I need to do is have my database deduct the VAT from the field
Total depending what the user has selected in the VAT Rate Combo box
and display the Total Vat amount in the Text control VAT Total.

Hope this makes more sense.

You don't store the total amount.
You get Access to calculate it for you.
The methodology depends on your context.

In a form, you have a subform for the InvoiceDetail.
In the Form Footer section of the subform, you have a text box with
Control Source of:
=Sum([Amount])
On the main form, you can now show the Tax to add as:
=[InvoiceDetail].[Form].[txtTotalAmount] * [VatRate]

In a report, you do basically the same thing in the Invoice ID footer
section.

In a query, if you wanted to show the invoice totals (rather than
details), you would create a Totals query (depress the Total button on
the toolbar in query design.) In the Total row under:
InvoiceID VatRate Amount
Group By Group By Sum
Then in the next column type this calculate field:
Tax: CCur(Nz(Round([SumOfAmount] * [VatRate],2),0))
and in the total row under this, choose
Expression

Okay,

I have added a field in tblinvoice called VatRate and added a combo
box on to frmsales. If I have a field called total including vat with
the following control source
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))
- how would I then make the database check the VatRate combo box and
calculate the vat dependant on the value selected here?

Many Thanks for you help!!


Okay, so you don't need ProductCategory.VatRate.

Presumably this means the *whole* order either has VAT of 17.5% or
0%, i.e. there won't ever be an order where some items have VAT and
others don't. If so, you need a VatRate field in your Order table
(not in OrderDetail as suggested previously.)

Allen,

Sorry I should ahve given more detailed information.

All Items are Vatable unless they are for example on military
deployment from overseas. So it wouldn't be until the item is sold
I would want to zero rate the vat.

I use the following to calculate the total including vat
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]*DLookUp("Vat","tblvat"),2)))

I also use the same to produce the reports.

Any further suggestions would be much appreciated.

Kind Regards

S


If some items have VAT and some do not, you probably need a
ProductCategory table.

This table would have fields:
ProductCategoryID primary key
VatRate Number (size = Double, format =
Percent.)
Now you can tell what VAT applies to a product by examining what
category it belongs to.

It is possible that the VAT rate will change over the years, or
even that some categories of product will have the VAT status
changed. Governments do these kinds of things, regardless of their
promises. You therefore need to store the VAT rate in each row of
each order, so your existing records will still be correct if
these things change.

You probably have an Order table, with an OrderDetail to handle
the row items in the order. If some items have VAT and some do
not, the OrderDetail table needs to have a VatRate column.

Typically this is interfaced by a main form for the order, with a
subform for OrderDetail. The subform has a combo for selecting the
product. The product combo's RowSource is a query that uses both
the Product and ProductCategory tables, so that the columns of the
combo contains its current price ex-VAT (from the Product table)
and the VatRate (from the ProductCategory table.) You can
therefore use the AfterUpdate event of the combo to assign the
PriceEachEx and VatRate to the fields in the subform.

The subform will be based on a query that has some calculated
fields, such as:
AmountEx: CCur(Nz([Quantity] * [PriceEachEx],0))
VatAmount: CCur(Round(Nz([VatRate],0) * [AmountEx], 2))
Since these calculated fields exist in the query the subform is
based on, you can sum these fields in the Form Footer section of
your continuous subform to get the order total.

I have created a database which has a table called tblVat which
has the VAT rate of 17.5% stored. I use this table to calculate
vat etc. What I also need to incorporate is Zero Rated VAT but I
am struggling to understand and figure out how I can incorporate
this in to my database for example if I am selling an item and
there is no vat on the item how do I them configure the database
not to calulate the vat and to also rememeber that the item has no
VAT added when I am not storing the VAT total?

 

[Allen Browne]

Use an IIf() expression inside your IIf() expression, to test for zero, and
assign zero if that is the case.

(BTW, the CCur() is doing little in that expression since it is inside
another IIf() with nulls, and Access could understand it.)

Your report will be based on a query. The query will use your VAT table as
well as your other table. You can therefore get the VatRate from the field
in the VAT table, and add it to the query output.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Sorry for the delay in responding! I now have this working but I have 2
problems, 1 being that using the following
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1))))) when
I set the vat rate to 0 I get the following displayed in the Sub Total
Excluding Vat #Div/0! and #Error in the VAT field which has the following
set as the control source =[subtotal]-[sub total] how do I stop these
messages being displayed when I select the 0 Rate VAT.

The Second problem I have is prior to making the above changes I had a VAT
report which showed the VAT for items sold this was simple as I only had 1
VAT Rate Setup but now I have 2 VAT Rates 1.175 and 0.

I'm at a bit of a loss how to now go about doing the report??!!

Any advice would be much appreciated!

Thank you in advance.

S

Perhaps Access is not understanding the column correctly.

To break the problem down, open the form, and add a text box with
ControlSource of:
=Val(Nz([VatID].[Column](2), "0")
If that expression works, you can then use it in your main expression.

If not, and the column displays a % at the end, you may need to get the
field to show in the column as a double (i.e. without the percent.) The
RowSource would be:
SELECT VatID, VatRate, CDbl(Nz([VatRate], 0.175)) AS VatAsDouble FROM
...
Once you have it working, you can hide the 3rd column, but Access should
be able to recognise it as a number.

Note that Column() is zero-based: the first column is zero, the second is
one, and so on.

Ok if I do the first part I get the subtotal excluding vat but as soon
as I add - [InvoiceSubForm].[Form]![OrderSubtotal] / (1 +
VatID.Column(2)) as the control source I get #Error?


Any ideas?



Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the 17.5%
found in one of the other columns? If it is in the 3rd column, then the
expression you need in the ControlSource of your text box would be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

 

[SG]

Allen,

Thank you for the assistance, how would I add the IIf() in to
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1))))) to
check for the 0

Many Thanks

S

Use an IIf() expression inside your IIf() expression, to test for zero,
and assign zero if that is the case.

(BTW, the CCur() is doing little in that expression since it is inside
another IIf() with nulls, and Access could understand it.)

Your report will be based on a query. The query will use your VAT table as
well as your other table. You can therefore get the VatRate from the field
in the VAT table, and add it to the query output.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Sorry for the delay in responding! I now have this working but I have 2
problems, 1 being that using the following
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1)))))
when I set the vat rate to 0 I get the following displayed in the Sub
Total Excluding Vat #Div/0! and #Error in the VAT field which has the
following set as the control source =[subtotal]-[sub total] how do I stop
these messages being displayed when I select the 0 Rate VAT.

The Second problem I have is prior to making the above changes I had a
VAT report which showed the VAT for items sold this was simple as I only
had 1 VAT Rate Setup but now I have 2 VAT Rates 1.175 and 0.

I'm at a bit of a loss how to now go about doing the report??!!

Any advice would be much appreciated!

Thank you in advance.

S

Perhaps Access is not understanding the column correctly.

To break the problem down, open the form, and add a text box with
ControlSource of:
=Val(Nz([VatID].[Column](2), "0")
If that expression works, you can then use it in your main expression.

If not, and the column displays a % at the end, you may need to get the
field to show in the column as a double (i.e. without the percent.) The
RowSource would be:
SELECT VatID, VatRate, CDbl(Nz([VatRate], 0.175)) AS VatAsDouble FROM
...
Once you have it working, you can hide the 3rd column, but Access should
be able to recognise it as a number.

Note that Column() is zero-based: the first column is zero, the second
is one, and so on.

Ok if I do the first part I get the subtotal excluding vat but as soon
as I add - [InvoiceSubForm].[Form]![OrderSubtotal] / (1 +
VatID.Column(2)) as the control source I get #Error?


Any ideas?



Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the 17.5%
found in one of the other columns? If it is in the 3rd column, then
the expression you need in the ControlSource of your text box would
be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

 

[SG]

Allen,

I have setup the query based on the invoice table and invoice details table
as shown here
SELECT tblInvoice.InvoiceID AS tblInvoice_InvoiceID, tblInvoice.InvoiceDate,
tblInvoice.VAT, tblInvoiceDetail.InvoiceDetailID, tblInvoiceDetail.InvoiceID
AS tblInvoiceDetail_InvoiceID, tblInvoiceDetail.RealisedPrice,
Sum(CCur(Nz(Round([SumOfRealisedAmount]*[Vat],2),0))) AS Tax
FROM tblInvoice INNER JOIN tblInvoiceDetail ON tblInvoice.InvoiceID =
tblInvoiceDetail.InvoiceID
GROUP BY tblInvoice.InvoiceID, tblInvoice.InvoiceDate, tblInvoice.VAT,
tblInvoiceDetail.InvoiceDetailID, tblInvoiceDetail.InvoiceID,
tblInvoiceDetail.RealisedPrice;

I am still having a problem trying to calculate the vat on the report
Sum(CCur(Nz(Round([SumOfRealisedAmount]*[Vat],2),0))) AS Tax

tblInvoice.VAT contains the selected vat rate for the invoice and
tblInvoiceDetail.RealisedPrice contains the invoice amount.

Your help would be much appreciated.

Thanks S

Use an IIf() expression inside your IIf() expression, to test for zero,
and assign zero if that is the case.

(BTW, the CCur() is doing little in that expression since it is inside
another IIf() with nulls, and Access could understand it.)

Your report will be based on a query. The query will use your VAT table as
well as your other table. You can therefore get the VatRate from the field
in the VAT table, and add it to the query output.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Sorry for the delay in responding! I now have this working but I have 2
problems, 1 being that using the following
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1)))))
when I set the vat rate to 0 I get the following displayed in the Sub
Total Excluding Vat #Div/0! and #Error in the VAT field which has the
following set as the control source =[subtotal]-[sub total] how do I stop
these messages being displayed when I select the 0 Rate VAT.

The Second problem I have is prior to making the above changes I had a
VAT report which showed the VAT for items sold this was simple as I only
had 1 VAT Rate Setup but now I have 2 VAT Rates 1.175 and 0.

I'm at a bit of a loss how to now go about doing the report??!!

Any advice would be much appreciated!

Thank you in advance.

S

Perhaps Access is not understanding the column correctly.

To break the problem down, open the form, and add a text box with
ControlSource of:
=Val(Nz([VatID].[Column](2), "0")
If that expression works, you can then use it in your main expression.

If not, and the column displays a % at the end, you may need to get the
field to show in the column as a double (i.e. without the percent.) The
RowSource would be:
SELECT VatID, VatRate, CDbl(Nz([VatRate], 0.175)) AS VatAsDouble FROM
...
Once you have it working, you can hide the 3rd column, but Access should
be able to recognise it as a number.

Note that Column() is zero-based: the first column is zero, the second
is one, and so on.

Ok if I do the first part I get the subtotal excluding vat but as soon
as I add - [InvoiceSubForm].[Form]![OrderSubtotal] / (1 +
VatID.Column(2)) as the control source I get #Error?


Any ideas?



Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the 17.5%
found in one of the other columns? If it is in the 3rd column, then
the expression you need in the ControlSource of your text box would
be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

 

[Allen Browne]

You have the calculated "Tax" field in your query.
Good.

Now in the report, you can just use:
=Sum([Tax])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have setup the query based on the invoice table and invoice details
table as shown here
SELECT tblInvoice.InvoiceID AS tblInvoice_InvoiceID,
tblInvoice.InvoiceDate, tblInvoice.VAT, tblInvoiceDetail.InvoiceDetailID,
tblInvoiceDetail.InvoiceID AS tblInvoiceDetail_InvoiceID,
tblInvoiceDetail.RealisedPrice,
Sum(CCur(Nz(Round([SumOfRealisedAmount]*[Vat],2),0))) AS Tax
FROM tblInvoice INNER JOIN tblInvoiceDetail ON tblInvoice.InvoiceID =
tblInvoiceDetail.InvoiceID
GROUP BY tblInvoice.InvoiceID, tblInvoice.InvoiceDate, tblInvoice.VAT,
tblInvoiceDetail.InvoiceDetailID, tblInvoiceDetail.InvoiceID,
tblInvoiceDetail.RealisedPrice;

I am still having a problem trying to calculate the vat on the report
Sum(CCur(Nz(Round([SumOfRealisedAmount]*[Vat],2),0))) AS Tax

tblInvoice.VAT contains the selected vat rate for the invoice and
tblInvoiceDetail.RealisedPrice contains the invoice amount.

Your help would be much appreciated.

Thanks S

Use an IIf() expression inside your IIf() expression, to test for zero,
and assign zero if that is the case.

(BTW, the CCur() is doing little in that expression since it is inside
another IIf() with nulls, and Access could understand it.)

Your report will be based on a query. The query will use your VAT table
as well as your other table. You can therefore get the VatRate from the
field in the VAT table, and add it to the query output.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Sorry for the delay in responding! I now have this working but I have 2
problems, 1 being that using the following
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1)))))
when I set the vat rate to 0 I get the following displayed in the Sub
Total Excluding Vat #Div/0! and #Error in the VAT field which has the
following set as the control source =[subtotal]-[sub total] how do I
stop these messages being displayed when I select the 0 Rate VAT.

The Second problem I have is prior to making the above changes I had a
VAT report which showed the VAT for items sold this was simple as I only
had 1 VAT Rate Setup but now I have 2 VAT Rates 1.175 and 0.

I'm at a bit of a loss how to now go about doing the report??!!

Any advice would be much appreciated!

Thank you in advance.

S


Perhaps Access is not understanding the column correctly.

To break the problem down, open the form, and add a text box with
ControlSource of:
=Val(Nz([VatID].[Column](2), "0")
If that expression works, you can then use it in your main expression.

If not, and the column displays a % at the end, you may need to get the
field to show in the column as a double (i.e. without the percent.) The
RowSource would be:
SELECT VatID, VatRate, CDbl(Nz([VatRate], 0.175)) AS VatAsDouble
FROM ...
Once you have it working, you can hide the 3rd column, but Access
should be able to recognise it as a number.

Note that Column() is zero-based: the first column is zero, the second
is one, and so on.

Ok if I do the first part I get the subtotal excluding vat but as soon
as I add - [InvoiceSubForm].[Form]![OrderSubtotal] / (1 +
VatID.Column(2)) as the control source I get #Error?


Any ideas?



Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the 17.5%
found in one of the other columns? If it is in the 3rd column, then
the expression you need in the ControlSource of your text box would
be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

 

[SG]

The problem I have is that the TAX field when executing the query is all 0's

You have the calculated "Tax" field in your query.
Good.

Now in the report, you can just use:
=Sum([Tax])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have setup the query based on the invoice table and invoice details
table as shown here
SELECT tblInvoice.InvoiceID AS tblInvoice_InvoiceID,
tblInvoice.InvoiceDate, tblInvoice.VAT, tblInvoiceDetail.InvoiceDetailID,
tblInvoiceDetail.InvoiceID AS tblInvoiceDetail_InvoiceID,
tblInvoiceDetail.RealisedPrice,
Sum(CCur(Nz(Round([SumOfRealisedAmount]*[Vat],2),0))) AS Tax
FROM tblInvoice INNER JOIN tblInvoiceDetail ON tblInvoice.InvoiceID =
tblInvoiceDetail.InvoiceID
GROUP BY tblInvoice.InvoiceID, tblInvoice.InvoiceDate, tblInvoice.VAT,
tblInvoiceDetail.InvoiceDetailID, tblInvoiceDetail.InvoiceID,
tblInvoiceDetail.RealisedPrice;

I am still having a problem trying to calculate the vat on the report
Sum(CCur(Nz(Round([SumOfRealisedAmount]*[Vat],2),0))) AS Tax

tblInvoice.VAT contains the selected vat rate for the invoice and
tblInvoiceDetail.RealisedPrice contains the invoice amount.

Your help would be much appreciated.

Thanks S

Use an IIf() expression inside your IIf() expression, to test for zero,
and assign zero if that is the case.

(BTW, the CCur() is doing little in that expression since it is inside
another IIf() with nulls, and Access could understand it.)

Your report will be based on a query. The query will use your VAT table
as well as your other table. You can therefore get the VatRate from the
field in the VAT table, and add it to the query output.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Sorry for the delay in responding! I now have this working but I have 2
problems, 1 being that using the following
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1)))))
when I set the vat rate to 0 I get the following displayed in the Sub
Total Excluding Vat #Div/0! and #Error in the VAT field which has the
following set as the control source =[subtotal]-[sub total] how do I
stop these messages being displayed when I select the 0 Rate VAT.

The Second problem I have is prior to making the above changes I had a
VAT report which showed the VAT for items sold this was simple as I
only had 1 VAT Rate Setup but now I have 2 VAT Rates 1.175 and 0.

I'm at a bit of a loss how to now go about doing the report??!!

Any advice would be much appreciated!

Thank you in advance.

S


Perhaps Access is not understanding the column correctly.

To break the problem down, open the form, and add a text box with
ControlSource of:
=Val(Nz([VatID].[Column](2), "0")
If that expression works, you can then use it in your main expression.

If not, and the column displays a % at the end, you may need to get
the field to show in the column as a double (i.e. without the
percent.) The RowSource would be:
SELECT VatID, VatRate, CDbl(Nz([VatRate], 0.175)) AS VatAsDouble
FROM ...
Once you have it working, you can hide the 3rd column, but Access
should be able to recognise it as a number.

Note that Column() is zero-based: the first column is zero, the second
is one, and so on.

Ok if I do the first part I get the subtotal excluding vat but as
soon as I add - [InvoiceSubForm].[Form]![OrderSubtotal] / (1 +
VatID.Column(2)) as the control source I get #Error?


Any ideas?



Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the
17.5% found in one of the other columns? If it is in the 3rd column,
then the expression you need in the ControlSource of your text box
would be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

 

[SG]

Allen,

I how have a figure appearing the tax field have set an expression of Tax:
Sum(CCur(Nz(Round([RealisedPrice]*[Vat],2),0))) my problem is this is not
calculating the VAT correctly for example VAT Filed = 1.175 Realised Price
Field is 1000.00 The tax field is showing 1175.00???? This is incorrect as
the amount should be for example VAT = 1.175 Realised Price 1000.00 VAT =
148.94. The calculation I use is 1000 x 7 / 47 = 148.93617



Any suggestions?


S

You have the calculated "Tax" field in your query.
Good.

Now in the report, you can just use:
=Sum([Tax])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have setup the query based on the invoice table and invoice details
table as shown here
SELECT tblInvoice.InvoiceID AS tblInvoice_InvoiceID,
tblInvoice.InvoiceDate, tblInvoice.VAT, tblInvoiceDetail.InvoiceDetailID,
tblInvoiceDetail.InvoiceID AS tblInvoiceDetail_InvoiceID,
tblInvoiceDetail.RealisedPrice,
Sum(CCur(Nz(Round([SumOfRealisedAmount]*[Vat],2),0))) AS Tax
FROM tblInvoice INNER JOIN tblInvoiceDetail ON tblInvoice.InvoiceID =
tblInvoiceDetail.InvoiceID
GROUP BY tblInvoice.InvoiceID, tblInvoice.InvoiceDate, tblInvoice.VAT,
tblInvoiceDetail.InvoiceDetailID, tblInvoiceDetail.InvoiceID,
tblInvoiceDetail.RealisedPrice;

I am still having a problem trying to calculate the vat on the report
Sum(CCur(Nz(Round([SumOfRealisedAmount]*[Vat],2),0))) AS Tax

tblInvoice.VAT contains the selected vat rate for the invoice and
tblInvoiceDetail.RealisedPrice contains the invoice amount.

Your help would be much appreciated.

Thanks S

Use an IIf() expression inside your IIf() expression, to test for zero,
and assign zero if that is the case.

(BTW, the CCur() is doing little in that expression since it is inside
another IIf() with nulls, and Access could understand it.)

Your report will be based on a query. The query will use your VAT table
as well as your other table. You can therefore get the VatRate from the
field in the VAT table, and add it to the query output.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Sorry for the delay in responding! I now have this working but I have 2
problems, 1 being that using the following
=IIf(IsNull([SubTotal]),Null,CCur(Round([SubTotal]/(VAT.Column(1)))))
when I set the vat rate to 0 I get the following displayed in the Sub
Total Excluding Vat #Div/0! and #Error in the VAT field which has the
following set as the control source =[subtotal]-[sub total] how do I
stop these messages being displayed when I select the 0 Rate VAT.

The Second problem I have is prior to making the above changes I had a
VAT report which showed the VAT for items sold this was simple as I
only had 1 VAT Rate Setup but now I have 2 VAT Rates 1.175 and 0.

I'm at a bit of a loss how to now go about doing the report??!!

Any advice would be much appreciated!

Thank you in advance.

S


Perhaps Access is not understanding the column correctly.

To break the problem down, open the form, and add a text box with
ControlSource of:
=Val(Nz([VatID].[Column](2), "0")
If that expression works, you can then use it in your main expression.

If not, and the column displays a % at the end, you may need to get
the field to show in the column as a double (i.e. without the
percent.) The RowSource would be:
SELECT VatID, VatRate, CDbl(Nz([VatRate], 0.175)) AS VatAsDouble
FROM ...
Once you have it working, you can hide the 3rd column, but Access
should be able to recognise it as a number.

Note that Column() is zero-based: the first column is zero, the second
is one, and so on.

Ok if I do the first part I get the subtotal excluding vat but as
soon as I add - [InvoiceSubForm].[Form]![OrderSubtotal] / (1 +
VatID.Column(2)) as the control source I get #Error?


Any ideas?



Simon, I'm not reallly clear here.

If you change the Control Source to just:
=[InvoiceSubForm].[Form]![OrderSubtotal]
does it show the correct total?

Once that works, we need to clarify the combo's properties.
If its bound column is the ID (which is a whole number), is the
17.5% found in one of the other columns? If it is in the 3rd column,
then the expression you need in the ControlSource of your text box
would be:
=[InvoiceSubForm].[Form]![OrderSubtotal] -
[InvoiceSubForm].[Form]![OrderSubtotal] / (1 + VatID.Column(2))

(That's assuming the total includes VAT.)

 

[Allen Browne]

I'm going to have to leave this thread here.

I've given you all the ideas I have as to how to help Access to calculate
the data, and recognise the data type correctly.