Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2009
    Posts
    9

    Question Unanswered: Concatenating Fields in a Query

    Firstly forgive my complete noviceness.

    I have to concatenate fields together in a query. I have done this using the following formula:

    Field: [Field1] + [Field2] + [Field3]

    This works just fine if there is something in fields1,2 and 3, but sometimes there wont be and if there is a null field it wont concatenate the others and leaves it completely blank. I have tried playing about with IIF formulae, like i would have done in excel, but i can't get it to work and I'm not that great at coding either.

    Any help is very welcomed!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    wonder if the concat() SQL function will be of any use?
    of course you could try the concatenation operator in MS Access/JET which is &
    select column1 & column2 & column3 as myconcatenatedcolumn from mytable
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    9
    Quote Originally Posted by healdem
    wonder if the concat() SQL function will be of any use?
    of course you could try the concatenation operator in MS Access/JET which is &
    select column1 & column2 & column3 as myconcatenatedcolumn from mytable

    em.....you have slightly lost me, sorry. Tried using the concat() function but couldn't get it to work.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    nz([Field],"") for text values
    nz([Field],0]) for values (careful if you are using for sums).

    For SQL use COALESCE([Field],ValueifFieldIsNull)

    You maybe could use e.g. "<Please Enter Surname>" or something descriptive for missing data if you wish...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Mar 2009
    Posts
    9
    I tried using nz(), but was still getting an "#Error". I think there is something urther back in the other fields so will leave it for now. THank you for your help, at least i learnt something new!!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the VBA string concatenation character is "&" not "+", if you try to use a + VBA will try to add the values of those columns, if one is nul or non numeric you will have problems

    so if you are doing string / text concatenation then
    Code:
    field: field1 & field2 & field3
    or in SQL
    Code:
    select field1 & field2 & field3 as MyConcatenatedColumn from mytable
    Coalesce may be not be supported in JET.
    NZ is a function which returns another value if the column/supplied value is null

    most of this you could have found out from the help system and /or context senstivie help, havign got the intitial pointers of where to look
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    9
    Thanks! Problem solved. I have made a mental note next time to us & rather than +.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, it really amazes me that MS allowed the + to concatenate in the first place.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    i alway think of it like

    1+1 = 2
    1&1 = 11
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by angelmosue
    em.....you have slightly lost me, sorry. Tried using the concat() function but couldn't get it to work.
    That's bcause concat() is not a function in Access VBA!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StarTrekker
    Yeah, it really amazes me that MS allowed the + to concatenate in the first place.
    blame Sybase (original creators of Transact-SQL) for the same operator in SQL Server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, stupid Sybase
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I have purchased it . . . about 10 years ago and I stull use it!

Posting Permissions

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