Vat Field on The Report Footer 1

[ncopeland]

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))))

 

[MajP]

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.

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

​

 

[ncopeland]

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.

Thanks in advance.

Neil.

 

[MajP]

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.

 

[ncopeland]

Thanks all sorted.