How to concatenate a long string in a function and avoid 255 chr l

S

samirk

I have a table with data similar to:
Num String
1 String 1A
1 String 1B
1 String 1C
2 String 2A
2 String 2B
3 String 3
....

and would like a query/table with the result:

1 String 1A; String 1B; String 1C
2 String 2A; String 2B
3 String 3

----------
After reading the forums, I was able to find this function below and now I
call it in a query that has a Group By on the Num field. Unfortunately, some
of these strings 1A, 1B 1C etc. can be long and the result is getting
truncated to 255 characters. I read a KB article and have tried to change
the query to become an Append Table query with a memo field in the table to
hold the result, but no luck. Is there a way I can accomplish what I need
(and ideally, make it run faster too!).

---------
Public Function ConCatField(pstrTable As String, pstrField As String,
Optional pstrCriteria As String = "", Optional pstrOrderBy As String,
Optional pstrDelimiter As String = "; ") As String
Dim rs As New ADODB.Recordset

rs.Open "Select [" & pstrField & "] FROM [" & pstrTable & "]" &
IIf(Len(pstrCriteria) > 0, " WHERE " & pstrCriteria, "") &
IIf(Len(pstrOrderBy) > 0, " ORDER BY " & pstrOrderBy, ""),
CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
ConCatField = rs.GetString(adClipString, , "", pstrDelimiter)
ConCatField = Left(ConCatField, Len(ConCatField) - Len(pstrDelimiter))
End If
rs.Close
Set rs = Nothing
End Function
--------
 
S

samirk

Allen - Thanks for pointing me to that page and tip. I added the FIRST into
the query, but it's still the same issue: the table still gets only 255
characters into the WebReviews field (which has been defined as memo). I
think it's perhaps because the ConCatField function that I am using returns
only a String, right?

If if helps to see it, here's the Append Table query that I am using -
---
INSERT INTO [Reviews by Movie] ( MovieNum, WebReviews )
SELECT [1a2 - Parse Movie Reviews].MovieNum, First(ConCatField("1a2 - Parse
Movie Reviews","FullReview","[MovieNum]=" & [MovieNum] & "")) AS WebReviews
FROM [1a2 - Parse Movie Reviews]
GROUP BY [1a2 - Parse Movie Reviews].MovieNum;
----

Allen Browne said:
In the Totals query, use First in the Total Row under the long field. Don't
use Group By under the memo: that will truncate it.

Explanation in:
Truncation of Memo fields
at:
http://allenbrowne.com/ser-63.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

samirk said:
I have a table with data similar to:
Num String
1 String 1A
1 String 1B
1 String 1C
2 String 2A
2 String 2B
3 String 3
...

and would like a query/table with the result:

1 String 1A; String 1B; String 1C
2 String 2A; String 2B
3 String 3

----------
After reading the forums, I was able to find this function below and now I
call it in a query that has a Group By on the Num field. Unfortunately,
some
of these strings 1A, 1B 1C etc. can be long and the result is getting
truncated to 255 characters. I read a KB article and have tried to change
the query to become an Append Table query with a memo field in the table
to
hold the result, but no luck. Is there a way I can accomplish what I need
(and ideally, make it run faster too!).

---------
Public Function ConCatField(pstrTable As String, pstrField As String,
Optional pstrCriteria As String = "", Optional pstrOrderBy As String,
Optional pstrDelimiter As String = "; ") As String
Dim rs As New ADODB.Recordset

rs.Open "Select [" & pstrField & "] FROM [" & pstrTable & "]" &
IIf(Len(pstrCriteria) > 0, " WHERE " & pstrCriteria, "") &
IIf(Len(pstrOrderBy) > 0, " ORDER BY " & pstrOrderBy, ""),
CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
ConCatField = rs.GetString(adClipString, , "", pstrDelimiter)
ConCatField = Left(ConCatField, Len(ConCatField) - Len(pstrDelimiter))
End If
rs.Close
Set rs = Nothing
End Function
Click to expand...
Click to expand...
 
A

Allen Browne

Hmm. You may have struck this bug in Access:
Concatenated fields yield garbage in recordset
at:
http://allenbrowne.com/bug-16.html

Particularly, the article states:
Typical problem scenarios include:
* Any field that relies on a function that could yield
more than 255 characters.

If you want to trace it further, break the issue down. Open the Immediate
Window (Ctrl+G), and test the function there with a case that should return
255 char.
Click to expand...

If that works without a problem, lose the first and last lines of your query
(the INSERT INTO and GROUP BY clauses), and see if that works. You will then
know if the bug is in handling the concatenated string, or in the grouping.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

samirk said:
Allen - Thanks for pointing me to that page and tip. I added the FIRST
into
the query, but it's still the same issue: the table still gets only 255
characters into the WebReviews field (which has been defined as memo). I
think it's perhaps because the ConCatField function that I am using
returns
only a String, right?

If if helps to see it, here's the Append Table query that I am using -
---
INSERT INTO [Reviews by Movie] ( MovieNum, WebReviews )
SELECT [1a2 - Parse Movie Reviews].MovieNum, First(ConCatField("1a2 -
Parse
Movie Reviews","FullReview","[MovieNum]=" & [MovieNum] & "")) AS
WebReviews
FROM [1a2 - Parse Movie Reviews]
GROUP BY [1a2 - Parse Movie Reviews].MovieNum;
----

Allen Browne said:
In the Totals query, use First in the Total Row under the long field.
Don't
use Group By under the memo: that will truncate it.

Explanation in:
Truncation of Memo fields
at:
http://allenbrowne.com/ser-63.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

samirk said:
I have a table with data similar to:
Num String
1 String 1A
1 String 1B
1 String 1C
2 String 2A
2 String 2B
3 String 3
...

and would like a query/table with the result:

1 String 1A; String 1B; String 1C
2 String 2A; String 2B
3 String 3

----------
After reading the forums, I was able to find this function below and
now I
call it in a query that has a Group By on the Num field.
Unfortunately,
some
of these strings 1A, 1B 1C etc. can be long and the result is getting
truncated to 255 characters. I read a KB article and have tried to
change
the query to become an Append Table query with a memo field in the
table
to
hold the result, but no luck. Is there a way I can accomplish what I
need
(and ideally, make it run faster too!).

---------
Public Function ConCatField(pstrTable As String, pstrField As String,
Optional pstrCriteria As String = "", Optional pstrOrderBy As String,
Optional pstrDelimiter As String = "; ") As String
Dim rs As New ADODB.Recordset

rs.Open "Select [" & pstrField & "] FROM [" & pstrTable & "]" &
IIf(Len(pstrCriteria) > 0, " WHERE " & pstrCriteria, "") &
IIf(Len(pstrOrderBy) > 0, " ORDER BY " & pstrOrderBy, ""),
CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
ConCatField = rs.GetString(adClipString, , "", pstrDelimiter)
ConCatField = Left(ConCatField, Len(ConCatField) -
Len(pstrDelimiter))
End If
rs.Close
Set rs = Nothing
End Function
Click to expand...
Click to expand...
Click to expand...
 
S

samirk

I have never used the immediate window and am having trouble figuring it out.
I launch it fine and then type in the following:

SELECT [1a2 - Parse Movie Reviews].MovieNum, ConCatField("1a2 - Parse Movie
Reviews","FullReview","[MovieNum]=360")
FROM [1a2 - Parse Movie Reviews];

and when I hit enter, it gives me a Compile error: Expected end of
statement. What can I do to fix that?


Allen Browne said:
Hmm. You may have struck this bug in Access:
Concatenated fields yield garbage in recordset
at:
http://allenbrowne.com/bug-16.html

Particularly, the article states:
Typical problem scenarios include:
* Any field that relies on a function that could yield
more than 255 characters.

If you want to trace it further, break the issue down. Open the Immediate
Window (Ctrl+G), and test the function there with a case that should return
255 char.
Click to expand...

If that works without a problem, lose the first and last lines of your query
(the INSERT INTO and GROUP BY clauses), and see if that works. You will then
know if the bug is in handling the concatenated string, or in the grouping.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

samirk said:
Allen - Thanks for pointing me to that page and tip. I added the FIRST
into
the query, but it's still the same issue: the table still gets only 255
characters into the WebReviews field (which has been defined as memo). I
think it's perhaps because the ConCatField function that I am using
returns
only a String, right?

If if helps to see it, here's the Append Table query that I am using -
---
INSERT INTO [Reviews by Movie] ( MovieNum, WebReviews )
SELECT [1a2 - Parse Movie Reviews].MovieNum, First(ConCatField("1a2 -
Parse
Movie Reviews","FullReview","[MovieNum]=" & [MovieNum] & "")) AS
WebReviews
FROM [1a2 - Parse Movie Reviews]
GROUP BY [1a2 - Parse Movie Reviews].MovieNum;
----

Allen Browne said:
In the Totals query, use First in the Total Row under the long field.
Don't
use Group By under the memo: that will truncate it.

Explanation in:
Truncation of Memo fields
at:
http://allenbrowne.com/ser-63.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table with data similar to:
Num String
1 String 1A
1 String 1B
1 String 1C
2 String 2A
2 String 2B
3 String 3
...

and would like a query/table with the result:

1 String 1A; String 1B; String 1C
2 String 2A; String 2B
3 String 3

----------
After reading the forums, I was able to find this function below and
now I
call it in a query that has a Group By on the Num field.
Unfortunately,
some
of these strings 1A, 1B 1C etc. can be long and the result is getting
truncated to 255 characters. I read a KB article and have tried to
change
the query to become an Append Table query with a memo field in the
table
to
hold the result, but no luck. Is there a way I can accomplish what I
need
(and ideally, make it run faster too!).

---------
Public Function ConCatField(pstrTable As String, pstrField As String,
Optional pstrCriteria As String = "", Optional pstrOrderBy As String,
Optional pstrDelimiter As String = "; ") As String
Dim rs As New ADODB.Recordset

rs.Open "Select [" & pstrField & "] FROM [" & pstrTable & "]" &
IIf(Len(pstrCriteria) > 0, " WHERE " & pstrCriteria, "") &
IIf(Len(pstrOrderBy) > 0, " ORDER BY " & pstrOrderBy, ""),
CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
ConCatField = rs.GetString(adClipString, , "", pstrDelimiter)
ConCatField = Left(ConCatField, Len(ConCatField) -
Len(pstrDelimiter))
End If
rs.Close
Set rs = Nothing
End Function
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

In the Immediate Window, just test the function:

? ConCatField("1a2 - Parse Movie Reviews", "FullReview", "[MovieNum]=360")

The query you posted will need to be tested in the query window.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

samirk said:
I have never used the immediate window and am having trouble figuring it
out.
I launch it fine and then type in the following:

SELECT [1a2 - Parse Movie Reviews].MovieNum, ConCatField("1a2 - Parse
Movie
Reviews","FullReview","[MovieNum]=360")
FROM [1a2 - Parse Movie Reviews];

and when I hit enter, it gives me a Compile error: Expected end of
statement. What can I do to fix that?


Allen Browne said:
Hmm. You may have struck this bug in Access:
Concatenated fields yield garbage in recordset
at:
http://allenbrowne.com/bug-16.html

Particularly, the article states:
Typical problem scenarios include:
* Any field that relies on a function that could yield
more than 255 characters.

If you want to trace it further, break the issue down. Open the Immediate
Window (Ctrl+G), and test the function there with a case that should
return

If that works without a problem, lose the first and last lines of your
query
(the INSERT INTO and GROUP BY clauses), and see if that works. You will
then
know if the bug is in handling the concatenated string, or in the
grouping.
Click to expand...
Click to expand...
 
S

samirk

Thanks for that tip. The function ran fine in the immediate window, but even
the simplest query (below) gets cut off, so it could be the bug you're
referring to:

SELECT ConCatField("1a2 - Parse Movie Reviews","FullReview","[MovieNum]=360")
FROM [1a2 - Parse Movie Reviews];

There doesn't seem to be a simple workaround now, is there?

Allen Browne said:
In the Immediate Window, just test the function:

? ConCatField("1a2 - Parse Movie Reviews", "FullReview", "[MovieNum]=360")

The query you posted will need to be tested in the query window.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

samirk said:
I have never used the immediate window and am having trouble figuring it
out.
I launch it fine and then type in the following:

SELECT [1a2 - Parse Movie Reviews].MovieNum, ConCatField("1a2 - Parse
Movie
Reviews","FullReview","[MovieNum]=360")
FROM [1a2 - Parse Movie Reviews];

and when I hit enter, it gives me a Compile error: Expected end of
statement. What can I do to fix that?


Allen Browne said:
Hmm. You may have struck this bug in Access:
Concatenated fields yield garbage in recordset
at:
http://allenbrowne.com/bug-16.html

Particularly, the article states:
Typical problem scenarios include:
* Any field that relies on a function that could yield
more than 255 characters.

If you want to trace it further, break the issue down. Open the Immediate
Window (Ctrl+G), and test the function there with a case that should
return
255 char.

If that works without a problem, lose the first and last lines of your
query
(the INSERT INTO and GROUP BY clauses), and see if that works. You will
then
know if the bug is in handling the concatenated string, or in the
grouping.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Is there any chance of using the function in a context other than the query?

For example, could you set the ControlSource of a text box to:
=ConCatField("1a2 - Parse Movie Reviews","FullReview","[MovieNum]=360")
 
S

samirk

The end goal is to get a tab delimited file that I can import into our
eCommerce software. You can check out:
- A movie with the reviews field I set manually in the eCommerce system:
http://www.indiaforeveryone.org/IFE/product/D-309.html
- The movie (MovieNum 360) which I imported the data for -- and the reviews
were cut off: http://www.indiaforeveryone.org/IFE/product/D-360.html

Thanks again for all your help!

Allen Browne said:
Is there any chance of using the function in a context other than the query?

For example, could you set the ControlSource of a text box to:
=ConCatField("1a2 - Parse Movie Reviews","FullReview","[MovieNum]=360")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

samirk said:
Thanks for that tip. The function ran fine in the immediate window, but
even
the simplest query (below) gets cut off, so it could be the bug you're
referring to:

SELECT ConCatField("1a2 - Parse Movie
Reviews","FullReview","[MovieNum]=360")
FROM [1a2 - Parse Movie Reviews];

There doesn't seem to be a simple workaround now, is there?
Click to expand...
Click to expand...
 
A

Allen Browne

Not sure what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

samirk said:
The end goal is to get a tab delimited file that I can import into our
eCommerce software. You can check out:
- A movie with the reviews field I set manually in the eCommerce system:
http://www.indiaforeveryone.org/IFE/product/D-309.html
- The movie (MovieNum 360) which I imported the data for -- and the
reviews
were cut off: http://www.indiaforeveryone.org/IFE/product/D-360.html

Thanks again for all your help!

Allen Browne said:
Is there any chance of using the function in a context other than the
query?

For example, could you set the ControlSource of a text box to:
=ConCatField("1a2 - Parse Movie
Reviews","FullReview","[MovieNum]=360")
Click to expand...
Click to expand...
 
S

samirk

Perhaps I could try doing this in two steps ...
1. Create an append table query to get the GROUP BY done and a memo field
created (concatenated string, but leaving out the really long parts like the
target URLs, so it stays within 255 characters)
2. Write another query that UPDATEs the memo field by doing a Replace
function with the longer URLs

I am not sure how to actually do Step 2.

My new table from step 1 could have something like -

360 "8 out of 10 !R1!<br>7 out of 10 !R2!"<br>9 out of 10 !R3!

and I still have the original table with the fields -

360 R1 URL1 (the URL will replace !R1!)
360 R2 URL2
360 R3 URL3


Could I write a query / function that can then do Step 2 above?



Allen Browne said:
Not sure what else to suggest.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

samirk said:
The end goal is to get a tab delimited file that I can import into our
eCommerce software. You can check out:
- A movie with the reviews field I set manually in the eCommerce system:
http://www.indiaforeveryone.org/IFE/product/D-309.html
- The movie (MovieNum 360) which I imported the data for -- and the
reviews
were cut off: http://www.indiaforeveryone.org/IFE/product/D-360.html

Thanks again for all your help!

Allen Browne said:
Is there any chance of using the function in a context other than the
query?

For example, could you set the ControlSource of a text box to:
=ConCatField("1a2 - Parse Movie
Reviews","FullReview","[MovieNum]=360")
Click to expand...
Click to expand...
Click to expand...
 
D

David W. Fenton

Hmm. You may have struck this bug in Access:
Concatenated fields yield garbage in recordset
at:
http://allenbrowne.com/bug-16.html
Click to expand...

I had never seen that article of yours before. You are wrong in
stating that it doesn't affect memo fields -- it does, and I've been
dealing with it for years in one of my A97 apps (literally, almost
10 years, in fact).

I've mentioned it in the newsgroups but never had anyone confirm
that they've seen the same thing. I've found that the only way to
get around it is to concatenate into a temp table with SQL, and then
use that temp table as the source of your recordset (i.e., there is
no concatenation in the recordset, since the data's already been
concatenate in the data fields of the source table).
 
A

Allen Browne

David, would you have a sample that demonstrates the problem with memo
fields?

I am interested to see the specifics of the case you are referring to. Email
me, using the address in the sig below. Thanks
 
D

David W. Fenton

David, would you have a sample that demonstrates the problem with
memo fields?
Click to expand...

Ack. It would be quite complex to pull the stuff out of the app it's
in, as I've coded around the problem years ago.
I am interested to see the specifics of the case you are referring
to. Email me, using the address in the sig below.
Click to expand...

If I have the time to extract the example, I'll send it to you.

But it's definitely the case that I was concatenating memo fields
and the values were truncating at 255 characters and returning
gibberish after that.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concatenate Function 7
Garbage in a string variable loaded from a field 2
How do you pass parameters to a stored procedure 2
Query within a query 4
How to Call a Function 4
create an uppend query on the fly? 1
HELP! What is used in excel to merge into designated Word Table Ce 3
Concatenate hierarchy 1

Top