Nested IIF Statement

[his65]

I've been using the nested IIF statement in my query using MS Access 97. I'm trying to use it MS Access 2003 and its coming up with syntax errors. The first thing the query does to my statement is convert the commas to vertical lines (|). When I change it back to commas, I still get a syntax error. Can anyone assist???

Qty Used: IIf([Category]=1,[Concentrate]/128*[Quantity],IIf([Category]=2,[Concentrate]*[Quantity],IIf([Category]=3,[Quantity]*1,IIf([Category]=4,[Concentrate]/99*[Quantity]))))

Thanks in advance.

 

[CraigDolphin]

The most obvious issue I see if that you need three parts to an iif

1. A logical test
2. What to do if test evaluates true
3. What to do if test evaluates false

IIf([Category]=4,[Concentrate]/99*[Quantity])

Only has parts 1 & 2.

Have you lookeed into the switch function instead of using nested iif's? It does tend to simplify things for debugging.

Qty Used: Switch([Category]=1,[Concentrate]/128*[Quantity],[Category]=2,[Concentrate]*[Quantity],[Category]=3,[Quantity]*1,[Category]=4,[Concentrate]/99*[Quantity],[Category] Not In (1,2,3,4),Null)

Also, make sure that Concentrate doesn't contain any nulls. If it does/could then make sure to use the Nz function wherever you reference the field to ensure that nulls don;t mess you up.

 

[his65]

CraigDolphin,
Thanks for the reply. Some info concerning my query...The [category] will always be a number from 1-4. They are pre-entered by me and will never be null.

I changed my query from the IIF to the Switch statement and I still get the same error: Expression you entered contains invalid syntax.

I'm baffled that my original query worked in Access 97; but not in 2002. Any other suggestions....My computer with Access 97 is constantly crashign and I would like to transfer all my programs over to my other computer. Thanks again for the help.

 

[his65]

Oh, just one more piece of info...If I don't open the query at all, it works fine!! Only when I open the query do I get this message and the statement changes from commas to vertical lines (|).

 

[WayneRyan]

his65,

An alternative is to use a Public Function:

QtyUsed: fnCalculateQuantity([Category], [Quantity], [Concentrate])

Then, just put this code in a module:

Public Function [B]fnCalculateQuantity[/B](Category As Integer, Quantity As Integer, Concentrate As Integer) As Single
Select Case Category
   Case 1
      fnCalculateQuantity = Concentrate/128
   Case 2
      fnCalculateQuantity = Concentrate * Quantity
   Case 3
      fnCalculateQuantity = Quantity
   Case 4
      fnCalculateQuantity = Concentrate/99
   Case Else
      fnCalculateQuantity = 0
   End Select
End Function

To me, that's easier to understand/maintain.

And, I'm not sure of the datatypes for Quantity & Concentrate

hth,
Wayne