queries and functions

[Cereldine]

hi, i have to perform some calculations in a query based on criteria based in it. i need to look at a applicants VAT status then a companys VAT status and decide whether VAT is to be included.

How would i go about doing this in a function rather than a IIF? there are 3 possible scenarios!

How do i get my code to look at the particular query/record in question, check some values, then send a string back based on the results back to the query? Or am i completely off track

 

[ShiftyJaffa]

You dont say what your 3 scenarios are or what type of fields your VAT status are, so I'll just give you a basic outline and you can tweak it.

Create function like this in a module.

Public Function MyResult (AppVAT as Boolean, ComVAT as Boolean) as String
If AppVAT = True and ComVAT = True then MyResult = "Scenario 1"
If AppVAT = False and ComVAT = True then MyResult = "Scenario 2"
If AppVAT = True and ComVAT = False then MyResult = "Scenario 3"
End Function

In your Query, use the function like this

Select MyResult(nz(AppVAT),nz(ComVAT)) FROM tblMyTable

Where AppVAT and ComVAT are the relevant fields in your table. In answer to your question, the result string is returned to the query through the name of the function, so you can set MyResult in your function to whatever you like.