Results 1 to 3 of 3
Thread: Nulls & concatenation
2011-01-06, 15:59 #1
- 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 expressionCode:
String + NullCode:
String & Null
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]
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]).
The cool way: Employ the behavior of "null propagation" instead, and try it this way:
[FirstName] & " " & [LastName] & (", " + [Title])
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 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)Garry
2011-01-06, 16:30 #2
- Join Date
- Jun 2010
- Thanked 1,390 Times in 1,218 Posts
Very interesting . Thanks for posting.Rui
2011-01-13, 05:19 #3
- 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]
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," ,",""))
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 & "'")