• alternative to IIF ? (2002/2000)

    Author
    Topic
    #401711

    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?

    Viewing 3 reply threads
    Author
    Replies
    • #793468

      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?

      • #793562

        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.

        • #793581

          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

          • #793608

            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.

            • #793678

              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.

            • #793679

              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.

            • #793699

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

            • #793700

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

          • #793609

            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.

        • #793582

          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

      • #793563

        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.

    • #793469

      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?

    • #793474

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

      LastName FirstName [LastName] & “, ” & [FirstName] [LastName] & (“, “+[FirstName])
      Blair Tony Blair, Tony Blair, Tony
      Blair
      • #793520

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

      • #793521

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

    • #793475

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

      LastName FirstName [LastName] & “, ” & [FirstName] [LastName] & (“, “+[FirstName])
      Blair Tony Blair, Tony Blair, Tony
      Blair
    Viewing 3 reply threads
    Reply To: alternative to IIF ? (2002/2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: