Nesting IIF Function in Query...

[ThatNewGuy]

Hello,

First of all I know there are a million BETTER ways to do this, but I've managed to get the first half of this to work in my database so I'm running with it. I'm trying to nest several Iif functions together in one query field but I'm having some problems. The first portion of the Iif function works exactly as I need it to, as it rounds any number below 1 such as .67 up to 1 and it rounds any larger number to its interger such as 3.6 to 3. What I need to accomplish now is if there are 4 new part samples ordered that the query won't run through the initial function but by pass it to apply the additional nested Iif function to simply state that there are 4.

IIF ( [Packages] <1 , Int([Packages] +1) , Fix([Packages])
the above portion works as needed on its own...

When I add the nested Iif it looks like this:

IIF ( [Packages] <1 , Int([Packages] +1) , Fix([Packages]) ,
IIF ( [Shipped] =4 , [Shipped]*1 , [Shipped]*1 ))

Access returns an error that reads "expression you entered has a function containing the wrong # of arguments"

Any help is appreciated. Thanks.

 

[PHV]

I'm not sure I've really understood, but you may try this:
IIf([Shipped]=4,4,IIf([Packages]<1,1,Fix([Packages]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ThatNewGuy]

Thanks PHV,

I tried your exact function as well as a variation on it and both attempts return a #Error in the field. Back to the drawing board I suppose.

 

[dhookom]

I would create a small user-defined function. I hate nested IIf()s and don't feel they should be used.

Generally if multiple IIf()s are desired, they are used to described business calculations. These may need to be changed. You should keep all these types of calculations somewhere that allows easy maintenance and documenting.

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]

 

[MajP]

I am with dhookum. There are a lot of articles you can read about issues with nested IIf().
1) Unexpected results or run-time errors because does not short circuit
2) Hard to write, read, and debug
3) Very poor performance.
Write your own using If Then or Select Case

 

[ThatNewGuy]

Like I said earlier, I'm sure there are better ways to do this but I was nesting IIf() functions because I actually understood them. Look, I'm not exactly very handy in writing code in Access. Honestly, I don't know what I'm doing when it comes to code.

I've attempted to build a user defined function but I can't seem to figure it out. I was working with a module that reads as follows:

Function Replacement() As Double

If [Shipped] = 4 Then
Replacement = 1 And [# In Box] = 4
Else
Replacement = Int([Packages] + 1)
Else
Replacement = Fix([Packages])
End If

End Function

I left the so-called argument () blank above because what I was using wasn't working and secondly I'm not even sure what the "argument" is considered in visual basic. I know this is basic stuff but I'm trying to learn how this all works in visual basic.

 

[dhookom]

Good questions. I'm sure you are not alone in not grasping VBA and creating functions. I'm not sure what the data type of Shipped and Packages and if they are possibly null. However try start with something like:

Function Replacement(dblShipped as Double, _
      dblPackages as Double) As Double
' from PH IIf([Shipped]=4,4,IIf([Packages]<1,1,Fix([Packages]))
 
  If dblShipped = 4 Then
      Replacement = 4
    ElseIf dblPackages <1 Then
      Replacement = 1
    Else
      Replacement = Fix(dblPackages)
    End If
End Function

You can enter this function into a standard module and save the module as "modBusinessCalcs". You can test this by opening the debug window and entering something like:
? Replacement(4,12)

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]