Type mismatch error when concatenating fileds and assigning to Row source in Access

Question

I have a form with list control named lstCustomer. I am passing rowsource method a string as shown below

me.lstCustomer.RowSource = lstSQL

The lstSQL is being specified before assigning to rowSource method.

lstSQL = "SELECT FullName, City & " - " & Region AS Location FROM tblContact ORDER BY FullName;"

This is produicing an error Type Mismatch

When I am not using concatenated column, I am not getting this error. I tried the below code (this does not produces the error)

lstSQL = "SELECT FullName, City, Region FROM tblContact ORDER BY FullName;"

I want to show city and region in a single column. Please point out my mistake. Thanks in advance.

Answer 1

Please use single quotes ' instead of " in the query:

SELECT FullName, City & ' - ' & Region AS Location 
FROM tblContact ORDER BY FullName;

Answer 2

lstSQL = "SELECT FullName, City & ' - ' & Region AS Location FROM tblContact ORDER BY FullName;"

Two things: inside your quotes you have to use single quotes or an escaping character. Otherwise, VBA confuses where your literal string starts and stops. The double quotes mean take everything in the middle as a literal. The single quotes inside the double quotes are the literals for when the SELECT statement is actually run.

You're getting the type mismatch error because you're effectively trying to subtract two strings here, and you can't really add or subtract strings.

Also, you should have an alias for a computed field. That's where the AS operator comes into play in the above answer.