Switch Statement

[comfygringo]

This code works fine without the switch statement. What am I doing wrong?

 DoCmd.RunSQL "SELECT Salutation.Salutation, valid_numbers.[first name], valid_numbers.[last name], ([house number] & Chr(32) & " _        & "[street name]) AS address, city, st, zip, (security.[first name] & " _
        & "Switch(Len([Middle Initial]) = 0, "", Len([Middle Initial]) <> 0, Chr(32) & [Middle Initial] & '.') & Chr(32) & " _
        & "security.[last name]) AS Agent into Letters " _
        & "FROM (valid_numbers INNER JOIN salutation ON Salutation.ID = Valid_Numbers.Salutation) INNER JOIN security ON security.userid = valid_numbers.userid " _
        & "WHERE [send letter] = true and [letter sent date] is null and licensed = True and valid_numbers.userid = '" & myUserID & "'"

[June7]

I use literal space instead of Chr(32).

Try replacing inner quote marks with apostrophes, like you did with '.'.

"Switch(Len([Middle Initial]) = 0, '', Len([Middle Initial]) <> 0, ' ' & [Middle Initial] & '.')" & " " _

or maybe (I never tried this but I've seen)
"Switch(Len([Middle Initial]) = 0, """", Len([Middle Initial]) <> 0, "" "" & [Middle Initial] & ""."")" & " " _

But with only two possible values, why use Switch?

"IIf(Len([Middle Initial])=0, '', ' ' & [Middle Initial] & '.')"

Why is [Middle Initial] zero length? What is actually in the field - null or empty string? I don't allow empty strings in tables.

"IIf(IsNull([Middle Initial]),'',' ' & [Middle Initial] & '.')"

[comfygringo]

I got it to work. The code: & "Switch(Len([Middle Initial]) = 0, "", Len([Middle Initial]) <> 0, Chr(32) & [Middle Initial] & '.') needed to be changed to & "Switch(Len([Middle Initial]) = 0, '', Len([Middle Initial]) <> 0, Chr(32) & [Middle Initial] & '.')

Subtle but took me forever to find.

[June7]

Did you review my complete post? I might have edited it after you posted.

[June7]

My point is Switch is actually more complicated approach for this situation.