INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Vat Field on The Report Footer

## Vat Field on The Report Footer

(OP)
Hi

I have this formula in a text field at the bottom of the report. Is there a way to debug the statement to see what each field equals. Thanks for the help.

=IIf([Which-Company-Name]="Test1" And [Customer-Country]="Ireland",([Total]+[Deposit])*([Vat-Rate]/100),IIf([Which-Company-Name]="Test2" And [Customer-Country]="England",([Total]+[Deposit])*([Vat-Rate]/100),IIf([Which-Company-Name]="Test2" And [Customer-Country]="Northern Ireland",([Total]+[Deposit])*([Vat-Rate]/100),IIf([Which-Company-Name]="Test2" And [Customer-Country]="Scotland",([Total]+[Deposit])*([Vat-Rate]/100),0))))

### RE: Vat Field on The Report Footer

Calculated fields and nested iifs are very hard to debug. I would build a user defined function and use that instead. You can test that ahead of time using literals. Once you are satisfied that the logic works you can then pass the field names in to use it in the form/report.

#### CODE -->

Public Function GetAmount(CompanyName As String, Customer_Country As String, Total As Currency, Deposit As Currency, VatRate As Double) As Currency
If CompanyName = "Test1" And Customer_Country = "Ireland" Then
GetAmount = (Total + Deposit) * VatRate / 100
ElseIf CompanyName = "Test2" Then
Select Case Customer_Country
Case "England", "Northern Ireland", "Scotland"
GetAmount = (Total + Deposit) * VatRate / 100
End Select
End If
End Function

Public Sub TestAmount()
Debug.Print GetAmount("Test1", "Ireland", 10, 10, 7)
Debug.Print GetAmount("Test1", "USA", 10, 10, 7)
Debug.Print GetAmount("Test2", "England", 10, 10, 7)
End Sub 

### RE: Vat Field on The Report Footer

(OP)
Hi

Thanks for the help on this. Don't do a lot of coding and this is the last part of the puzzle.

Not quite sure how to access this. The query runs on the report which calculates the total field. It is this total field that ~I want to calculate VAT or sales tax on the variables based on the location of the customer.

On the VAT field at the bottom of the report how do I get it to access the function.

Neil.

### RE: Vat Field on The Report Footer

1) You would build a function in a standard vba module. If I did it correctly you would paste that code into a vba module.
2) You could test it as shown using literals, by running the test.
3) Then to use it in a calculated control you would call the function like you are doing now with your nested if except it would look like this passing in your field names
=GetAmount([Which-Company-Name],[Customer-Country],[total],[Deposit],[vat-rate])

I am not saying your nested iif will not work, but those a so hard to debug. A comma, bracket, mispelling etc will make it fail and difficult to do in pieces.

### RE: Vat Field on The Report Footer

(OP)
Thanks all sorted.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!