SQL Query Select to Return All Fields even if empty? 1

[KARR]

All,

I am working with a simply query below:

SELECT LAST_NAME + FIRST_NAME + MIDDLE_NAME + SUFFIX AS NOTES
FROM TEST;

Which is supposed to return a users Last Name, First Name, Middle Name and Suffix in one column called NOTES.

The problem I am having is that the query is return only values that meet all 4 criteria. Is there a way to tell Access/SQL Query to return any values it may find?

Sample Table:

First_Name Middle_Name Last_name SUFFIX

John S Smith II
Mary Khan
Kim Fett Sr.

Based on the query above it will only return John S Smith II and leave off the other two. And what I would like to do is return any and all information, thus returing John, Mary and Kim

Any help would be greatly appreciated.

Note: I accidentally posted this in the SQL forum, it should be here as I am using MS Access Query and not SQL Server.

Thanks

Rob

 

[Golom]

Try this
[blue][tt]
SELECT ( NZ(LAST_NAME) +
NZ(FIRST_NAME) +
NZ(MIDDLE_NAME) +
NZ(SUFFIX) ) AS NOTES
FROM TEST;
[/tt][/blue]

 

[KARR]

Golom,

I tried about the above query and the results were either 0 or #error.

Rob

 

[Golom]

OK. Change your "+" to "&".

 

[KARR]

That did it, thanks!
Rob