Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Vat Field on The Report Footer

Vat Field on The Report Footer

Vat Field on The Report Footer


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


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.

Thanks in advance.


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

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

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close