Concatenate in my Sql. 2

[lars7]

Hi,
I cant get a concatenate in my Sql Statement to work.

[surname] & "" & [forename] AS EmployeeName,

I had a coma in between the quote marks and the writing turned red but it still didn't work when I removed it. The full statement is:

strSQL = "SELECT [trust staff].CHP AS Chp, [trust staff].CHPLocation AS Location, [trust staff].PAYNO AS PayNo, [trust staff].[ADDIT PAYNO] AS AdditPayNo, [surname] & "" & [forename] AS EmployeeName [trust staff].START_DATE AS StartDate, [trust staff].[GRADE START], [trust staff].[JD CODE NO] AS JdCodeNo, [trust staff].LEFT AS Leavers, [trust staff].TRANSFER as Transfers " & _
"FROM [trust staff] " & _


Does anyone know how to fix this problem.

 

[Golom]

strSQL = "SELECT [trust staff].CHP AS Chp, [trust staff].CHPLocation AS Location, [trust staff].PAYNO AS PayNo, [trust staff].[ADDIT PAYNO] AS AdditPayNo, [surname] & "" & [forename] AS EmployeeName[COLOR=red yellow],[/color] [trust staff].START_DATE AS StartDate, [trust staff].[GRADE START], [trust staff].[JD CODE NO] AS JdCodeNo, [trust staff].LEFT AS Leavers, [trust staff].TRANSFER as Transfers " & _
    "FROM [trust staff] " & _

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

something like this ?
strSQL = "SELECT [trust staff].CHP AS Chp, [trust staff].CHPLocation AS Location, [trust staff].PAYNO AS PayNo, [trust staff].[ADDIT PAYNO] AS AdditPayNo, [surname] & [!]', '[/!] & [forename] AS EmployeeName [trust staff].START_DATE AS StartDate, [trust staff].[GRADE START], [trust staff].[JD CODE NO] AS JdCodeNo, [trust staff].LEFT AS Leavers, [trust staff].TRANSFER as Transfers " & _
"FROM [trust staff] " & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Thanks,
You where both right:

strSQL = "SELECT [trust staff].CHP AS Chp, [trust staff].CHPLocation AS Location, [trust staff].PAYNO AS PayNo, [trust staff].[ADDIT PAYNO] AS AdditPayNo, [surname] & ', ' & [forename] AS EmployeeName, [trust staff].START_DATE AS StartDate, [trust staff].[GRADE START], [trust staff].[JD CODE NO] AS JdCodeNo, [trust staff].LEFT AS Leavers, [trust staff].TRANSFER as Transfers " & _
"FROM [trust staff] " & _