Results 1 to 8 of 8
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    iif statement with <=>

    I'm trying to calculate vacation hours earned in a query. This is what I came up with, but it will not accept it.
    VacEarned: iif(YearsOn<2,"8", iif(YearsOn>=2 and <5,"9"), iif(YearsOn>=5 and <,"10"), iif(YearsOn>=10 and <15,"11", iif(YearsOn>=15 and <20,"13"), iif(YearsOn>=20 and <25,"15"), iif(YearsOn>=25 and <30,"17"), iif(YearsOn>=30 and <35,"21")

    I get this error:
    Click image for larger version. 

Name:	error.JPG 
Views:	8 
Size:	18.1 KB 
ID:	42042

    Click image for larger version. 

Name:	error2.JPG 
Views:	8 
Size:	18.5 KB 
ID:	42043

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You have no value for a comparison operator:
    iif(YearsOn>=5 and <,"10")

    Can't recall if you can use Switch function in a query, but if so, I think it would be better than a bunch of nested IIFs like that. I don't even want to try and figure out the ramifications of each one of those inner ones being the False part of an outer one!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Also, have to use field name for each equality test:
    iif(YearsOn>=2 and YearsOn<5,"9")

    But really don't need both.

    And parens are wrong

    VacEarned: iif(YearsOn<2,8, iif(YearsOn<5,9, iif(YearsOn<10,10, iif(YearsOn<15,11, iif(YearsOn<20,13, iif(YearsOn<25,15, iif(YearsOn<30,17, iif(YearsOn<35,21,0))))))))

    What should be returned after 35?

    Consider:

    VacEarned: Switch(YearsOn < 2, 8, YearsOn < 5, 9, YearsOn < 10, 10, YearsOn < 15, 11, YearsOn < 20, 13, YearsOn < 25, 15, YearsOn < 30, 17, YearsOn < 35, 21)

    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.

  4. #4
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Wouldn't that make 34 years getting 21 instead of 19?

  5. #5
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Just a slight change and it seems to work. Thanks.
    VacEarned: Switch(YearsOn < 2, 8, YearsOn < 5, 10, YearsOn < 10, 9, YearsOn < 15, 11, YearsOn < 20, 13, YearsOn < 25, 15, YearsOn < 30, 17, YearsOn < 35, 19, YearsOn >= 35, 21)

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    There was no 19 in the original equation. But glad you got it working.
    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.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by UT227 View Post
    Wouldn't that make 34 years getting 21 instead of 19?
    It was what you wanted in your original expression?
    iif(YearsOn>=30 and <35,"21")

  8. #8
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Yes. Guess all those numbers began running together. I appreciate the help.

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

Similar Threads

  1. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  2. if statement in sql statement, query builder
    By 54.69.6d.20 in forum Access
    Replies: 4
    Last Post: 09-11-2012, 07:38 AM
  3. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  4. if-then statement in vba
    By rivereridanus in forum Programming
    Replies: 3
    Last Post: 07-07-2011, 02:11 PM
  5. If statement help please
    By bgeorge12 in forum Programming
    Replies: 12
    Last Post: 06-28-2011, 08:41 PM

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