Help with IIF statements (nested)

[mvital]

Hi All,

I have the following statements. I have tested them separately and they each do what I want them to do. When I try putting them all together to make one statement I get "an aggregate function with too few arguments". What am I missing.

IIf([source]="ACGIH" Or "NIOSH" Or "OSHA" And [limit_type]="STEL",CDbl(Nz([RESULT]*[TIME_MINUTES]/IIf([TIME_MINUTES]<=15,15),0)


IIf([source]="ACGIH" Or "OSHA" And [LIMIT_TYPE]="TLV" Or "PEL",CDbl(nz([result]*[time_minutes]/480)))


IIf([source]= "NIOSH" AND [LIMIT_TYPE]= "REL", CDbl(nz([result]*[time_minutes]/600)))

IIf([source]= "NIOSH" Or "OSHA" or "ACGIH" And [LIMIT_TYPE] = "CEILING", [RESULT])

thanks in advance,

MV

 

[mvital]

I looked at my statement(s) again. I finally saw my error. I am SO sorry y'all, I had just seen it too much I was not seeing the problem anymore. R937 and ddiamond are correct [source] is incorrect. I [limit_type] was the field I should have been referencing.

My apologies to all and thank you all for all your help!!!

***One last question, I want to omit the [time_minutes]if it is less than 15 minutes from some of these. I tried this, but did not work.
I promise all the fields are correct and I am just looking for the syntax.

8HRTWA: CDbl(Nz([RESULT]*[TIME_MINUTES]/IIf([TIME_MINUTES]<=15,15,
IIf([LIMIT_TYPE] In ("TLV","PEL") And [TIME_MINUTES]>15,480,
IIf([LIMIT_TYPE]="REL" And [time_minutes]>15,600,
IIf([LIMIT_TYPE]="CEILING" And [time_minutes]>15,nz([RESULT])))))))


thanks in advance!

MV

 

[Golom]

You're missing the [blue]FALSE[/blue] on the last IIF

8HRTWA: CDbl(Nz([RESULT]*[TIME_MINUTES]/
IIf([TIME_MINUTES]<=15,15,
IIf([LIMIT_TYPE] In ("TLV","PEL") And [TIME_MINUTES]>15,480,
IIf([LIMIT_TYPE]="REL" And [time_minutes]>15,600,
IIf([LIMIT_TYPE]="CEILING" And [time_minutes]>15,nz([RESULT])[b][red],???[/red][/b]))))))

 

[r937]

you go, golom!



r937.com | rudy.ca

 

[mvital]

It worked! But for those where the time_minutes less than 15 and the limit_type is not 'STEL' the record is populated with a '#Error', I tried to filter those out in the query, but I get a datatype mismatch. Any ideas.

Thanks again !

 

[ddiamond]

mvital,

Can you post the current version of your IIF statements? The last version you posted which Golom replied to does not contain 'STEL'.

 

[mvital]

I figured it out. Thank you SO much to all of you for all your help!

Have a WONDERFUL Thanksgiving!

mv