combine fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a SP and I've combind two fields

Address+ ' ' +AppNum AS [Address]. It works fine as long as both flieds have
a value, if for example APPNum doesn't, I get blank Address field.Do I have
to use IsNULL(AppNum,address) or even Case? Why can't I get just address
displayed when AppNum is blank?I'm re-writing a mdb to adp, and this worked
fine in access.(Address)& " " &(Appnum)
 
Use & operator instead of + operator:

Address & ' ' & AppNum AS [Address]

+ operator perpetuates Null values through the concatenation; & does not. In
fact, if you don't want the trailing space when AppNum is Null, then use
this:

Address & (' ' + AppNum) AS [Address]
 
On SQL-Server, concatenating a string with a null value give a null value.
Same thing also if you try a mathematical operation with a null value.

There is somewhere on SQL-Server an option to change this but the best way
is usually to use the isNull() function to return the empty string.
However, in your case, you must also take the precaution of not having a
blank caracter where one or both of you fields are null; so the best
solution in your case is probably using a Case.

If you want to use the IsNull function, one solution could be:

isNull (Address+ ' ' +AppNum, isNull (Address, AppNum)) as FullAddress

or, if you want to make sure that no Null are returned, which might be
usefull in some case:

isNull (Address+ ' ' +AppNum, isNull (Address, isNull (AppNum, ''))) as
FullAddress

Obviously, a Case would be easier to read here and will also give a better
performance because you won't try to make a concatenation with a null value
before testing for the null value. By the way, don't take the bad habit of
using the name of a field as the name of the result of an expression.

S. L.
 
I get an error msg when I use
Address & ' ' & AppNum AS [Address]

ADO Error:Invalid operator for data type.

Ken Snell said:
Use & operator instead of + operator:

Address & ' ' & AppNum AS [Address]

+ operator perpetuates Null values through the concatenation; & does not. In
fact, if you don't want the trailing space when AppNum is Null, then use
this:

Address & (' ' + AppNum) AS [Address]
--

Ken Snell
<MS ACCESS MVP>

Armin_A said:
I have a SP and I've combind two fields

Address+ ' ' +AppNum AS [Address]. It works fine as long as both flieds have
a value, if for example APPNum doesn't, I get blank Address field.Do I have
to use IsNULL(AppNum,address) or even Case? Why can't I get just address
displayed when AppNum is blank?I'm re-writing a mdb to adp, and this worked
fine in access.(Address)& " " &(Appnum)
Click to expand...
Click to expand...
 
Thanks.I just wanted to confirm that I need to use either IsNUll or Case.I
will go with Case.

Thank you all for your help

Armin

Sylvain Lafontaine said:
On SQL-Server, concatenating a string with a null value give a null value.
Same thing also if you try a mathematical operation with a null value.

There is somewhere on SQL-Server an option to change this but the best way
is usually to use the isNull() function to return the empty string.
However, in your case, you must also take the precaution of not having a
blank caracter where one or both of you fields are null; so the best
solution in your case is probably using a Case.

If you want to use the IsNull function, one solution could be:

isNull (Address+ ' ' +AppNum, isNull (Address, AppNum)) as FullAddress

or, if you want to make sure that no Null are returned, which might be
usefull in some case:

isNull (Address+ ' ' +AppNum, isNull (Address, isNull (AppNum, ''))) as
FullAddress

Obviously, a Case would be easier to read here and will also give a better
performance because you won't try to make a concatenation with a null value
before testing for the null value. By the way, don't take the bad habit of
using the name of a field as the name of the result of an expression.

S. L.

Armin_A said:
I have a SP and I've combind two fields

Address+ ' ' +AppNum AS [Address]. It works fine as long as both flieds
have
a value, if for example APPNum doesn't, I get blank Address field.Do I
have
to use IsNULL(AppNum,address) or even Case? Why can't I get just address
displayed when AppNum is blank?I'm re-writing a mdb to adp, and this
worked
fine in access.(Address)& " " &(Appnum)
Click to expand...
Click to expand...
 
I believe you can change the SET_CONCAT_NULL_YIELDS_NULL off to solve the
adding of nulls issue.


Jamie


Sylvain Lafontaine said:
On SQL-Server, concatenating a string with a null value give a null value.
Same thing also if you try a mathematical operation with a null value.

There is somewhere on SQL-Server an option to change this but the best way
is usually to use the isNull() function to return the empty string.
However, in your case, you must also take the precaution of not having a
blank caracter where one or both of you fields are null; so the best
solution in your case is probably using a Case.

If you want to use the IsNull function, one solution could be:

isNull (Address+ ' ' +AppNum, isNull (Address, AppNum)) as FullAddress

or, if you want to make sure that no Null are returned, which might be
usefull in some case:

isNull (Address+ ' ' +AppNum, isNull (Address, isNull (AppNum, ''))) as
FullAddress

Obviously, a Case would be easier to read here and will also give a better
performance because you won't try to make a concatenation with a null
value before testing for the null value. By the way, don't take the bad
habit of using the name of a field as the name of the result of an
expression.

S. L.

Armin_A said:
I have a SP and I've combind two fields

Address+ ' ' +AppNum AS [Address]. It works fine as long as both flieds
have
a value, if for example APPNum doesn't, I get blank Address field.Do I
have
to use IsNULL(AppNum,address) or even Case? Why can't I get just address
displayed when AppNum is blank?I'm re-writing a mdb to adp, and this
worked
fine in access.(Address)& " " &(Appnum)
Click to expand...
Click to expand...
 
....or more correctly, it will solve the concatenation issue, not the
"adding" issue. Incidentally the correct syntax is

SET CONCAT_NULL_YIELDS_NULL OFF

Apologies.

This will return the part of the string the that is not null.

Jamie

Jamie Richards said:
I believe you can change the SET_CONCAT_NULL_YIELDS_NULL off to solve the
adding of nulls issue.


Jamie


Sylvain Lafontaine said:
On SQL-Server, concatenating a string with a null value give a null
value. Same thing also if you try a mathematical operation with a null
value.

There is somewhere on SQL-Server an option to change this but the best
way is usually to use the isNull() function to return the empty string.
However, in your case, you must also take the precaution of not having a
blank caracter where one or both of you fields are null; so the best
solution in your case is probably using a Case.

If you want to use the IsNull function, one solution could be:

isNull (Address+ ' ' +AppNum, isNull (Address, AppNum)) as FullAddress

or, if you want to make sure that no Null are returned, which might be
usefull in some case:

isNull (Address+ ' ' +AppNum, isNull (Address, isNull (AppNum, '')))
as FullAddress

Obviously, a Case would be easier to read here and will also give a
better performance because you won't try to make a concatenation with a
null value before testing for the null value. By the way, don't take the
bad habit of using the name of a field as the name of the result of an
expression.

S. L.

Armin_A said:
I have a SP and I've combind two fields

Address+ ' ' +AppNum AS [Address]. It works fine as long as both flieds
have
a value, if for example APPNum doesn't, I get blank Address field.Do I
have
to use IsNULL(AppNum,address) or even Case? Why can't I get just address
displayed when AppNum is blank?I'm re-writing a mdb to adp, and this
worked
fine in access.(Address)& " " &(Appnum)
Click to expand...
Click to expand...
Click to expand...
 
Sorry... I was unaware of SQL server differences.

--

Ken Snell
<MS ACCESS MVP>

Armin_A said:
I get an error msg when I use
Address & ' ' & AppNum AS [Address]

ADO Error:Invalid operator for data type.

Ken Snell said:
Use & operator instead of + operator:

Address & ' ' & AppNum AS [Address]

+ operator perpetuates Null values through the concatenation; & does not. In
fact, if you don't want the trailing space when AppNum is Null, then use
this:

Address & (' ' + AppNum) AS [Address]
--

Ken Snell
<MS ACCESS MVP>

Armin_A said:
I have a SP and I've combind two fields

Address+ ' ' +AppNum AS [Address]. It works fine as long as both
Click to expand...
flieds
have
a value, if for example APPNum doesn't, I get blank Address field.Do I have
to use IsNULL(AppNum,address) or even Case? Why can't I get just address
displayed when AppNum is blank?I'm re-writing a mdb to adp, and this worked
fine in access.(Address)& " " &(Appnum)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top