Vat and Zero Rated

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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?



"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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.

"Allen Browne" wrote in message
...
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

"SG" wrote in message
...
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!!


"Allen Browne" wrote in message
...
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.)

"SG" wrote in message
...
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


"Allen Browne" wrote in message
...
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.

"SG" wrote in message
...
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,

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


"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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?



"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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.

"Allen Browne" wrote in message
...
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

"SG" wrote in message
...
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!!


"Allen Browne" wrote in message
...
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.)

"SG" wrote in message
...
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


"Allen Browne" wrote in message
...
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.

"SG" wrote in message
...
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?

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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


"Allen Browne" wrote in message
...
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.

"SG" wrote in message
...
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?



"Allen Browne" wrote in message
...
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,

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



"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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


"Allen Browne" wrote in message
...
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.

"SG" wrote in message
...
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?



"Allen Browne" wrote in message
...
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,

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

"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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


"Allen Browne" wrote in message
...
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.

"SG" wrote in message
...
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?



"Allen Browne" wrote in message
...
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.)

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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

"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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


"Allen Browne" wrote in message
...
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.

"SG" wrote in message
...
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?



"Allen Browne" wrote in message
...
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.)

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


"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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

"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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


"Allen Browne" wrote in message
...
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.

"SG" wrote in message
...
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?



"Allen Browne" wrote in message
...
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,

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


"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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

"Allen Browne" wrote in message
...
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
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


"Allen Browne" wrote in message
...
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.

"SG" wrote in message
...
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?



"Allen Browne" wrote in message
...
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.)

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SG" wrote in message
...
The problem I have is that the TAX field when executing the query is all
0's