Concatenating records (Access 2000)

Topic

New Reply

Does anyone know if it

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Thanks,
Drew

Reply | Quote

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 “.

Reply | Quote

It works great!!!
Thank you!

Drew

Reply | Quote

It works great!!!
Thank you!

Drew

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Works great.
Thanks

Reply | Quote

Works great.
Thanks

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Yes, I see that. The function returns the complete string, but the query truncates it to 255 characters (the maximum length of a string field.) I will look into it.

Reply | Quote

Yes, I see that. The function returns the complete string, but the query truncates it to 255 characters (the maximum length of a string field.) I will look into it.

Reply | Quote

I could reproduce your problem earlier today. Now, on another PC, I can’t. The difference is that I’ve got the latest Jet service pack on the one I’m on now. This led me to ACC2000: Result of the Mid, the Left, or the Right Function in Query Referring to Memo Column Is Incorrect. This applies to the Concat function, since it uses Mid.

So if you want to use the Concat function for results of more than 255 bytes, you must install the latest Jet service pack. Or, you can comment out the following lines in the code if you can live with a superfluous initial separator in the result:

If strRes “” Then
strRes = Mid$(strRes, Len(aSeparator) + 1)
End If

Reply | Quote

Hans,
I ran the .exe file after downloading, but my query is still truncating.
Am I doing something wrong?

Thanks so much for your help.

Reply | Quote

I’m sorry, in that case, I have no idea. On a PC with Access 2002 and Jet 4.0 SP6, the values get truncated to 255 characters, on a PC with Access 2002 and Jet 4.0 SP8, they don’t get truncated. I don’t have Access 2000, so I can’t test the behavior there.

Reply | Quote

I’m sorry, in that case, I have no idea. On a PC with Access 2002 and Jet 4.0 SP6, the values get truncated to 255 characters, on a PC with Access 2002 and Jet 4.0 SP8, they don’t get truncated. I don’t have Access 2000, so I can’t test the behavior there.

Reply | Quote

Try this modified version. It works OK for me on Jet 4.0 SP6.

Reply | Quote

Thank you very much for the help, but removing the IF statement didn’t work.
I’ve talked to our MIS dept here. After the holidays they are going to set my machine up with Office XP. I’ll let you know if this
works then.

Thanks again,
Drew

Reply | Quote

Thank you very much for the help, but removing the IF statement didn’t work.
I’ve talked to our MIS dept here. After the holidays they are going to set my machine up with Office XP. I’ll let you know if this
works then.

Thanks again,
Drew

Reply | Quote

Hans,

I was hoping the Office XP would fix my issue, but I still have the same problem (truncating the concatinated field to 254 characters).
I know very little about VB or the ‘Jet…’ service packs (?), but I don’t show under tools > references the Jet 4.0 SP6 or Jet 4.0 SP8. Is this
something I need and how do I go by getting it if so? …or do I need to do something else? Please help.

Thanks,
Drew

Reply | Quote

Having Access 2002 in itself does not solve your problem. As I remarked in a reply higher up in this thread, I could reproduce it on a PC with Access 2002 SP-2 and Jet 4.0 SP6, but not on a PC with Access 2002 SP-2 and Jet 4.0 SP8.

MSKB article How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine describes how to determine which version of Jet 4.0 you have. This article also contains links to download the install file for SP8.

Reply | Quote

Hans,

I had a problem that was similiar. I found that if I did a group by on a memo field it was truncated. What I ended up doing was doing the rest of the rows in the group by and then doing an update query later.

Don

Reply | Quote

Hans,

I had a problem that was similiar. I found that if I did a group by on a memo field it was truncated. What I ended up doing was doing the rest of the rows in the group by and then doing an update query later.

Don

Reply | Quote

Hans,
I just got Windows XP and Office XP installed on my machine, to which I’ve ensured that I have the JET 8.0 SP. Unfortunately I’m still having problems with it truncating. I feel like this is something that I’m doing wrong on my side I’m but not for sure, so below are the fields in my table (TBL_US_EXT_DESC) and their data types…
Part Number — Text
Description — Memo
Line Number — Text

The query I set up only includes the above table, with Part Number being the only field that is grouped on. The Concat statement that I’m using is the following…
Long Desc: Concat(“TBL_US_EXT_DESC”,”Description”,”[Part Number] = ” & Chr(34) & [Part Number] & Chr(34),”Line Number”,Chr(13) & Chr(10))
Total = Expression

Does everything look right? I’m at a loss on what else I can do to possibly fix this problem?
Any help is greatly appreciated.

Thanks,
Drew

Reply | Quote

Your expression looks OK. I created a test table with the same name and fields, and also a query with the design you describe; I copied the definition of Long Desc straight out of your post into the query design. On my PC with Jet 4.0 SP-8, the query works as intended: the Long Desc field is not truncated. So you can rest assured that you didn’t make a mistake in the design. But, sadly, I have no idea why it doesn’t work for you, or what you could do to fix it…

Reply | Quote

Hans/Wendell,
I did check and did already have SP8 installed, but to ensure I went ahead downloaded and ran the file, which didn’t change anything.
I don’t know why I didn’t think of this earlier, but one thing I didn’t try was setting up a separate table and trying this from scratch. Once I done this, the field was no longer truncated. Something must of happen with my original table????? I’ll just have to copy the text over to the new table.
Thanks for all the help!!!

Regards,
Drew

Reply | Quote

Hans/Wendell,
I did check and did already have SP8 installed, but to ensure I went ahead downloaded and ran the file, which didn’t change anything.
I don’t know why I didn’t think of this earlier, but one thing I didn’t try was setting up a separate table and trying this from scratch. Once I done this, the field was no longer truncated. Something must of happen with my original table????? I’ll just have to copy the text over to the new table.
Thanks for all the help!!!

Regards,
Drew

Reply | Quote

Hi,
I too was having a problem with truncation of the crosstab at 255 characters. I solved it like this:

I created a table with the three fields that were needed for the crosstab query, & I made the Concatenated field in the table a memo field.
Ran a Group by append query, that appended to the table that I’d just created & then run a crosstab query from that Table, voila, the 255 truncation diappeared.

Jim MacLeod

Reply | Quote

Your expression looks OK. I created a test table with the same name and fields, and also a query with the design you describe; I copied the definition of Long Desc straight out of your post into the query design. On my PC with Jet 4.0 SP-8, the query works as intended: the Long Desc field is not truncated. So you can rest assured that you didn’t make a mistake in the design. But, sadly, I have no idea why it doesn’t work for you, or what you could do to fix it…

Reply | Quote

Installing Office XP won’t get you Jet 4.0 SP8 – you will still need to download and install it. If you haven’t done so, follow the instructions in the link Hans posted and check to see what version you have.

Reply | Quote

Installing Office XP won’t get you Jet 4.0 SP8 – you will still need to download and install it. If you haven’t done so, follow the instructions in the link Hans posted and check to see what version you have.

Reply | Quote

Hans,
I just got Windows XP and Office XP installed on my machine, to which I’ve ensured that I have the JET 8.0 SP. Unfortunately I’m still having problems with it truncating. I feel like this is something that I’m doing wrong on my side I’m but not for sure, so below are the fields in my table (TBL_US_EXT_DESC) and their data types…
Part Number — Text
Description — Memo
Line Number — Text

The query I set up only includes the above table, with Part Number being the only field that is grouped on. The Concat statement that I’m using is the following…
Long Desc: Concat(“TBL_US_EXT_DESC”,”Description”,”[Part Number] = ” & Chr(34) & [Part Number] & Chr(34),”Line Number”,Chr(13) & Chr(10))
Total = Expression

Does everything look right? I’m at a loss on what else I can do to possibly fix this problem?
Any help is greatly appreciated.

Thanks,
Drew

Reply | Quote

Having Access 2002 in itself does not solve your problem. As I remarked in a reply higher up in this thread, I could reproduce it on a PC with Access 2002 SP-2 and Jet 4.0 SP6, but not on a PC with Access 2002 SP-2 and Jet 4.0 SP8.

MSKB article How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine describes how to determine which version of Jet 4.0 you have. This article also contains links to download the install file for SP8.

Reply | Quote

Hans,

I was hoping the Office XP would fix my issue, but I still have the same problem (truncating the concatinated field to 254 characters).
I know very little about VB or the ‘Jet…’ service packs (?), but I don’t show under tools > references the Jet 4.0 SP6 or Jet 4.0 SP8. Is this
something I need and how do I go by getting it if so? …or do I need to do something else? Please help.

Thanks,
Drew

Reply | Quote

Try this modified version. It works OK for me on Jet 4.0 SP6.

Reply | Quote

Hans,
I ran the .exe file after downloading, but my query is still truncating.
Am I doing something wrong?

Thanks so much for your help.

Reply | Quote

I could reproduce your problem earlier today. Now, on another PC, I can’t. The difference is that I’ve got the latest Jet service pack on the one I’m on now. This led me to ACC2000: Result of the Mid, the Left, or the Right Function in Query Referring to Memo Column Is Incorrect. This applies to the Concat function, since it uses Mid.

So if you want to use the Concat function for results of more than 255 bytes, you must install the latest Jet service pack. Or, you can comment out the following lines in the code if you can live with a superfluous initial separator in the result:

If strRes “” Then
strRes = Mid$(strRes, Len(aSeparator) + 1)
End If

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 “.

Reply | Quote

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

Thanks,
Drew

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Many thanks Hans

Reply | Quote

Many thanks Hans

Reply | Quote

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.

Reply | Quote