nested if statements

[aspnetuser]

is it possible to do the following iif statement:
=IIf(

=In("ABC","DEK"),Sum([Amt]),0)

I receive a synatax error when trying to compile this?

How can i use In("","",) Etc in a formula?

access 2k

regards,

 

[hneal98]

=IIf(

=In("ABC","DEK"),Sum([Amt]),0)

 would have to be

[code]iif([code] = "ABC", Sum([Amt], iif([code]="DEK", Sum([Amt]),0))

or

iif([code] = "ABC" or [code]="DEK", Sum([Amt]),0))

 

[mordja]

Or

Sum(iif(

 = "ABC" or [code]="DEK",[Amt],0))

MOrdja

 

[hneal98]

Actually mordja's is better.

 

[aspnetuser]

mORDJA, what is the limit on how many OR statements you can do. It works frin for 3 but when i try to do 25 it returns an error...

please advise.

 

[mordja]

aspnetuser,

25 ??? I dont know the limit but from what you say it is less than 25. You could maybe use a switch instead. Although perhaps you should think about a more efficient way to do this, maybe a parameter table.

Mordja

 

[aspnetuser]

can you advise how to do that?

 

[hneal98]

I believe a statement can only be 256 characters. Your best bet would be to put these values in a table and then link the query to the table.

 

[aspnetuser]

I have to do for different calculations on the report? with all different codes....

 

[hneal98]

You can put all codes in the table in one field and link that field to the

 field in your current table.

 

[aspnetuser]

i have to calc 4 amount fields based on that one code field all with different crtieria...not sure i follow you...

 

[hneal98]

Do you need to put all 25 codes into one formula?