Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: multiple if statement

    Hi everyone, This should be a simple fix for you guys and gals. Here is the function I am writing in a query.

    MVP: IIf([MSRP]>[986320C],[986320C]*[986320],(IIf([MSRP]<[986320C],[MSRP]*[986320],IIf([MSRP]>[986310C],[986310C]*[986310],(IIf([MSRP]<[986310C],[MSRP]*[986310],"Wrong"))))))

    If I separate the 986310 from the 986320 IF statement they both work. When I put them together it fails. I have a form that allows a query and these fields are associated with that query. For some reason it will only read the first line. For example if 986320 is returned from the query, this function works. If 986310 is returned it fails. I get a $0.00 when 986310 is used. I just don't think its reading all the way through. If anyone has any ideas I would be tickled.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    At risk of making a fool of myself again, isn't it only going to get to 986310C if [MSRP]=[986320C] (i.e. not > and not <) regardless of the [986310C] value?

    If I've managed to bark up the wrong tree again, I usually throw together a vb If or Select Case function if a SQL iif statement won't work and I can't figure out why; you can at least step through the code then and see the path it is taking.

    hopin' you are tickled

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    The good thing in my case is [MSPR] will never equal 986310C. So it should read one or the other.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    rewriting your IIF in normal IF/ELSE

    Code:
    if [MSRP]>[986320C]
       return [986320C]*[986320]
    else 
       'you already know that [MSRP] is not greater than [986320C]
       'so why do you bother with this next step 
       if [MSRP]<[986320C]
          return [MSRP]*[986320]
       else
          'unless [MSRP]=[986320C] is possible, you will never get here
          if [MSRP]>[986310C]
             return [986310C]*[986310]
          else
             'you already know etc etc etc
             if [MSRP]<[986310C]
                return [MSRP]*[986310]
             else
                'unless [MSRP]=[986310] is possible, you will never get here
                return "Wrong"
             endif
          endif
       endif
    endif
    try writing it first as a normal code set of nested IF/ELSE. it makes it much easier to get the logic right.


    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
  •