Results 1 to 5 of 5

Thread: If statement

  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Red face Unanswered: If statement

    Hi,

    Can someone pls. assist me with a query I would like to write in Access. The objective of the query is to categorize yearly data into specific term buckets.



    Examples noted below:
    Term= 3.8 yrs >> Expected result= "< 5 yrs"
    Term= 7.9 yrs >> Expected result= "10 yrs"

    I wrote the following query but it does not work:
    IIf(Is Null ([Term])=Is Null, " ", (IIf([Term]<=5), "< 5 yrs",(IIf([Term]>5 AND ([Term])<=7),"7 yrs", (IIf([Term]>7 AND ([Term])<=10),"10 yrs", (IIf([Term]>10 AND ([Term])<=15),"15 yrs","> 15 yrs"))

    I appreciate your assistance in this matter.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would go with a lookup table instead.

    Any reason you can't do that?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Hi Teddy,

    Can you pls. elaborate on the lookup table? Are you referring to creating a seperate table in Access that would contain the term buckets and join this table with the original table?

    Thank you
    Last edited by maldonadocj; 10-12-09 at 19:25.

  4. #4
    Join Date
    Sep 2009
    Posts
    11
    When I count the round brackets in your expression, I got 13 open and 10 closed. I suspect you should have the same number each way.

    If you write it out on a bit of paper, and for each open round bracket, label it with a letter (so, first one is a, second one is b...), and then find what you believe is the closing round bracket for a and label it a, and the same for b and so on, you should see where it is out of step. The format of IIF is IIF(expr,truepart,falsepart) - balanced round brackets.

    I hope this is some help to you.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by maldonadocj View Post
    Hi Teddy,

    Can you pls. elaborate on the lookup table? Are you referring to creating a seperate table in Access that would contain the term buckets and join this table with the original table?

    Thank you
    Precisely.

    This approach would also grant you better flexibility for reporting as you can sort/group/sum/etc on those buckets while getting friendly names at the same time "for free".
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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