Iif Statement help

[essseeproductions]

Hi, basically i need a soltuion which is a huge Iif statement i
believe maybe a nested Iif staement.
OK basically i want the statement to be like this:

IIf[X]=Bob,IIf[BobTotal] Between 1 and 2,"Green",Iif[BobTotal] Between
2 and 3,"Blue",IIf[X]=Ben, Iif[BobTotal] Between 3 and 4,"Grey" etc..

So basically if field X has a value then it gives a result depending
on the value in field y. However the value in field X may be one of 5
options, therefore i suppose i need some sort of if statement in an if
statement in an if statement..

Many thanks in advance to all help recieved.

 

[Van T. Dinh]

Very messy with nested IIf 4 levels deep ...

Check Access VB Help on the Switch() function.

Alternatively, write your own UDF (User-Defined Function).

 

[essseeproductions]

OK Thanks for the advice Van, i am going to try the Switch function. I
was wondering how would i enter the VBA code, do i just put it into
the Field box of the query?

 

[essseeproductions]

Ok two problems, firstly is it possible to do something like this:

Function matchLanguage(ByVal cityName As String) As String
Return CStr(Microsoft.VisualBasic.Switch( _
cityName = "London", Iif ([citypop] between 50 and
1000,"Eng1",Iif ([citypop] between 1000 and 2000,"Eng2)) _
cityName = "Rome", Iif ([citypop] between 50 and
1000,"Rme1",Iif ([citypop] between 1000 and 2000,"Rme2)) __
etc....
End Function

If so i just need to know where to insert this code (i want it as a
field in a query)

 

[Van T. Dinh]

Are you doing this outside Access???

 

[essseeproductions]

Yes, i'm making the system in Microsoft Access 2000.
Why is it not possible to enter the above into Access as a query?

 

[Van T. Dinh]

I asked that because your function looks nothing like Access VBA. For a
start, there is no Return keyword in VBA as far as I know and the Reference
is VBA, not "Microsoft.VisualBasic". Since VBA is the "default" Library in
code, we don't need to use "VBA" qualifier, either.

Microsoft Access (in general) actually has 3 distinct & major components:

* Access itself which consists basically of Forms & Reports

* The default Database Engine JET which handles SQL, and

* VBA (Visual Basic for Application) which handles VBA code.

There are communication channels between these 3 major components. However,
it does not mean that a construct in one component can be understood by
other components. Thus, VBA codes are not recognised by JET engine which
handles SQL and you cannot mix the syntaxes of JET & SQL. However, JET is
aware of the function names, including UDF. Therefore, if JET sees a VBA
function name, it will pass the function back to VBA for processing.

Check Access VB Help topic "Writing a Function Procedure" and the If
statement.