Concatenate numeric and text fields

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

Guest

Hi:

Can you please tell me how I combine 1 numeric field and 5 text fields:
this does not work:

SELECT
CStr([org_unit]+"-"+([ccy])+"-"+([account])+"-"+([sub])+"-"+([prod])+"-"+([cust])) AS [Key]
FROM Adjustement;

Thanks,

Dan
 
Use & rather than +.

The main difference is that + propagates the NULL while & does not:


? Null + ( "aaa" + "bbb" )
' nothing printed for the result which is null


? Null & ( "aaa" + "bbb" )
aaabbb


Note that it can be useful to combine both, as in:


? LastName & ( ", " + FirstName )
Blow, Joe

but if FirstName is null, the result would be:

Blow


(and not

Blow,

)



Hoping it may help,
Vanderghast, Access MVP
 
Try using "&" instead of "+".

One problem with using + for concatenation is that if any value is Null then
the entire concatenated value becomes NULL. Second, if you use the plus for
concatenation and your calculation includes a number field then Access will
try to ADD the values. If the text fields contain non-number characters
then Access will error since it cannot add "Albacore" to 1.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks a lot Michael/John!

Dan

John Spencer said:
Try using "&" instead of "+".

One problem with using + for concatenation is that if any value is Null then
the entire concatenated value becomes NULL. Second, if you use the plus for
concatenation and your calculation includes a number field then Access will
try to ADD the values. If the text fields contain non-number characters
then Access will error since it cannot add "Albacore" to 1.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

D said:
Hi:

Can you please tell me how I combine 1 numeric field and 5 text fields:
this does not work:

SELECT
CStr([org_unit]+"-"+([ccy])+"-"+([account])+"-"+([sub])+"-"+([prod])+"-"+([cust]))
AS [Key]
FROM Adjustement;

Thanks,

Dan
Click to expand...
Click to expand...
 
Back
Top