Wednesday, February 13, 2008 11:37 AM
Wednesday, February 13, 2008 11:39 AM
SELECT CONVERT(nvarchar, id) + N'|||' + nvarcharColumn
Wednesday, February 13, 2008 11:58 AMModeratorTo concatenate a Numeric or Date column you need to convert it to VARCHAR or NVARCHAR. You can use the CONVERT() or CAST() functions to do that.
SELECT CONVERT(VARCHAR(20), NumberColumn) + '|' + CAST(DateColumn AS VARCHAR(20))
The first column uses CONVERT() function and the second uses CAST() function.In most of the cases, you can use either of them.
Wednesday, February 13, 2008 12:02 PMModeratorThe MSAccess equivalent of CONVERT(VARCHAR, NumberColumn) is CStr(NumberColumn)
Wednesday, February 13, 2008 12:05 PM
Wednesday, February 13, 2008 12:08 PM
Just to follow up on Jacob's comment I remembered that you will need to add a coalesce or ISNULL round each column if they are nullable as any string concatenated with null results in NULL.
To be honest it is a long time since I looked at MS Access and, therefore, cannot say whether there is a single SQL statement which would satisfy both SQL Server and MS Access. I believe that the more recent versions of MSAccess use SQL Server under the hood but I may be wrong.