I suspect this super simple query is giving me what i asked for not what I wanted

[Al Kramer]

Hello Access Friends

I want to concatenate three fields in my table into a new field.
When the first field in empty I do not get field2 + field3

Yeilds:

I hoped to see the last+first without the prefix

The sql looks like this:

SELECT Demo.PREFIX, Demo.FNAME, Demo.LNAME, [prefix]+" "+[fname]+" "+[lname] AS NewFld
FROM Demo;

I'm new at this & I am sure suspect I don't understand something basic to the query.

Any help appreciated.

Thanx

Al

 

[XPS35]

Use & instead of +.

 

[Isaac]

You're trying to concatenate a null. wrap [prefix] in Isnull([prefix],'')

Edit - trying to remember at the moment if IsNull is the same in Access as SQL Server - if not my code might need an adjustment.

 

[June7]

IsNull() is a VBA function call and simply returns True or False, not the same thing as SQLServer IsNull() which is actually same functionality as Access Nz() also a VBA function call. In Access IS NULL is SQL.

Concatenation using + will return Null when Null is involved (null propagation - as happens in arithmetic). Concatenation using & will return string part when Null is involved (returns Null if all inputs are Null).

Try mixing them if you want to avoid extra spaces. The +'s evaluate first.

[prefix] + " " & [fname] + " " & [lname]

I would use Nz() in this case only if wanted to return something like "name not given" when field is Null.

 

[Al Kramer]

IsNull() is a VBA function call and simply returns True or False, not the same thing as SQLServer IsNull() which is actually same functionality as Access Nz() also a VBA function call. In Access IS NULL is SQL.

Concatenation using + will return Null when Null is involved. Concatenation using & will return string part when Null is involved (returns Null if all inputs are Null).

Try mixing them if you want to avoid extra spaces. The +'s evaluate first. Assumes there is always lname value.

[prefix] + " " & [fname] + " " & [lname]

This worked perfetly, Thanx

& Vs + .... Hmmmm
Now... Where can I look / go to understand this stuff? Can you suggest a reference or tutorial?
Again
Thanx

 

[Al Kramer]

Use & instead of +.

Thank you.

 

[XPS35]

Google is your best friend. You will find sites like THIS.

 

[June7]

Google. I don't remember when or where I learned this. I revised my answer as you were reading. Might look at again. Think it pretty much covers the & Vs + question.

 

[Isaac]

Well Microsoft, please update sql server to handle the concatenation of Nulls as well as Access apparently does

 

[June7]

Well Microsoft, please update sql server to handle the concatenation of Nulls as well as Access apparently does

I think that's what COALESCE() function is for. Although is kinda annoying SQLServer requires a special function to do what Access SQL can simply do with operators.

 

[Pat Hartman]

To be clear, the & is the standard concatenation operator for VBA. The + is the standard addition operator which is used only when you don't want to propagate nulls. So the expression you needed (thanks to June) ended up having to use both to obtain the result you wanted.