Problem with string concatenation in Microsoft Access SQL query

Question

I have a table in an Access 2016 database which contains information for several locations. Each location is stored in one of three formats:

(type 1) standard US address with house number 
(type 2) street intersection
(type 4) GPS coordinates only 
(type 3 is not used in this database).

I'm trying to concatenate the individual field values into a single string with a query, so that I can display that string in a text box on a report. The format for type 1 is

[HouseNumber],[HouseNumberSuffix],[PrefixDirectional],[StreetName],[StreetType],[Qualifier],[City]

which would look like "100 E UNION ST, SOMERSET" for row 2 in the example below.

The format for type 2 is

[PrefixDirectional],[StreetName],[StreetType],'/',[XPrefixDirectional],[XStreetName],[XStreetType]

which should look like "N CENTER AVE/E MAIN ST" for row 3 in the example below.

The format for type 3 is

[LatitudeY],"|",[LongitudeX]

which should look like "39.957384|-78.824255" for row 6 in the example below.

Types 1 and 2 are geocoded by the application producing this data, so they also include Lat and Long values (when available), but I don't need to consider those fields when concatenating those rows. In this implementation, many of the fields contain null values. Since Microsoft Access doesn't support the CONCAT_WS() SQL function, I've been wracking my brain trying to find a workaround to concatenate the strings while eliminating extra spaces for the null fields.

Here is a sample of my table:

Table: Dim_Address

|AddressID|LocationType|HouseNumber|HouseNumberSuffix|PrefixDirectional|StreetName      |StreetType|XPrefixDirectional|XStreetName|XStreetType|Qualifier|City      |LatitudeY   |LongitudeX   |
|---------|------------|-----------|-----------------|-----------------|----------------|----------|------------------|-----------|-----------|---------|----------|------------|-------------|
|1        |1           |           |                 |                 |<UNKNOWN>       |          |                  |           |           |         |          |            |             |
|12       |1           |100        |                 |E                |UNION           |ST        |                  |           |           |         |SOMERSET  |40.0092574  |-79.078380702|
|37       |2           |           |                 |N                |CENTER          |AVE       |E                 |MAIN       |ST         |         |SOMERSET  |40.008420389|-79.078610673|
|6363     |4           |           |                 |                 |                |          |                  |           |           |         |SOMERSET  |39.996243   |-79.034395   |
|9302     |2           |           |                 |                 |MARKLETON SCHOOL|RD        |                  |ROCKDALE   |RD         |         |ROCKWOOD  |39.908031106|-79.160141687|
|9725     |4           |           |                 |                 |                |          |                  |           |           |         |BERLIN    |39.957384   |-78.824255   |
|8282     |1           |222        |                 |                 |MAIN            |ST        |                  |           |           |APT 13   |MEYERSDALE|39.814387822|-79.026677269|
|55233    |1           |2110       |1/2              |                 |GRAHAM          |AVE       |                  |           |           |         |WINDBER   |40.230844268|-78.82551539 |

[AddressID], [LocationType], and [HouseNumber] are integers; [LatitudeY] and [LongitudeX] are doubles; the remaining fields are all strings.

And here is the code I'm attempting to use in my query:

SELECT
Switch(
[LocationType]=1,((CStr([HouseNumber])+' ') & ([HouseNumberSuffix]+' ') & ([PrefixDirectional]+' ') & ([StreetName]+' ') & [StreetType] & (', '+[Qualifier]) & (', '+[VenueName])),
[LocationType]=2,(([PrefixDirectional]+' ') & ([StreetName]+' ') & ([StreetType]+' ') & ('/') & ([XPrefixDirectional]+' ') & ([XStreetName]+' ') & ([XStreetType]+' ')),
[LocationType]=4,(CStr([LatitudeY]) & ' | ' & CStr([LongitudeX]))
) AS LocationConcatenation
FROM Dim_Address;

Here are the results I get when I run the query on the table above:

|LocationConcatenation          |
|-------------------------------|
|#Error                         |
|100 E UNION ST, SOMERSET       |
|#Error                         |
|#Error                         |
|#Error                         |
|#Error                         |
|222 MAIN ST, APT 13, MEYERSDALE|
|2110 1/2 GRAHAM AVE, WINDBER   |

It works exactly as expected for type 1, except for row 1. On another post in this forum (CONCAT equivalent in MS Access), someone suggested that using + for concatenation would create empty strings if used to concatenate a string with another null, so I tried that, but Row 1 is still giving me grief. For types 2 or 4, the query doesn't work at all. Could anyone shed some light on where I'm making the mistake? I'm fairly familiar with SQL but am frustrated with the limited way that Access supports it. (BTW, <UNKNOWN> is used by the program that created this data as a placeholder for an address by default if an actual address is not entered by the user, so it appears in many records in a related table).

For clarification, if a value exists in a field such as `[HouseNumber]`, then I want to append that value, followed by a single space, to the resulting string to make it readable. If the value is NULL, then I don't want to append anything to the string. So, if `[HouseNumber]` is "100", `[HouseNumberSuffix]` is NULL, `[PrefixDirectional]` is "E" and `[StreetName]` is "UNION", I want the resulting string to be "100 E UNION" and not "100EUNION" or "100 E UNION" (with two spaces between the 100 and E). — Skippy687, Aug 02 '19 at 17:10

Gordon Linoff · Accepted Answer · 2019-08-05T12:04:56.603

2

You can implement the equivalent of concat_ws() in MS Access using nz(), ltrim() and conditional logic:

select Switch(LocationType = 1,
              ltrim(nz(" " + CStr(HouseNumber), "") +
                    nz(" " + HouseNumberSuffix, "") + 
                    nz(" " + PrefixDirectional, "") +
                    nz(" " + StreetName, "") +
                    nz(" " + StreetType, "") + ", " &
                    nz(" " + Qualifier, "") + ", "
                    nz(" " + VenueName, "")
                   ),
              . . .
              )

edited Aug 05 '19 at 12:04

answered Aug 02 '19 at 16:54

Gordon Linoff

1,242,037
58
646
786

In your example, is there a difference in using double quotes instead of single quotes for string delimiters? I searched several sites for a reference on the query syntax, and found examples of both. – Skippy687 Aug 02 '19 at 16:59
@Skippy687 . . . I associate double quotes with being the standard for MS Access (in contrast to standard SQL and other databases). I think you can use either, if used consistently. – Gordon Linoff Aug 02 '19 at 17:36
@GordonLinoff: Nz is of no use here: `Nz(" " & HouseNumberSuffix, "")`. Did you mean: `Nz(" " + HouseNumberSuffix, "")`? – Gustav Aug 02 '19 at 18:22
@Gustav . . Why is `nz()` of no use? The expression `" " & HouseNumberSuffix` should return `NULL` if `HouseNumberSuffix` is `NULL`. The `nz()` then returns an empty string. – Gordon Linoff Aug 02 '19 at 21:01
@GordonLinoff: Not with the ampersand; but with the _plus_ it will. – Gustav Aug 03 '19 at 15:52
Thank you for the answer. Unfortunately I'm still getting the error. I think part of the problem is that apparently the CStr() function can't handle any input that evaluates to NULL, so that's probably why I'm getting the error for rows 1, 3, and 5 in the sample table. I still can't figure out why I'm getting an error for rows 4 and 6, though. I'm wondering, does the Access engine try to evaluate all three of the CStr() functions first before evaluating the expressions contained in the Switch() function? – Skippy687 Aug 05 '19 at 02:35
Then replace `CStr([SomeValue])` with `CStr("" & [SomeValue])`, or `Str([SomeValue])`, or `LTrim(Str([SomeValue]))` – Gustav Aug 05 '19 at 10:41

Problem with string concatenation in Microsoft Access SQL query

1 Answers1