Results 1 to 5 of 5
  1. #1
    deso is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2013
    Location
    brisbane, australia
    Posts
    23

    iff function in expression builder

    hi guys, i'm using the expression builder in access 2010 to build an IFF calculation. Basically, I want to assign a value to a new field where field one (bottom) minus field 2 (top) is less than a certain value.



    I tried this but I get a syntax error:

    IIf([BOTTOM] - [TOP] <= 2 , "<2", IIf([BOTTOM] - [TOP] <= 5 , "<5", IIf([BOTTOM] - [TOP] <= 10 , "<10", IIf([BOTTOM] - [TOP] <= 20 , "<20", IIf( [BOTTOM] - [TOP] <= 30 , "<30", IIf([BOTTOM] - [TOP] <= 50 , "<50", IIf([BOTTOM] - [TOP] >= 50 , "50>") ) ) ) ) ) )

    I thought that perhaps the "<" symbols in the <truepart> were causing problems so I tried this as well (changing to a letter):

    IIf([BOTTOM] - [TOP] <= 2 , "A", IIf([BOTTOM] - [TOP] <= 5 , "B", IIf([BOTTOM] - [TOP] <= 10 , "C", IIf([BOTTOM] - [TOP] <= 20 , "D", IIf( [BOTTOM] - [TOP] <= 30 , "E", IIf([BOTTOM] - [TOP] <= 50 , "F", IIf([BOTTOM] - [TOP] >= 50 , "G") ) ) ) ) ) )

    but still i get a syntax error. I'm sure it's possible to do this query, but i just have my expression formula wrong.....but I don't have a <falsepart> value......does that matter? Any ideas on what I'm doing wrong?

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Code:
    IIf
        (
            [BOTTOM] - [TOP] <= 2 , 
            "<2", 
            IIf
                (
                    [BOTTOM] - [TOP] <= 5 , 
                    "<5", 
                    IIf
                        (
                            [BOTTOM] - [TOP] <= 10 , 
                            "<10", 
                            IIf
                                (
                                    [BOTTOM] - [TOP] <= 20 , 
                                    "<20", 
                                    IIf
                                        (
                                            [BOTTOM] - [TOP] <= 30 , 
                                            "<30", 
                                            IIf
                                                (
                                                    [BOTTOM] - [TOP] <= 50 , 
                                                    "<50", 
                                                    IIf
                                                        (
                                                            [BOTTOM] - [TOP] >= 50 , 
                                                            "50>"
                                                        ) 
                                                ) 
                                        ) 
                                ) 
                        ) 
                ) 
        )
    At first glance, is the last IIf() missing the FALSE option.

    Thanks

  3. #3
    deso is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2013
    Location
    brisbane, australia
    Posts
    23
    of course. I added the false part and it worked with both formulae. I just didn't think to add the falsepart at the end.

    Thanks, this was driving me crazy. Glad it was a simple fix.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The expression overlaps on the =50 criteria. Probably doesn't impact what you want.

    Consider this alternative:

    Switch([BOTTOM] - [TOP]<=2,"<2", [BOTTOM] - [TOP]<=5,"<5", [BOTTOM] - [TOP]<=10,"<10", [BOTTOM] - [TOP]<=20,"<20", [BOTTOM] - [TOP]<=30,"<30", [BOTTOM] - [TOP]<=50,"<50", [BOTTOM] - [TOP]>50,"50>")

    Choose() is another interesting function that can sometimes replace nested IIf.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Glad you got things working.
    In addition to June's suggestion :
    Depending on your situation,
    you can look at the option of eliminating the last IIf
    &
    have "50>" in the False condition of the second-last IIf.

    Instead of

    Code:
    .........................
    IIf
    (
        [BOTTOM] - [TOP] <= 50 , 
            "<50", 
            IIf
                (
                        [BOTTOM] - [TOP] >= 50 , 
                            "50>"
            ) 
    )
    ...................
    you can try

    Code:
    .........................
    IIf
    (
        [BOTTOM] - [TOP] <= 50 , 
            "<50", 
        "50>"
    )
    ...................
    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  2. IIf Function for expression builder
    By Bertrand82 in forum Programming
    Replies: 3
    Last Post: 11-12-2012, 12:34 AM
  3. Expression builder help
    By jigg14 in forum Forms
    Replies: 1
    Last Post: 03-19-2012, 09:47 AM
  4. Expression Builder not there
    By schnuber in forum Access
    Replies: 1
    Last Post: 02-17-2012, 01:17 AM
  5. Expression builder
    By PJ_d_DJ in forum Access
    Replies: 2
    Last Post: 02-24-2011, 03:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums