& or + to join fields/text?

[myNameIsRon]

Hi, Please refresh my memory... What the difference is between using & or + to join fields with text. SELECT FirstName + ' ' + LastName AS FullName FROM table or SELECT FirstName & ' ' & LastName AS FullName FROM table thanks, Ron

[Ashfield]

myNameIsRon wrote:

SELECT FirstName + ' ' + LastName AS FullName FROM table

This works

myNameIsRon wrote:

SELECT FirstName & ' ' & LastName AS FullName FROM table

This doesn't, you get Invalid operator for data type. Operator equals boolean AND, type equals varchar.

Bob Ashfield Consultants Ltd

[myNameIsRon]

Hmmm, Both will work for me. I'm using a MS Access db with OleDbCommand (C#) Ron

[Michael Potter]

Both do not work on SQL Server. Access has its own brand of SQL and does a lot of wierd stuff.

[Ashfield]

My mistake, I assumed you were using SQL Server - or maybe yours for not specifying :) Anyway, as Micheal has said, Access has its own rules!

Bob Ashfield Consultants Ltd

[myNameIsRon]

Thanks guys, I didn't realize it was specific to Access SQL. I did some testing, and here it is. If your field is NULL, and you're using '+', the join is ignored. SELECT ID_Field + ' - ' & NameField FROM table ID_Field not null: 3433 - Bob Smith ID_Field null: Bob Smith SELECT ID_Field & ' - ' & NameField FROM table ID_Field not null: 3433 - Bob Smith ID_Field null: - Bob Smith Ron

[Sgg245]

+ return NULL if any one of the field contains NULL value. Otherwise both more or less returns same results. :)

Regards SG