Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2005
    Beechworth, Victoria, Australia
    Thanked 0 Times in 0 Posts
    In reponse to Helen Feddema's latest Access Watch newsletter (Concatenating Name and Address Fields - Access Archon #202), I thought I'd share this alternative which I found on the web ages ago.

    When Null is handy!

    Michael Waddell, MW Data Basics of Marshfield, Massachusetts USA.

    (The "+" in the expression
    String + Null
    is a null. But
    String & Null
    is still a string.)

    Null values in queries often complicate the presentation of data - here's a way to make nulls work for you when string concatenation is your task.

    Example: You often concatenate a person's name parts and title in a derived query column, like

    [FirstName] & " " & [LastName] & ", " & [Title]
    Result: Great, if everyone has a title, but shaggy when someone doesn't... your recordset contains values like:

    John Smith, (trailing comma, no title).

    One often-used technique is to use the IIf function to conditionally concatenate the [Title], like:

    [FirstName] & " " & [LastName] & IIf(IsNull([Title]),"",", " & [Title]).
    This works fine - unless speed and elegance are important to you.

    The cool way: Employ the behavior of "null propagation" instead, and try it this way:

    [FirstName] & " " & [LastName] & (", " + [Title])
    Notice the '+' operator within the parenthetical expression. If [Title] is null, the '+' operator forces the whole expression to a null value - canceling the irrelevant comma. Then, using the normal ampersand - '&' - to concatenate the resulting null expression to [LastName] prevents the null value from propagating through the whole string. You get: John Smith (no trailing comma).

    Obviously, this technique will work for those pesky missing middle initials, too. Try it! It's cool and it beats those tiresome 'iif..' expressions.

    Handling Your Nulls Inline

    Michael Waddell of Marshfield, Massachusetts US.

    Rather than testing for a Null value using multiple lines of code, try this method:

    For string data:

    Dim strData as String, strCriteria as String
    strData = "" & DLookUp("[SomeField]","tblSomeTable", "[CriteriaField]= """ & strCriteria & """")
    By concatenating the DLookup to a zero-length string, if [SomeField] is null, then strData will be assigned a zero-length string and you avoid a type mismatch and an Invalid use of Null error.

    By the way, the high performance way to test whether a string is empty is: Len(strData)=0 NOT: strData = ""

    For numeric data; Access/VB handles type conversion on-the-fly:

    Dim intVal as Integer, intCriteria as Integer
    intVal=0 & DMax("[NumField]","tblSomeTable","[NumField]=" & intCriteria)
    Granted, it's an odd use of the string concatenation operator, but IT WORKS!

  2. #2
    Join Date
    Jun 2010
    Thanked 1,390 Times in 1,218 Posts
    Very interesting . Thanks for posting.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Manchester, United Kingdom
    Thanked 17 Times in 16 Posts
    I like the use of "null propagation" instead of IIF. Coming from a strong typing background I've always used & not + for concatenation, now I can see where + might be useful.

    Don't forget the NZ() function (ISNULL() in T-SQL) which replaces null values with a value of your choice:

    FullName = NZ([forename],'(no forename)') & " " & [surname]
    makes the missing value very clear.

    I've suggested to Helen that a quick way to skip null values in some cases is to use replace() and trim() to strip unwanted delimiters out of the simply concatenated string. Especially as sometimes you are faced with a concatenated string created by the database developer and you need to tidy it for a report:

    strConcat = [field1] & ", " & [field2] & ", " & [field3]
    strTidy = trim(replace(" " & strConcat," ,",""))
    In this case a missing last field will leave a trailing comma, but that can be handled by another replace() or two. Usually I find the last field is mandatory anyway.

    Also, looking at the examples with their proliferation of double quotes, I make use of the fact that Access expressions allow both double and single quotes for strings (SQL only allows single, VBA only allows double). That means you can put single quotes inside double quotes to build a quoted value:

    dcount("field", "table", "[stringfield] = '" & strValue & "'")
    VBA treats the single quotes as part of the text and passes them to Access which then uses them as string delimiters.


Posting Permissions

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