Nest iif multiple condition to be converted to Switch or Select Case

[Ihussein]

Hello,
I'm using the following code and it's working, by any chance is it possible to have the same very code working using Switch or Select Case might be? using VBA function will be the best option.
Thanks

CategoryNat: IIf([NoBeneSign]=True And [NoDateOfPurchase]=True,DLookUp("CancellationTextAra","tblRedeemedCancelledVr","CancellationID= " & 7),IIf([NoBeneSign]=True,DLookUp("CancellationTextAra","tblRedeemedCancelledVr","CancellationID= " & 1),IIf([NoBeneCardNb]=True,DLookUp("CancellationTextAra","tblRedeemedCancelledVr","CancellationID= " & 2),IIf([NoTraderSign]=True,DLookUp("CancellationTextAra","tblRedeemedCancelledVr","CancellationID= " & 3),IIf([NoDateOfPurchase]=True,DLookUp("CancellationTextAra","tblRedeemedCancelledVr","CancellationID= " & 4),"")))))

 

[Isskint]

You could replace with a Select Case, but it would be a little convoluted. You would either need to run a select case, set a flag, run another select case cross referencing the first Select Case etc... OR nest Select Case's.

If you want to be able to return a value as a function, just use your IIF() statement and convert to a normal IF() statement.

 

[arnelgp]

yes, vba function will be the best (i think):

public function fnCancellation(bolNoBeneSign As Boolean, _
    bolNoDateOfPurchase As Boolean, _
    bolNoBeneCardNb As Boolean, _
    bolNoTraderSign As Boolean, _
    bolNoDateOfPurchase As Boolean) As Variant
dim intCancellationID As Integer
dim bolOk as boolean
If bolNoBenesign And bolNoDateOfPurchase Then
    intCancellationID = 7
    bolOK = True
end if
if not bolOk then
    if bolBenesign then
        intCancellationID = 1
        bolOk = True
    end if
end if
if not bolOk then
    if bolBeneCardNb then
        intCancellationID = 2
        bolOk = True
    end if
end if
if not bolOk then
    if bolTradersign then
        intCancellationID = 3
        bolOk = True
    end if
end if
if not bolOk then
    if bolNoDateOfPurchase then
        intCancellationID = 4
        bolOk = True
    end if
end if
if bolOK then
    fnCancellation=DLookUp("CancellationTextAra","tblRedeemedCancelledVr","CancellationID= " & intCancellationID)
end if
end function

you call it in your query:

CategoryNat: fnCancellation[NoBeneSign],[NoDateOfPurchase],[NoBeneCardNb],[NoTraderSign],[NoDateOfPurchase])

 

[Ihussein]

All right, thank you so much, now it is very clear.
Regards