Concatenation Function

[Cydney]

This question for Duane Hookum:

I am using your concatenation function in my access database. My problem is
that the parts that are combined equal more than 255 characters. It's cutting
off the text even though the field that I am appending into is a Memo field
type. Any ideas of a way I can fix this? I need to also export this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Duane Hookom]

I expect the issue is your query. Can you copy your full SQL view into a
reply?

 

[Cydney]

Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate, Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" & [TaskNumber] & "' AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#") AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs

I expect the issue is your query. Can you copy your full SQL view into a
reply?

--
Duane Hookom
MS Access MVP

This question for Duane Hookum:

I am using your concatenation function in my access database. My problem
is
that the parts that are combined equal more than 255 characters. It's
cutting
off the text even though the field that I am appending into is a Memo
field
type. Any ideas of a way I can fix this? I need to also export this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Duane Hookom]

The query that you are showing should not limit the returned CombinedNotes
to 255 characters. When you view the results of the query do you see more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP

Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" & [TaskNumber] & "'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#") AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs

I expect the issue is your query. Can you copy your full SQL view into a
reply?

--
Duane Hookom
MS Access MVP

This question for Duane Hookum:

I am using your concatenation function in my access database. My
problem
is
that the parts that are combined equal more than 255 characters. It's
cutting
off the text even though the field that I am appending into is a Memo
field
type. Any ideas of a way I can fix this? I need to also export this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Cydney]

I agree with you and that's what I thought. The records are cut off at the
query level after the concatenation. My guess was that during that process it
was combining the fields as "text" format and thus cutting off the characters
beyond 255. But I don't know how to correct that in the function, IF that's
the case.
--
THX cs

The query that you are showing should not limit the returned CombinedNotes
to 255 characters. When you view the results of the query do you see more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP

Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" & [TaskNumber] & "'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#") AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs

I expect the issue is your query. Can you copy your full SQL view into a
reply?

--
Duane Hookom
MS Access MVP


This question for Duane Hookum:

I am using your concatenation function in my access database. My
problem
is
that the parts that are combined equal more than 255 characters. It's
cutting
off the text even though the field that I am appending into is a Memo
field
type. Any ideas of a way I can fix this? I need to also export this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Duane Hookom]

Do you have any type of Format set on the columns?

--
Duane Hookom
MS Access MVP

I agree with you and that's what I thought. The records are cut off at the
query level after the concatenation. My guess was that during that process
it
was combining the fields as "text" format and thus cutting off the
characters
beyond 255. But I don't know how to correct that in the function, IF
that's
the case.
--
THX cs

The query that you are showing should not limit the returned
CombinedNotes
to 255 characters. When you view the results of the query do you see more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP

Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" & [TaskNumber] &
"'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#") AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs


:

I expect the issue is your query. Can you copy your full SQL view into
a
reply?

--
Duane Hookom
MS Access MVP


This question for Duane Hookum:

I am using your concatenation function in my access database. My
problem
is
that the parts that are combined equal more than 255 characters.
It's
cutting
off the text even though the field that I am appending into is a
Memo
field
type. Any ideas of a way I can fix this? I need to also export this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Cydney]

No, they don't have any formatting on the query fields. Just the table fields
that are being appended to.

However... I did some more testing and made the Concatenate query into an
append to a new temp table. Everything seems to be better now within all the
queries, but I still get the fields cut off on export. Here's my command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"TempMonthlyData", efile, True

I thought that acSpreadsheetTypeExcel9 would keep it from doing that.

--
THX cs

Do you have any type of Format set on the columns?

--
Duane Hookom
MS Access MVP

I agree with you and that's what I thought. The records are cut off at the
query level after the concatenation. My guess was that during that process
it
was combining the fields as "text" format and thus cutting off the
characters
beyond 255. But I don't know how to correct that in the function, IF
that's
the case.
--
THX cs

The query that you are showing should not limit the returned
CombinedNotes
to 255 characters. When you view the results of the query do you see more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP


Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" & [TaskNumber] &
"'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#") AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs


:

I expect the issue is your query. Can you copy your full SQL view into
a
reply?

--
Duane Hookom
MS Access MVP


This question for Duane Hookum:

I am using your concatenation function in my access database. My
problem
is
that the parts that are combined equal more than 255 characters.
It's
cutting
off the text even though the field that I am appending into is a
Memo
field
type. Any ideas of a way I can fix this? I need to also export this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Duane Hookom]

I believe Ken Snell posted a solution to this some time back. I suggest you
create a new thread on "How to export to Excel with more than 255
characters"...

--
Duane Hookom
MS Access MVP

No, they don't have any formatting on the query fields. Just the table
fields
that are being appended to.

However... I did some more testing and made the Concatenate query into an
append to a new temp table. Everything seems to be better now within all
the
queries, but I still get the fields cut off on export. Here's my command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"TempMonthlyData", efile, True

I thought that acSpreadsheetTypeExcel9 would keep it from doing that.

--
THX cs

Do you have any type of Format set on the columns?

--
Duane Hookom
MS Access MVP

I agree with you and that's what I thought. The records are cut off at
the
query level after the concatenation. My guess was that during that
process
it
was combining the fields as "text" format and thus cutting off the
characters
beyond 255. But I don't know how to correct that in the function, IF
that's
the case.
--
THX cs


:

The query that you are showing should not limit the returned
CombinedNotes
to 255 characters. When you view the results of the query do you see
more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP


Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" & [TaskNumber]
&
"'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#") AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs


:

I expect the issue is your query. Can you copy your full SQL view
into
a
reply?

--
Duane Hookom
MS Access MVP


This question for Duane Hookum:

I am using your concatenation function in my access database. My
problem
is
that the parts that are combined equal more than 255 characters.
It's
cutting
off the text even though the field that I am appending into is a
Memo
field
type. Any ideas of a way I can fix this? I need to also export
this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Ken Snell \(MVP\)]

The issue arises because Jet treats the string returned by the Concatenate
function as an expression in the query. When you export the results of a
query to a text file or an EXCEL spreadsheet, all calculated fields within
that query will truncate their strings to 255 characters. See this Knowledge
Base article:

Exported Query Expression Truncated at 255 Characters
http://support.microsoft.com/default.aspx?scid=kb;en-us;207668


Workaround would be to create a temporary table for the data, append the
query to the temporary table, export the table, and then delete the
temporary table (or delete the records from the temporary table so that you
can reuse the table the next time).
--

Ken Snell
<MS ACCESS MVP>

I believe Ken Snell posted a solution to this some time back. I suggest you
create a new thread on "How to export to Excel with more than 255
characters"...

--
Duane Hookom
MS Access MVP

No, they don't have any formatting on the query fields. Just the table
fields
that are being appended to.

However... I did some more testing and made the Concatenate query into an
append to a new temp table. Everything seems to be better now within all
the
queries, but I still get the fields cut off on export. Here's my command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"TempMonthlyData", efile, True

I thought that acSpreadsheetTypeExcel9 would keep it from doing that.

--
THX cs

Do you have any type of Format set on the columns?

--
Duane Hookom
MS Access MVP


I agree with you and that's what I thought. The records are cut off at
the
query level after the concatenation. My guess was that during that
process
it
was combining the fields as "text" format and thus cutting off the
characters
beyond 255. But I don't know how to correct that in the function, IF
that's
the case.
--
THX cs


:

The query that you are showing should not limit the returned
CombinedNotes
to 255 characters. When you view the results of the query do you see
more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP


Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" &
[TaskNumber] &
"'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#") AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs


:

I expect the issue is your query. Can you copy your full SQL view
into
a
reply?

--
Duane Hookom
MS Access MVP


This question for Duane Hookum:

I am using your concatenation function in my access database. My
problem
is
that the parts that are combined equal more than 255 characters.
It's
cutting
off the text even though the field that I am appending into is a
Memo
field
type. Any ideas of a way I can fix this? I need to also export
this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Cydney]

Finally. I had to take out the grouping from the append query and then it
seemed to work. I grouped my data separately first, then joined the grouped
queries together and then used that join as the source for my append query.
That seems to have done the trick. I really don't understand why though..
--
THX cs

The issue arises because Jet treats the string returned by the Concatenate
function as an expression in the query. When you export the results of a
query to a text file or an EXCEL spreadsheet, all calculated fields within
that query will truncate their strings to 255 characters. See this Knowledge
Base article:

Exported Query Expression Truncated at 255 Characters
http://support.microsoft.com/default.aspx?scid=kb;en-us;207668


Workaround would be to create a temporary table for the data, append the
query to the temporary table, export the table, and then delete the
temporary table (or delete the records from the temporary table so that you
can reuse the table the next time).
--

Ken Snell
<MS ACCESS MVP>

I believe Ken Snell posted a solution to this some time back. I suggest you
create a new thread on "How to export to Excel with more than 255
characters"...

--
Duane Hookom
MS Access MVP

No, they don't have any formatting on the query fields. Just the table
fields
that are being appended to.

However... I did some more testing and made the Concatenate query into an
append to a new temp table. Everything seems to be better now within all
the
queries, but I still get the fields cut off on export. Here's my command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"TempMonthlyData", efile, True

I thought that acSpreadsheetTypeExcel9 would keep it from doing that.

--
THX cs


:

Do you have any type of Format set on the columns?

--
Duane Hookom
MS Access MVP


I agree with you and that's what I thought. The records are cut off at
the
query level after the concatenation. My guess was that during that
process
it
was combining the fields as "text" format and thus cutting off the
characters
beyond 255. But I don't know how to correct that in the function, IF
that's
the case.
--
THX cs


:

The query that you are showing should not limit the returned
CombinedNotes
to 255 characters. When you view the results of the query do you see
more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP


Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" &
[TaskNumber] &
"'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#") AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs


:

I expect the issue is your query. Can you copy your full SQL view
into
a
reply?

--
Duane Hookom
MS Access MVP


This question for Duane Hookum:

I am using your concatenation function in my access database. My
problem
is
that the parts that are combined equal more than 255 characters.
It's
cutting
off the text even though the field that I am appending into is a
Memo
field
type. Any ideas of a way I can fix this? I need to also export
this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Ken Snell \(MVP\)]

Grouping? The query that you posted does not have any grouping in it. Had
you posted the "grouping" query, where likely you were grouping on the
Concatenation field, Duane would have been able to point out the problem in
the first reply.

Grouping on (GROUP BY) a field will always cause it to truncate to 255
characters. Also, if you use a UNION (not a UNION ALL) query, truncation
will occur.

--

Ken Snell
<MS ACCESS MVP>

Finally. I had to take out the grouping from the append query and then it
seemed to work. I grouped my data separately first, then joined the
grouped
queries together and then used that join as the source for my append
query.
That seems to have done the trick. I really don't understand why though..
--
THX cs

The issue arises because Jet treats the string returned by the
Concatenate
function as an expression in the query. When you export the results of a
query to a text file or an EXCEL spreadsheet, all calculated fields
within
that query will truncate their strings to 255 characters. See this
Knowledge
Base article:

Exported Query Expression Truncated at 255 Characters
http://support.microsoft.com/default.aspx?scid=kb;en-us;207668


Workaround would be to create a temporary table for the data, append the
query to the temporary table, export the table, and then delete the
temporary table (or delete the records from the temporary table so that
you
can reuse the table the next time).
--

Ken Snell
<MS ACCESS MVP>

I believe Ken Snell posted a solution to this some time back. I suggest
you
create a new thread on "How to export to Excel with more than 255
characters"...

--
Duane Hookom
MS Access MVP


No, they don't have any formatting on the query fields. Just the table
fields
that are being appended to.

However... I did some more testing and made the Concatenate query into
an
append to a new temp table. Everything seems to be better now within
all
the
queries, but I still get the fields cut off on export. Here's my
command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"TempMonthlyData", efile, True

I thought that acSpreadsheetTypeExcel9 would keep it from doing that.

--
THX cs


:

Do you have any type of Format set on the columns?

--
Duane Hookom
MS Access MVP


I agree with you and that's what I thought. The records are cut off
at
the
query level after the concatenation. My guess was that during that
process
it
was combining the fields as "text" format and thus cutting off the
characters
beyond 255. But I don't know how to correct that in the function,
IF
that's
the case.
--
THX cs


:

The query that you are showing should not limit the returned
CombinedNotes
to 255 characters. When you view the results of the query do you
see
more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP


Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" &
[TaskNumber] &
"'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#")
AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs


:

I expect the issue is your query. Can you copy your full SQL
view
into
a
reply?

--
Duane Hookom
MS Access MVP


This question for Duane Hookum:

I am using your concatenation function in my access database.
My
problem
is
that the parts that are combined equal more than 255
characters.
It's
cutting
off the text even though the field that I am appending into
is a
Memo
field
type. Any ideas of a way I can fix this? I need to also
export
this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Cydney]

My apologies, Duane and Ken. I didn't realize it would've had an affect. The
query I posted was prior to the grouping. He asked for my concatenation
query. That's what I posted. My grouping occurs after the concatenation.

Thanks for the explanation.
--
THX cs

Grouping? The query that you posted does not have any grouping in it. Had
you posted the "grouping" query, where likely you were grouping on the
Concatenation field, Duane would have been able to point out the problem in
the first reply.

Grouping on (GROUP BY) a field will always cause it to truncate to 255
characters. Also, if you use a UNION (not a UNION ALL) query, truncation
will occur.

--

Ken Snell
<MS ACCESS MVP>

Finally. I had to take out the grouping from the append query and then it
seemed to work. I grouped my data separately first, then joined the
grouped
queries together and then used that join as the source for my append
query.
That seems to have done the trick. I really don't understand why though..
--
THX cs

The issue arises because Jet treats the string returned by the
Concatenate
function as an expression in the query. When you export the results of a
query to a text file or an EXCEL spreadsheet, all calculated fields
within
that query will truncate their strings to 255 characters. See this
Knowledge
Base article:

Exported Query Expression Truncated at 255 Characters
http://support.microsoft.com/default.aspx?scid=kb;en-us;207668


Workaround would be to create a temporary table for the data, append the
query to the temporary table, export the table, and then delete the
temporary table (or delete the records from the temporary table so that
you
can reuse the table the next time).
--

Ken Snell
<MS ACCESS MVP>

I believe Ken Snell posted a solution to this some time back. I suggest
you
create a new thread on "How to export to Excel with more than 255
characters"...

--
Duane Hookom
MS Access MVP


No, they don't have any formatting on the query fields. Just the table
fields
that are being appended to.

However... I did some more testing and made the Concatenate query into
an
append to a new temp table. Everything seems to be better now within
all
the
queries, but I still get the fields cut off on export. Here's my
command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"TempMonthlyData", efile, True

I thought that acSpreadsheetTypeExcel9 would keep it from doing that.

--
THX cs


:

Do you have any type of Format set on the columns?

--
Duane Hookom
MS Access MVP


I agree with you and that's what I thought. The records are cut off
at
the
query level after the concatenation. My guess was that during that
process
it
was combining the fields as "text" format and thus cutting off the
characters
beyond 255. But I don't know how to correct that in the function,
IF
that's
the case.
--
THX cs


:

The query that you are showing should not limit the returned
CombinedNotes
to 255 characters. When you view the results of the query do you
see
more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP


Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" &
[TaskNumber] &
"'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] & "#")
AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs


:

I expect the issue is your query. Can you copy your full SQL
view
into
a
reply?

--
Duane Hookom
MS Access MVP


This question for Duane Hookum:

I am using your concatenation function in my access database.
My
problem
is
that the parts that are combined equal more than 255
characters.
It's
cutting
off the text even though the field that I am appending into
is a
Memo
field
type. Any ideas of a way I can fix this? I need to also
export
this
information to Excel (2003).

Your function:

Option Compare Database

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

 

[Ken Snell \(MVP\)]

No harm done.... just shows how important "tiny" details sometimes are! < g
--

Ken Snell
<MS ACCESS MVP>

My apologies, Duane and Ken. I didn't realize it would've had an affect.
The
query I posted was prior to the grouping. He asked for my concatenation
query. That's what I posted. My grouping occurs after the concatenation.

Thanks for the explanation.
--
THX cs

Grouping? The query that you posted does not have any grouping in it. Had
you posted the "grouping" query, where likely you were grouping on the
Concatenation field, Duane would have been able to point out the problem
in
the first reply.

Grouping on (GROUP BY) a field will always cause it to truncate to 255
characters. Also, if you use a UNION (not a UNION ALL) query, truncation
will occur.

--

Ken Snell
<MS ACCESS MVP>

Finally. I had to take out the grouping from the append query and then
it
seemed to work. I grouped my data separately first, then joined the
grouped
queries together and then used that join as the source for my append
query.
That seems to have done the trick. I really don't understand why
though..
--
THX cs


:

The issue arises because Jet treats the string returned by the
Concatenate
function as an expression in the query. When you export the results of
a
query to a text file or an EXCEL spreadsheet, all calculated fields
within
that query will truncate their strings to 255 characters. See this
Knowledge
Base article:

Exported Query Expression Truncated at 255 Characters
http://support.microsoft.com/default.aspx?scid=kb;en-us;207668


Workaround would be to create a temporary table for the data, append
the
query to the temporary table, export the table, and then delete the
temporary table (or delete the records from the temporary table so
that
you
can reuse the table the next time).
--

Ken Snell
<MS ACCESS MVP>

I believe Ken Snell posted a solution to this some time back. I
suggest
you
create a new thread on "How to export to Excel with more than 255
characters"...

--
Duane Hookom
MS Access MVP


No, they don't have any formatting on the query fields. Just the
table
fields
that are being appended to.

However... I did some more testing and made the Concatenate query
into
an
append to a new temp table. Everything seems to be better now
within
all
the
queries, but I still get the fields cut off on export. Here's my
command:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"TempMonthlyData", efile, True

I thought that acSpreadsheetTypeExcel9 would keep it from doing
that.

--
THX cs


:

Do you have any type of Format set on the columns?

--
Duane Hookom
MS Access MVP


I agree with you and that's what I thought. The records are cut
off
at
the
query level after the concatenation. My guess was that during
that
process
it
was combining the fields as "text" format and thus cutting off
the
characters
beyond 255. But I don't know how to correct that in the
function,
IF
that's
the case.
--
THX cs


:

The query that you are showing should not limit the returned
CombinedNotes
to 255 characters. When you view the results of the query do
you
see
more
than 255? Is the issue with the transfer to Excel?

--
Duane Hookom
MS Access MVP


Here's my SQL:
SELECT qDates.Empl, qDates.TaskNumber, qDates.ThruDate,
Concatenate("SELECT
Note FROM [NEWHoursNotes-ALL] WHERE [Task Number] ='" &
[TaskNumber] &
"'
AND
[Empl] ='" & [Empl] & "' AND [ThruDate] = #" & [ThruDate] &
"#")
AS
CombinedNotes
FROM qDates
ORDER BY qDates.Empl, qDates.TaskNumber, qDates.ThruDate;

(The field "Note" is a memo field.)

--
THX cs


:

I expect the issue is your query. Can you copy your full SQL
view
into
a
reply?

--
Duane Hookom
MS Access MVP


This question for Duane Hookum:

I am using your concatenation function in my access
database.
My
problem
is
that the parts that are combined equal more than 255
characters.
It's
cutting
off the text even though the field that I am appending
into
is a
Memo
field
type. Any ideas of a way I can fix this? I need to also
export
this
information to Excel (2003).

Your function:

Option Compare Database

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