Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenating records (Access 2000)

    Does anyone know if it
    Attached Files Attached Files

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

    Re: Concatenating records (Access 2000)

    If you want to do this in a report, you can create this effect by putting a text box bound to Part Number and a text box bound to Description next to each other. Set the Hide Duplicates property of Part Number to Yes.

    If you need to concatenate in a form or query, you need some VBA code. I have attached a text file with the code for a custom function Concat. You can copy the code into a standard module.
    To use it, create a Totals query based on the table with the part numbers and descriptions. Add the part number, and leave the Totals option as Group By. Create a calculated field

    Descriptions: Concat("tblSomething", "Description", "[Part Number] = " & [Part Number], "Line Number", Chr(13) & Chr(10))

    where tblSomething is the name of the table.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating records (Access 2000)

    Hans,
    I'm having a hard time with this. VB is telling me 'Compile Error' and highlights the text upto line 17. Am
    I doing something wrong?
    I've took a snapshot of the error & the query, but the file is too large to attach in the lounge. I can send you this info if needed.
    Thanks,
    Drew

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

    Re: Concatenating records (Access 2000)

    To reduce the screenshot in size, use any graphics program, such as Paint, Microsoft Photo Editor, IrfanView, ... to trim bits you do not need from the screenshot, then save it as a .png file.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating records (Access 2000)

    I've removed the 'Compiling Error' snapshot picture and only included the query and the section of VB code highlighted when I ran the query. The pictures are now attached.
    (the rest of the code can be seen from your original text document you sent in the lounge)
    The snapshop doesn't include the highlighted text ("Dim dbs As DAO.Database") from line 17 of the VB. I know very little about VB, but seems like their is an
    error on that line??? or maybe in my query?
    Thanks,
    Drew
    Attached Files Attached Files

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

    Re: Concatenating records (Access 2000)

    Select Tools | References... in the Visual Basic Editor. Locate and tick Microsoft DAO 3.6 Object Library, then click OK. This should take care of the compile error.

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating records (Access 2000)

    Hans,
    Now I'm getting a run-time error ('3454').
    "Data type mismatch in criteria expression"
    I hit debug and it highlights line 36 of your code (Set rst = dbs.OpenRecordset(strSQL))

    Regards,
    Drew

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

    Re: Concatenating records (Access 2000)

    While the error is highlighted, activate the Immediate window (Ctrl+G) and type

    ? strSQL

    then press Enter. Could you copy the result into a reply? Thank you.

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating records (Access 2000)

    Here is the result...
    SELECT [Description] FROM [Concat] WHERE ([Part Number] = 3810001000) ORDER BY [Line Number];

    Thanks,
    Drew

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

    Re: Concatenating records (Access 2000)

    3810001000 is too large to fit in a Long Integer field, so that makes me suspect that Part Number, despite its name, is a text field. If so, you must change the third argument from

    "[Part Number] = " & [Part Number]

    to

    "[Part Number] = " & Chr(34) & [Part Number] & Chr(34)

    Chr(34) is the double quote ".

  11. #11
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating records (Access 2000)

    It works great!!!
    Thank you!

    Drew

  12. #12
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating records (Access 2000)

    Hans,
    Would it be possible to change the expression and use the same code on another query?
    I have a table of the following columns...
    ID [Number; Long Integer]
    ITEM [Text]
    MFG [Text]
    In this list, the MFG is the field that I would like to combine/concatenate with respect to both ID & ITEM. I've tried to
    modify the expression that you had given me, but with my knowledge I'm not able to get it to work.
    Can you help?
    Thanks,
    Drew

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

    Re: Concatenating records (Access 2000)

    If I understand correctly, you group on ID and ITEM, and you want to concatenate the values of MFG. It goes like this:

    MFG_List: Concat("tblSomething", "MFG", "[ID] = " & [ID] & " And [ITEM] = " & Chr(34) & [ITEM] & Chr(34), "", Chr(13) & Chr(10))

    where tblSomething is the name of the table.

  14. #14
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating records (Access 2000)

    Works great.
    Thanks

  15. #15
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating records (Access 2000)

    Hi Hans,

    I'm trying to use this same script for another instance but am having a problem. I have an table/qry with 2 columns; one is ID and the other is NOTES (having multiple records of notes per ID).
    Below is the statement I'm using and works, but it truncates at around 250 characters? Am setting this up correctly?
    NOTES: Concat("QRY_CONCAT_DESC_NOTES","CONCAT","[PROB_ID] = " & [PROB_ID],"",Chr(13) & Chr(10) & Chr(13) & Chr(10))

    Regards,
    Drew

Page 1 of 2 12 LastLast

Posting Permissions

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