Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIf question? (Access 2000)

    If "obl" is blank, then pull info from "rr" IIf([obl]=" ",[RR],[obl]) This expression does not seem to work. But, if I use IIf([obl]<>" ",[obl],[RR]) then it works.

    Why?

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

    Re: IIf question? (Access 2000)

    " " is not blank, it is a string consisting of a single space. Use IsNull instead to test if a field is blank:

    <code>=IIf(IsNull([obl]),[RR],[obl])</code>

    As an alternative, you can use the Access-specific Nz function. Nz returns the first argument, unless that is Null (blank), then it returns the second argument. The expression is

    <code>=Nz([obl],[RR])</code>

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: IIf question? (Access 2000)

    >>Use IsNull instead to test if a field is blank<<

    Hans,

    I don't entirely agree with this. It really depends on one's definition of "blank". Is it a zero-length string or is it Null? Or both?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIf question? (Access 2000)

    You're correct. But if you leave an Access text field empty, or clear its contents, it is Null, not a zero-length string. To get a zero-length string, you must enter <code>""</code> (and if the Allow Zero Length property is No, you can't do that) or import data from another application.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: IIf question? (Access 2000)

    Again, you have to know what you are dealing with. If the [obl] used in the original message was a defined by "Dim obl as string", then it's value is "", not null; and you can't "clear" it by setting it to null.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: IIf question? (Access 2000)

    In the first place, the OP used <code>" "</code>, not <code>""</code>.
    In the second place, I don't think that obl is a variable in VBA code. Since there are square brackets around it, it must be a field or a control. Text fields can be null, as can be text boxes, even if bound to a text field.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: IIf question? (Access 2000)

    Well actually you can use brackets around a VBA field.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IIf question? (Access 2000)

    Yes, Mark, but that was Hans's point. You can't use brackets around a variable, only around a field or control.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIf question? (Access 2000)

    In fact, you can put brackets around a variable, as Mark pointed out. I didn't know that, and I suspect that very few people do.

    The following code is valid VBA (not just in Access):
    <code>
    Sub TestBrackets()
    Dim intVar As Integer
    [intVar] = 37
    Debug.Print [intVar]
    End Sub
    </code>
    (You can't use the brackets in the declaration)

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: IIf question? (Access 2000)

    Try it yourself. I was able to do it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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