Calcuations in queries

[Guest]

I have a Tenant table which has an AnnualRent field. In a query I use the
expression builder to calculate the QuarterRent for the tenant from which I
produce a report. This all works well until I need to show the VAT on the
QuarterRent amount. The problem is that not all of the tenants are charged
VAT and I don’t know how to get around this. Normally I would have used the
expression builder to calculate the VAT on the QuarterRent but as it doesn’t
apply to all tenants I don’t know what to do.

Any help would be much appreciated on this topic.

Thanks.

emc

 

[Marshall Barton]

I have a Tenant table which has an AnnualRent field. In a query I use the
expression builder to calculate the QuarterRent for the tenant from which I
produce a report. This all works well until I need to show the VAT on the
QuarterRent amount. The problem is that not all of the tenants are charged
VAT and I don’t know how to get around this. Normally I would have used the
expression builder to calculate the VAT on the QuarterRent but as it doesn’t
apply to all tenants I don’t know what to do.

The data in the query needs to include something that
indicates if the VAT applies to the tenant or not.

Assuming you have a Yes/No field in the table with this
information, you can use an expression something like:

IIf(Tenamts.UseVat, rent * (1 + VatPercent), rent)

 

[Guest]

I forgot to mention that in the Tenant table I have a check box to show that
they should be charged VAT. I don't know if this will help me more.

 

[John Spencer]

Do you have a field that tells you whether or not the tenant is subject to
the VAT? You can use an IIF statement to figure out whether or not to
muliply by Quarter Rent by 1 or by 1 plus the VAT rate.

IIF(NoVatField=True, 1, 1+VatRate) * (Whatever formula you are using to
calculate the quarterrent)

 

[Guest]

John/Marshall,

Thank you for your formulas. John, I have used your formula which is
working wonderfully. The only other question I have is, is it possible to
calculate the VAT and show it on its own instead of it being added to the
QuarterRent? In the report I have to show the VAT and the QuarterRent
separate from each other in order that I can calculate the VAT and
QuarterRent separately at the end of each month.

Many thanks for your help on this already.

emc

 

[Guest]

John,

Have messed about with it and got the answer ! Thanks very much for all of
your help.

 

[Guest]

John,

I wonder if you could possibly help me again. I have used this calculation
now for working out various VAT issues, ie VAT on Rent, Management,
Insurance, etc. Although I was asked to show the VAT separately on one
report, I am now being asked to show it altogether on a separate report, ie
to add the VAT on the Rent, Management and Insurance and have it as one
total.

I have tried the IIf command in order to do this but I am getting into a
fankle as there are at least three fields that are either charged or not
charged VAT ! I’m not sure if it should be entered as a code (of which I
know very little) in the report or an expression in the query.

Any help would, as always, be much appreciated

emc

 

[John Spencer]

Do you always have VAT on all three items? Is the VAT rate the same on all
three items? Do you have one field for Rent, one for Management, and one
for insurance?

Assumptions:
A single VAT Rate
3 fields (Rent, Management, Insurance) that may be null (blank)

IIF(NoVatField=True, 0, VatRate) *
(Nz(Rent,0)+NZ(Management,0}+NZ(Insurance,0))

 

[Guest]

John,

My apologies for taking so long to reply. Due to other issues I wasn’t
giving this one my full attention. In answer to your questions:

No there is not always VAT on all three items. I have a separate Yes/No
field for VAT in each of the Rent, Management and Insurance tables (therefore
one field for each of Rent, Management and Insurance). The reason I have to
have three separate VAT fields is because VAT may be applicable to the Rent
but not to the Management and vice versa.

However if VAT is charged it is always the same rate.

Regards,

emc

 

[John Spencer]

IIF(NoVatField=True, 0, VatRate) *
(Nz(Rent,0)+NZ(Management,0}+NZ(Insurance,0))

Then you have to do each calculation as follows to get the Total VAT

IIF(NoRentVat=True,0,VatRate*Nz(Rent,0)) +
IIF(NoManagementVat=True,0,VatRate*Nz(Management,0)) +
IIF(NoInsuranceVat=True,0,VatRate*Nz(Insurance,0))

That should all be on one line, but the newsreader will cause it to wrap to
multiple lines

 

[Guest]

John,

Thank you once again. It is doing exactly what I need now. For me, this is
an invaluable piece of code.

Thank you.

emc