Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    4 memo fields into 1 (A2k2 (as 2k) SP2)

    Hi All,
    In order to do a report I have created a new tblTemp, sorted and inserted (INSERT INTO) data from 4 other tables (each has the same fk) into it, set the RecordSource of the report to tblTemp and then go to Print Preview. This is done from a Print Report command button. My problem is that each of these 4 other tables has a Comments memo field that needs to be joined or concatenated into 1 memo field per the same fk each table shares. Each comments field may or may not have anything written in it.
    How do I do this in code?
    (I realize that this is not the best db design, but circumstances dictate it be done this way.) <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: 4 memo fields into 1 (A2k2 (as 2k) SP2)

    Include in the sql an expression like:

    [memo1] & " " & [memo2] & " " & [memo3] & " " & [memo4] AS [memo]

    If the 4 memo fields all had the same name, you would need to preface each with a table name. I assume here you are not fussed be a few extra spaces when some of the memos are blank.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 4 memo fields into 1 (A2k2 (as 2k) SP2)

    If you want to concatenate strings, but avoid spaces when one of them is empty, you can use + instead of &:

    [memo1] & (" "+[memo2) & (" "+[memo3]) & (" "+[memo4])

    It can be done with carriage returns/line feeds too:

    [memo1] & ((Chr(13) & Chr(10))+[memo2) & ((Chr(13) & Chr(10))+[memo3]) & ((Chr(13) & Chr(10))+[memo4])

    Note: this trick works in Access, but not in SQL Server. Another way is to use IIf:

    [memo1] & IIf(IsNull([memo2],""," " & [memo2]) & IIf(IsNull([memo3],""," " & [memo3]) & IIf(IsNull([memo4],""," " & [memo4])

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 4 memo fields into 1 (A2k2 (as 2k) SP2)

    Hi John and Hans,
    Thanks. I got it working. Sorry it took so long to get back here, but dept. meetings have their own priorities I guess.
    Thanks for the added tips Hans.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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