Results 1 to 4 of 4
  1. #1
    denver1717 is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2012
    Posts
    16

    Unhappy iif statement using a range (between)

    ACCOUNT
    400001
    400002
    400003
    400004
    400005
    400006
    400008
    400012
    400013
    400075
    400076
    400077
    420001
    420002
    420003
    420004
    420005
    420006
    420008
    420010
    420012
    420013
    420075
    420076
    420077


    Question 1: What is the function that converts text to a number? I am working with the above, which I want to leave in the original table as text, but I would like to set up a query that contains both the text version, and a formula that converts the text to a number format so I can build an expression (I am thinking I probably can't use greater than/less than on a text format number).

    Question 2: I want to build an column called TYPE using an if statement that states if the ACCOUNT is > 400000 or < 400099, assign a value of "IP", and if the ACCOUNT is <420000 or <420099. I would also like to include in this expression if the ACCOUNT does not fall in these ranges, return the text value "Error".



    I built the following, but Access hates it:
    TYPE: IIf([ACCOUNT]>=400000 And [ACCOUNT]<=400099,"IP",IIf([ACCOUNT]>=420000 And [Lawson]![ACCOUNT]<=420099,"OP","Error"))

    Thanks for any help!

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,466

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,994

    Question 2 answer

    "Type" is a reserved word in Access and shouldn't be used for object names (plus it is not very descriptive - "Type" of what??). A better name might be "AcctType".

    What is "[Lawson]![ACCOUNT]?

    I created a table and a query. For the AcctType, you cannot use the "Account" (text values) in the IIF() statement - use the column that is converted to a number.
    SQL for query:
    Code:
    SELECT Account.ACCOUNT, Val([ACCOUNT]) AS AcctVal, IIf([AcctVal]>=400000 And [AcctVal]<=400099,"IP",IIf([AcctVal]>=420000 And [AcctVal]<=420099,"OP","Error")) AS AcctType
    FROM Account;
    Query result:
    ACCOUNT AcctVal AcctType
    400001 400001 IP
    400002 400002 IP
    400003 400003 IP
    400004 400004 IP
    400005 400005 IP
    400006 400006 IP
    400008 400008 IP
    400012 400012 IP
    400013 400013 IP
    400075 400075 IP
    400076 400076 IP
    400077 400077 IP
    420001 420001 OP
    420002 420002 OP
    420003 420003 OP
    420004 420004 OP
    420005 420005 OP
    420006 420006 OP
    420008 420008 OP
    420010 420010 OP
    420012 420012 OP
    420013 420013 OP
    420075 420075 OP
    420076 420076 OP
    420077 420077 OP
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    denver1717 is offline Novice
    Windows XP Access 2000
    Join Date
    Oct 2012
    Posts
    16

    Thumbs up Steve you are an evil genius THANK YOU! (it worked :)

    Awesome, thank you for taking the time to help me out!! Did not know that about "Type" in Access, either - will be sure to use naming conventions similar to the examples you used.

    (ps - Lawson was the name of the table I was working with)


    Quote Originally Posted by ssanfu View Post
    "Type" is a reserved word in Access and shouldn't be used for object names (plus it is not very descriptive - "Type" of what??). A better name might be "AcctType".

    What is "[Lawson]![ACCOUNT]?

    I created a table and a query. For the AcctType, you cannot use the "Account" (text values) in the IIF() statement - use the column that is converted to a number.
    SQL for query:
    Code:
    SELECT Account.ACCOUNT, Val([ACCOUNT]) AS AcctVal, IIf([AcctVal]>=400000 And [AcctVal]<=400099,"IP",IIf([AcctVal]>=420000 And [AcctVal]<=420099,"OP","Error")) AS AcctType
    FROM Account;
    Query result:

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

Similar Threads

  1. 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
  2. Replies: 1
    Last Post: 08-08-2012, 02:02 PM
  3. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  4. Date range
    By eacollie in forum Queries
    Replies: 7
    Last Post: 06-05-2011, 03:38 PM
  5. Replies: 2
    Last Post: 11-25-2010, 11:01 AM

Tags for this Thread

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 - Senior Forums