character limits

S

shank

In the below query, I'm trying to Concatenate 3 fields using Duane Hookom's
function. It works, but for some reason, it's cutting off data at the 255
character limit. First, I tried MAKE a table. Then I tried appending to a
table TCS with tts being a Memo field. Same difference. Character limit gets
whacked to 255 characters. What am I doing wrong here? thanks!


INSERT INTO TCS ( od, tts )
SELECT [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' & [ats]
FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY sk",Chr(13) &
Chr(10)) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
GROUP BY [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' & [ats]
FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY sk",Chr(13) &
Chr(10))
ORDER BY [nit].od;

=====================
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
A

Allen Browne

The truncation is not due to Duane's function: it's due to the GROUP BY
clause of your query.
 
J

John Spencer

As Allen forgot to mention, one way to solve the problem might be to use
FIRST(Concatenate...) and not group by

INSERT INTO TCS ( od, tts )
SELECT [nit].od
, FIRST(Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' & [ats]
FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY sk",Chr(13) &
Chr(10))) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
GROUP BY [nit].od
ORDER BY [nit].od;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Allen Browne said:
The truncation is not due to Duane's function: it's due to the GROUP BY
clause of your query.

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

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

shank said:
In the below query, I'm trying to Concatenate 3 fields using Duane
Hookom's function. It works, but for some reason, it's cutting off data
at the 255 character limit. First, I tried MAKE a table. Then I tried
appending to a table TCS with tts being a Memo field. Same difference.
Character limit gets whacked to 255 characters. What am I doing wrong
here? thanks!


INSERT INTO TCS ( od, tts )
SELECT [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' &
[ats] FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY
sk",Chr(13) & Chr(10)) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
GROUP BY [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' &
[ats] FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY
sk",Chr(13) & Chr(10))
ORDER BY [nit].od;

=====================
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
S

shank

John - Before I got your response, I came up with the below. I rely on the
primary key of TCS to remove the dupes and act as the GROUP clause. I'm sure
this is a waste of resources, but it works.

I could not get yours work. It still whacked the fields down to 255
characters. I would like to get yours to work for the obvious speed
advantages. What am I missing?

thanks!
= = = = = = = =
Mine...
INSERT INTO INSERT INTO TCS ( od, tts )
SELECT [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' & [ats]
FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY SortKey",Chr(13)
& Chr(10)) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
ORDER BY [nit].od;
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Yours....
INSERT INTO TCS ( od, tts )
SELECT [nit].od
, FIRST(Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' & [ats]
FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY sk",Chr(13) &
Chr(10))) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
GROUP BY [nit].od
ORDER BY [nit].od;


John Spencer said:
As Allen forgot to mention, one way to solve the problem might be to use
FIRST(Concatenate...) and not group by

INSERT INTO TCS ( od, tts )
SELECT [nit].od
, FIRST(Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' & [ats]
FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY sk",Chr(13) &
Chr(10))) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
GROUP BY [nit].od
ORDER BY [nit].od;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Allen Browne said:
The truncation is not due to Duane's function: it's due to the GROUP BY
clause of your query.

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

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

shank said:
In the below query, I'm trying to Concatenate 3 fields using Duane
Hookom's function. It works, but for some reason, it's cutting off data
at the 255 character limit. First, I tried MAKE a table. Then I tried
appending to a table TCS with tts being a Memo field. Same difference.
Character limit gets whacked to 255 characters. What am I doing wrong
here? thanks!


INSERT INTO TCS ( od, tts )
SELECT [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' &
[ats] FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY
sk",Chr(13) & Chr(10)) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
GROUP BY [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' &
[ats] FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY
sk",Chr(13) & Chr(10))
ORDER BY [nit].od;

=====================
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
J

John Spencer

I don't know. It should work as long as you've already defined the field
tts as a memo field then I would think that things shouldn't be truncated if
you use first.

If you just run the SELECT query, does that truncate the returned value of
the Concatenate function or does it truncate. If it does truncate, then I
am stuck for a solution other than what you have already come up with.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

shank said:
John - Before I got your response, I came up with the below. I rely on the
primary key of TCS to remove the dupes and act as the GROUP clause. I'm
sure this is a waste of resources, but it works.

I could not get yours work. It still whacked the fields down to 255
characters. I would like to get yours to work for the obvious speed
advantages. What am I missing?

thanks!
= = = = = = = =
Mine...
INSERT INTO INSERT INTO TCS ( od, tts )
SELECT [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' & [ats]
FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY
SortKey",Chr(13) & Chr(10)) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
ORDER BY [nit].od;
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Yours....
INSERT INTO TCS ( od, tts )
SELECT [nit].od
, FIRST(Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' & [ats]
FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY sk",Chr(13) &
Chr(10))) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
GROUP BY [nit].od
ORDER BY [nit].od;


John Spencer said:
As Allen forgot to mention, one way to solve the problem might be to use
FIRST(Concatenate...) and not group by

INSERT INTO TCS ( od, tts )
SELECT [nit].od
, FIRST(Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' & [ats]
FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY sk",Chr(13) &
Chr(10))) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
GROUP BY [nit].od
ORDER BY [nit].od;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Allen Browne said:
The truncation is not due to Duane's function: it's due to the GROUP BY
clause of your query.

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

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

In the below query, I'm trying to Concatenate 3 fields using Duane
Hookom's function. It works, but for some reason, it's cutting off data
at the 255 character limit. First, I tried MAKE a table. Then I tried
appending to a table TCS with tts being a Memo field. Same difference.
Character limit gets whacked to 255 characters. What am I doing wrong
here? thanks!


INSERT INTO TCS ( od, tts )
SELECT [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' &
[ats] FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY
sk",Chr(13) & Chr(10)) AS tts
FROM [nit] INNER JOIN [ssc] ON [nit].od = [ssc].od
GROUP BY [nit].od, Concatenate("SELECT [sk] & '. ' & [tts] & ' ... ' &
[ats] FROM [nit] WHERE [nit]![od] ='" & [ssc]![od] & "' ORDER BY
sk",Chr(13) & Chr(10))
ORDER BY [nit].od;

=====================
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 

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

Top