Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    alternative to IIF ? (2002/2000)

    Yesterday I found a alternative to using IIF when concatening a string (where you need commas between each part but don't want a load like ,,,,, if the strings were empty) but now I can't remember what it was. It had something to do with using +.

    Can anyone help?

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

    Re: alternative to IIF ? (2002/2000)

    It doesn't work with empty strings, it works with nulls. If you concatenate values into a string using the + operator, then any part of an expression that returns a null will cause the entire expression to return Null.

    [LastName] & (", " + [FirstName])

    If FirstName has a value entered, the result will be Jones, John. If there is no FirstName entered, the result will be Jones.

    Was that what you were looking for?
    Charlotte

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

    Re: alternative to IIF ? (2002/2000)

    Concatenating with + instead of the customary & results in Null if at least one of the components is Null. For example:

    <table border=1><td>LastName</td><td>FirstName</td><td>[LastName] & ", " & [FirstName]</td><td>[LastName] & (", "+[FirstName])</td><td>Blair</td><td>Tony</td><td>Blair, Tony</td><td>Blair, Tony</td><tr><td>Blair</td><td align=right>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: alternative to IIF ? (2002/2000)

    Thank you both, that's exactly what I was after (and a whole lot more)

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: alternative to IIF ? (2002/2000)

    I spoke too soon,

    I've been trying this out and it was ok when trying to add text but I was unable to add the numeric value of a particular combo in.
    I understand this so tried to use cstr to convert the number to text but this falls over if the value if null.

    I just can't get it to work - the line below is my latest attempt - with invalid use of null errors.

    strTest = (" Mile:" + (IIf(Not IsNull(cboMile.Value), CStr(Nz(cboMile.Value, "")), cboMile.Value)))

    I wanted the value to stay as it is if its Null but be converted to a string if its not.

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

    Re: alternative to IIF ? (2002/2000)

    Try

    strTest = Nz("Mile: " + cboMile.Value, "")

    If cboMile is Null, the result of "Mile: " + cboMile.Value is Null. You can't assign Null to a string value, so you need Nz. But the trick with +, and the use of Nz is mostly useful in expressions in queries and in the control source of controls on forms and reports. In VBA, I prefer to write "readable" code:

    If IsNull(cboMile) Then
    strTest = ""
    Else
    strTest = "Mile: " & cboMile
    End If

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: alternative to IIF ? (2002/2000)

    That works if the mile is Null but I get a type mismatch if it is a number. I think I've got the wrong end of the stick somewhere or is the + operator unimpressed with a genuine number?

    I think I'm better off setting variables as you suggested and building the final string from them instead of using the combo values directly.

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

    Re: alternative to IIF ? (2002/2000)

    Using + as a concatenation character is confusing to Access when a number is involved, since + is an arithmetic operator and Access tries to add the number, which leads to a type mismatch since the first element in the expression is a string.
    Charlotte

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

    Re: alternative to IIF ? (2002/2000)

    I would go with the longer If ... Then ... Else code.

Posting Permissions

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