Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    43

    Unanswered: sql in iif statement

    hello all, again...

    working in a query in Access 2000 and creating a new field called Status with an iif statement. it's working, except that i have some values that are above the high end of number6.

    so i want to change this last one from a between statement to a greater than or equal to. problem is: when i do that, i get an invalid syntax error, stating that i may have entered an operand w/o an operator.

    Code:
    Status: IIf(IsNull([value]) Or [value] Between [number1] And [number2],"Red",IIf([value] Between [number3] And [number4],"Yellow",IIf([value] Between [number5] And [number6],"Green")))
    anyone know the correct verbiage to use?
    Mos

  2. #2
    Join Date
    Jun 2005
    Posts
    43
    actually, i just changed it to

    Code:
    Status: IIf(IsNull([value]) Or [value] Between [number1] And [number2],"Red",IIf([value] Between [number3] And [number4],"Yellow","Green"))
    is this correct? the way i'm reading this, it's:

    - if value is >number1 and <number2 = red
    - if value is >number3 and <number4 = yellow
    - all else = green

    is that right?
    Mos

  3. #3
    Join Date
    Jun 2005
    Posts
    43
    i just ran a query comparing this code w/the previous version, and everything is good.

    thx!
    Mos

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can also do this...
    IIf(IsNull([value]) Or [value] Between [number1] And [number2],"Red",IIf([value] Between [number3] And [number4],"Yellow","Green"))
    to...
    If IsNull(Me!MyFieldValue) Then
    MsgBox "Red"
    End If
    Select Case Me!MyFieldValue
    Case 1, 2
    MsgBox "Red"
    Case 3, 4
    MsgBox "Yellow"
    Case Is > 4
    MsgBox "Green"
    End Select

    but your IIF select statement will also work. I used the Select Case in this example to just show another way utilizing the Select Case statement which is sometimes easier to diagnose than a multiple conditional IIF statement.
    Last edited by pkstormy; 09-28-07 at 23:20.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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