Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    182

    Unanswered: Concatenate string

    I am trying to concatenat 2 strings in adp at the query grid.
    This is what I have
    SELECT name + ' ' + Surname AS Name, cid
    FROM tblsupcon

    which works but only if there is data in both fields, sometimes only the name field will be filled. I nedd both results in one field as this data is populating another field so I cannot just add name to the query.

  2. #2
    Join Date
    May 2004
    Location
    The Netherlands
    Posts
    52
    Maybe the nz function will work:
    Code:
    SELECT nz(name,"") + ' ' + nz(Surname,"") AS Name, cid
    FROM tblsupcon
    Last edited by Robje73; 09-10-04 at 10:27.
    A good developer is a lazy developer - don't be to lazy, share your knowledge

  3. #3
    Join Date
    Apr 2004
    Posts
    182
    Quote Originally Posted by Robje73
    Maybe the nz function will work:
    Code:
    SELECT nz(name,"") + ' ' + nz(Surname,"") AS Name, cid
    FROM tblsupcon
    This gives the error
    'NZ' is not a recognised function name.
    This seems to happen a lot with .adp, the synatx for functions is different but it doesn't tell you anywhere what it should be.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's because when you use an .adp, you have to use T-SQL as opposed to JET:

    SELECT ISNULL(name,"") + ' ' + ISNULL(Surname,"") AS name, cid
    FROM tblsupcon

    remember, SQL queries in an adp are not processed by Access.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2004
    Posts
    182
    Quote Originally Posted by Teddy
    That's because when you use an .adp, you have to use T-SQL as opposed to JET:

    SELECT ISNULL(name,"") + ' ' + ISNULL(Surname,"") AS name, cid
    FROM tblsupcon

    remember, SQL queries in an adp are not processed by Access.
    Works lovely Teddy but just for information for others I had to replace the "" with ' ' .
    Cheers

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by livvie
    Works lovely Teddy but just for information for others I had to replace the "" with ' ' .
    Cheers
    Although it works I was taught when learning to try to stay away from the (+) signs to concatenate and use the (&) instead. Not sure what could possibly go wrong but was a suggestion to me, and I pass along to whoever is interested as well.

    everyone have a nice day,
    Bud

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Bud
    Although it works I was taught when learning to try to stay away from the (+) signs to concatenate and use the (&) instead. Not sure what could possibly go wrong but was a suggestion to me, and I pass along to whoever is interested as well.

    everyone have a nice day,
    Bud
    That's JET-SQL vs. T-SQL again. You'll get an operand error if you try to use "&" with strings.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Quote Originally Posted by Teddy
    That's JET-SQL vs. T-SQL again. You'll get an operand error if you try to use "&" with strings.
    Great Teddy,

    But just which one am I using inside A2K? This is the one I have the (&) in and no problems so far. Which one gives the error? Just trying to learn all I can and this never crossed my mind. Found out there are several flavors of SQL.

    Bud

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •