Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selections (A2003)

    Hi All,
    I am looking for a better way to use an IF statement then using something like:
    "if intAdmin = 1 or intAdmin = 2 or intAdmin = 3......... then"

    How can I make the if statement look for 8 different numbers without using all those "OR"s?

    Thanks,
    Mark

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Selections (A2003)

    In VBA, you can use Select Case:

    Select Case intAdmin
    Case 1 To 5, 12, 37
    ' some code here
    ...
    Case 6, 9 To 11
    ' some other code here
    ...
    Case Else ' all other values
    ' more code
    ...
    End Select

  4. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selections (A2003)

    Thanks Hans!

  5. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selections (A2003)

    OK, one other thing.... How would you handle something where you want to select a rowsource for a combo box through code, looking for a list of let's say 8 values? Right now I use something like "select * from someTable where tech_id = 1 or tech_id = 2 or tech_id = 3......... Is there a better way to do this?

    Thanks,
    Mark

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Selections (A2003)

    You can use

    SELECT * FROM SomeTable WHERE TechID Between 1 And 8

    or

    SELECT * FROM SomeTable WHERE TechID In (1, 2, 4, 7, 11, 16, 22, 29)

  7. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selections (A2003)

    Great!

  8. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selections (A2003)

    Is it possible to do the same thing with text? Like......... In ("SomeText","Someother Text")....

    Thanks,
    Mark

  9. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,521
    Thanks
    0
    Thanked 22 Times in 22 Posts

    Re: Selections (A2003)

    Try
    WHERE Text in ("SomeText","Someother Text")

    You cannot use the clause LIKE in this sense. LIKE is used with a wildcard.

  10. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selections (A2003)

    Thanks!

  11. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selections (A2003)

    Hi,
    How would you handle a null then? I tried "Case Null" and it came back with invalid use of null.

    Thanks,
    Mark

  12. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: Selections (A2003)

    Null is not a value, it signifies the absence of a value. You can't test for null in Select Case directly.

    One option is to handle it separately:

    If IsNull(Me.txtSomething) Then
    ...
    Else
    Select Case Me.txtDirection
    Case "Left"
    ...
    Case "Right"
    ...
    End Select
    End If

    Another option is to handle it in the Case Else part. This is mostly useful if you can handle all possible values in the other Case parts:

    Select Case Me.txtAmount
    Case Is > 0
    ...
    Case 0
    ...
    Case Is < 0
    ...
    Case Else
    ' Anything else, including Null
    ...
    End Select

Posting Permissions

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