Concat in SQL creates unwanted spaces

[Deutz]

Using Access 2003

I have some VBA code that creates an SQL string which concats three fields in a table with a string contained in a recordset and it outputs to another table ok except that there are a number of spaces between the Channel field and the rs!fldMetricType field. I've tried using the TRIM function as per code below but no improvement. Any help would be appreciated.



SELECT Stream & Channel & '" & Trim(rs!fldMetricType) & "' & MarketSegment as fldKey ....... etc etc


Thanks in advance

[ajetrumpet]

the one thing I notice about that is the use of single quote marks. you don't need them, and it shouldn't work the way it's written anyway. It should be:

"SELECT Stream & Channel" & Trim(rs!fldMetricType) & "MarketSegment as fldKey ....... etc etc"

and are you sure the spaces aren't coming from the other field? CHANNEL? to be sure, trim that one as well.

btw, concating with single quote marks is only ever needed in criteria clauses, with string values only.

[Deutz]

Thanks Adam

You are right; the spaces were coming from the Channel field and the SQL worked fine once I trimmed it.

[ajetrumpet]

great!