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

 

[SgtJarrow]

You already posted this as thread705-1301612 where Remou gave you a good solution. Why not continue there if you still need more help???

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[mvital]

I cannot put the VBA code together. So, I wanted to try it in the query instead since I don't have to worry about knowing all about select clause syntax using VBA.

 

[SgtJarrow]

You statement will be EXTREMELY difficult manage in this format. I suggest you check the help files on Select Case and learn that....Give it a shot and let us know what you tried and what happened and we will gladly help out.

That being said, you will have to figure out some serious logic here....IIF statements are IIF(logic, true, false) So you need to basically have one logic statement, then its true, next logic statements, its true, etc...

IIF(logic1, true1, iif(logic2, true2, IIF(logic3, true3, IIF(logic4, true4, false1)))

The problem here is you only have one real false answer...but I get the impression that is what you want. Regardless, the logic is going to be a hassle, because you have to determine the order that gets the right result...Some of your logics have AND and some have OR, and they are comparing like results...

Good luck and let us know if we can help, but please post us what you have tried and why it did not work.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[r937]

the problem is, you cannot just toss an OR into a series of values like that

this is wrong --

IIf([source]="ACGIH" Or "NIOSH" Or "OSHA" And ...

you could change it to this --

IIf( ( [source]="ACGIH"
or [source]="NIOSH"
or [source]="OSHA" ) And ...

or you could change it to this --

IIf([source] IN ("ACGIH","NIOSH","OSHA") And ...

and obviously the latter is cleaner ;-)

r937.com | rudy.ca

 

[mvital]

I tried another way instead...here's what I got. When I run the query. I get all blanks in the 8HRTWA field.

8HRTWA: IIf([source]="STEL",CDbl(Nz([RESULT]* [TIME_MINUTES]/15,
IIf([SOURCE] In ("TLV","PEL"),CDbl(nz([result]*[time_minutes]/480,
IIf([SOURCE]="REL",CDbl(nz([result]*[time_minutes]/600,[time_minutes])))))))))

If the source is STEL the the calculation should use 15.

If the source is TLV or PEL the calculation should use 480.

If the source is REL the calculation should use 600.

If the source is not STEL or TLV or PEL or REL then the just the [time_minutes] is used.

What am I missing above?

MV

 

[lespaul]

you've got your parens all screwy!!

8HRTWA: IIf([source]="STEL",CDbl(Nz([RESULT]* [TIME_MINUTES]/15[b]))[/b],
IIf([SOURCE] In ("TLV","PEL"),CDbl(nz([result]*[time_minutes]/480[b]))[/b],
IIf([SOURCE]="REL",CDbl(nz([result]*[time_minutes]/600[b]))[/b],
[time_minutes])))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[r937]

try this and let me know if it works...

8HRTWA: CDbl(Nz([RESULT]*[TIME_MINUTES]/
IIf([source]="STEL",15,
IIf([SOURCE] In ("TLV","PEL"),480,
IIf([SOURCE]="REL",600,[RESULT]))) ))

r937.com | rudy.ca

 

[randy700]

Try this...

SELECT CASE Source
   Case Is "STEL":
      8HRTWA = CDbl(Nz(Result * Time_Minutes / 15))
   Case Is "TLV", "PEL":
      8HRTWA = CDbl(Nz(Result * Time_Minutes / 480))
   Case Is "REL":
      8HRTWA = CDbl(Nz(Result * Time_Minutes / 600))
   Case Else:
      8HRTWA = Time_Minutes
End Select

Randy

 

[r937]

randy, what language is that?

r937.com | rudy.ca

 

[Golom]

Rudy

It's VB (Access Dialect because of the use of NZ) ... although in this case (pun only slightly intended), the "Case Is" construct isn't required. Just "Case" would work fine.

 

[mvital]

randy where would I put the select case statement. I'd like to try that too. I tried it, but I guess I was not putting it in the right place. I have a dialog form that user select certain criteria to filter the report by. The source and limit type are two of their choices. Would I put in the the After Update of the source? Will it work and filter my report?

THANK YOU SO MUCH to all of you for continuing to stick with me on this.

MV

 

[mvital]

Leslie and r937. when I use the code you both suggested 8HRTWA is populated with the result. It appears that the conditions were not applied, but the last one. Where the results is populated in the 8HRTWA if all the others are false.

thanks for your input and your help!

 

[mvital]

did you all think about a solution. I've been trying several variations and I can't get it to work the way I want it to.

MV

 

[bboffin]

As far as I can see lespaul's solution is correct. It has the correct number of matching parentheses and they're all in the right place. However if [time_minutes] or [result] is null it does rely on the default value of the Nz function being zero so maybe it would be best to add another IIF around the whole lot checking if [time_minutes] or [result] is null and returning 0 if it is.

Randy700's solution won't work as this is part of an Access SQL statement not VBA. It really needs the CASE statemnet from SQL Server but that's not available in Access SQL.

The 8HRTWA: at the start might cause some people confusion but it's how an alias is represented in the Access query designer. In the SQL view it would appear as ... AS 8HRTWA



Bob Boffin

 

[r937]

i have Nz around my formula ;-)

and if mine didn't work, then i can only conclude that [source] just isn't one of the listed values

r937.com | rudy.ca

 

[mvital]

That's why it doesn't make sense why it does not work. [source] is one of the fields.

 

[r937]

could you tell me what you get for this query?

select sum(
         IIf([source]="STEL",1,
         IIf([SOURCE] In ("TLV","PEL"),1,
         IIf([SOURCE]="REL",1,1000000)))
          ) as total
     , count(*) as rows
  from yourtable

r937.com | rudy.ca

 

[dhookom]

I would do everything possible to avoid solving this with nested IIf()s or code. It looks like you could create a small lookup table rather than hard-coding values like "STEL", "TLV", "PEL",1, 100000, etc in a complex expression.

Your lookup table with have columns for Source, Limit_Type, and a number.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ddiamond]

then i can only conclude that [source] just isn't one of the listed values

r937 is correct. What is the value of [source] when you get unexpected results? Can [source] have trailing spaces? If so, you will need to use the trim() function.