IIf statement problems, problems

[fibayne]

Hi

I have quite a few IIF statements in queries on forma and reports which have been working however I am tring to add more variables into them and am getting messages saying they are too complex, ideally I would like to be able to get them into code (I think, would this be a better way to deal with them ?) but dont know how to do this.
Below is an example of one statement that that is giving me the error message, i know it is long and am sure there is a better way to deal with it

Delivered:
IIf([CPM]='CPM' And [%Delivered]<90,[DeliveredImps]/1000*[OnlineCP],
IIf([CPM]='CPM' And [%Delivered]=90,[DeliveredImps]/1000*[OnlineCP],
IIf([CPM]='CPM' And [%Delivered]>90<100,[Quantity]/1000*[OnlineCP],
IIf([CPM]='CPV' And [%Delivered]<90,[DeliveredImps]*[OnlineCP],
IIf([CPM]='CPV' And [%Delivered]=90,[DeliveredImps]*[OnlineCP],
IIf([CPM]='CPV' And [%Delivered]>90<100,[Quantity]*[OnlineCP],
IIf([CPM]='CPU' And [%Delivered]<90,[DeliveredImps]*[OnlineCP],
IIf([CPM]='CPU' And [%Delivered]=90,[DeliveredImps]*[OnlineCP],
IIf([CPM]='CPU' And [%Delivered]>90<100,[Quantity]*[OnlineCP],
IIf([CPM]='CPC',0,
IIf([CPM]='CPA/CPC',0,
IIf([CPM]='CPC(Adserving)',0,
IIf([CPM]='CPA(Affilliate)',0,
IIf([CPM]='CPA',0,
IIf([CPM]='Fixed(1000)',0,
IIf([CPM]='Fixed(100)',0,
IIf([CPM]='CPC(Search)',0)))))))))))))))))

'IIf' anyone could give me some advice I would be extremely grateful

thanks

Fi

 

[Dennisk]

I would create a function instead and use block if/then/else.

then replace the IIf statements with a call to the function with the name of the comulms you wish to test.

 

[KeithG]

I thought you could only nest seven iff functions within a function but it looks like you have alot more than seven. You could use the Switch function which is kind of similar to a Select Case Statement in VBA.

 

[fibayne]

IIf

Hi Keith

I seem to be able to get to 9 statements before it stops working could you point me in the direction of where I could find info re Switch functions I have looked her but still havent been able to work it correctly

thanks for your help
Fi

 

[fibayne]

I

Hi Dennis

Your suggestions sounds good would you be able to give me a couple of pointers on how to do this ..
thanks fi

 

[Dennisk]

the structure of your function would be something like this
assuming your numeric parameters are integer

on error got Err_handler

Public Function YourNameHere(varCPM as string, varDelivered as integer,OnlineCp as integer,varDeliveredImps as integer,varOnlineCP as integer) as single

dim sglResult as Single
If varCPM = "CPM" And varDelivered<90 then

sglResult = varDeliveredImps/1000 * varOnlineCP

else
if ............................etc
endif



YourNameHere=sglResult

Exit_Handler:
exit function

Err_handler:
msgbox err & " " & err.Description
resume exit_Handler

end Function

 

[KeithG]

Look up the Switch function in Acess help.