Results 1 to 7 of 7

Thread: IIF query

  1. #1
    Join Date
    Oct 2005
    Posts
    23

    Unanswered: IIF query

    I have a working query with the following calculation:

    Expr1: [office]*[size]*[cartons]*[pack]

    I want to improve the calculation in that when the [cartons] is Null, the
    expression should be

    [office]*[size]*[quantity]*[pack]

    I know i have to use the IIf but how could i do it ? If the cartons is null, i have to include quantity in the equation.

    I have tried with the following expression, but i get errors,saying i have
    wrong syntax .Can you help me ?

    Expr1:iif([Cartons]is null,[Quantity],[Cartons];[office]*[size]*[cartons]*
    [pack]

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Expr1:[office]*[size]*Iif([Cartons] IS NULL,[Quantity],[Cartons])*[pack]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or
    Expr1:[office]*[size]*NZ([Cartons],[Quantity])*[pack]
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Other fields

    Dont forget IF any of the other fields [office] [size] [quantity] or [pack] can be null, to cater for these too.

    Maybe:

    Expr1:nz([office],0)*nz([size],0)*NZ([Cartons],nz([Quantity],0))*nz([pack],0)

    Which replaces null with 0 where appropriate.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good one, gareth

    so if the office is unknown, the entire calculation is 0, or if the price is unknown, the entire calculation is 0, and so on

    at least this will alert the user that something is wrong, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its going to go down well with the customers...
    if the data has a problem I can't see them getting to upset at an invoice with a line total of 0....... personally I'd probably set the nz default (if you MUST have a nz default to 1.

    mind you I'd of thought the logical place for such rules would be in the table design (so that null (and zero) values are not allowed)

    I'd also want to run a weekly/daily pricing query which attempted to identify rogue prices. one technique I've seen successfully used in food distribution is the concept of an average price per unit of measurement and report anything that is more that n% outside that unit price for that product category.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    speculation:

    how about a UNION of IS NULL and IS NOT NULL results so the whole thing stays in SQL without calling VBA functions?

    iif() is certainly slow in VBA (both True & False "results" are always evaluated), so i imagine it is also slow in Access-SQL

    nz([thisField], [thatField])
    actually works (to my total amazement!) and looks more elegant than
    iif(isnull([thisField], [thatField], [thisField])
    but is it as fast as a UNION approach?

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •