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).

Answer 1

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, "")
                   ),
              . . .
              )