Concatenate Function for unlimited characters

A

Alex

I'm using a Concatenate Function, that works great. The only problem is that
the new field created can only fit a certain number of characters. So not
all the data will be presented there. Is there a way to increase the number
of charcters in the new created query field so all my values would be shown.
Here is the function I used.

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
 
D

Duane Hookom

There is no real limit to the number of characters returned. If you are
experiencing a limit of 255 characters, I expect this is caused by something
else. If this is happening in a query, we need to see the SQL view. If in a
report, how about information regarding report section and control
properties. If this is from the result of an export, you should tell us.
 
A

Alex

The function is used in a new created field in a query.

Here is the SQL view

SELECT ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT Database
FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database") AS Databases
FROM ofa_user
GROUP BY ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT
Database FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database");
 
D

Duane Hookom

You have a "GROUP BY" clause which is causing the truncation. You might first
create a query qgrpUsers:
SELECT ofa_user.ID, ofa_user.LastName, ofa_user.First_Name
FROM ofa_user
GROUP BY ofa_user.LastName, ofa_user.First_Name;

Then use the function:
SELECT LastName, First_Name, Concatenate("SELECT Database
FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database") AS Databases
FROM qgrpUsers;

--
Duane Hookom
Microsoft Access MVP


Alex said:
The function is used in a new created field in a query.

Here is the SQL view

SELECT ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT Database
FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database") AS Databases
FROM ofa_user
GROUP BY ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT
Database FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database");



Duane Hookom said:
There is no real limit to the number of characters returned. If you are
experiencing a limit of 255 characters, I expect this is caused by something
else. If this is happening in a query, we need to see the SQL view. If in a
report, how about information regarding report section and control
properties. If this is from the result of an export, you should tell us.
Click to expand...
Click to expand...
 
A

Alex

I came with the same results as previously before. Also, in the query
qgrpUsers I shuld be group ofa_user.ID correct?

Duane Hookom said:
You have a "GROUP BY" clause which is causing the truncation. You might first
create a query qgrpUsers:
SELECT ofa_user.ID, ofa_user.LastName, ofa_user.First_Name
FROM ofa_user
GROUP BY ofa_user.LastName, ofa_user.First_Name;

Then use the function:
SELECT LastName, First_Name, Concatenate("SELECT Database
FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database") AS Databases
FROM qgrpUsers;

--
Duane Hookom
Microsoft Access MVP


Alex said:
The function is used in a new created field in a query.

Here is the SQL view

SELECT ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT Database
FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database") AS Databases
FROM ofa_user
GROUP BY ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT
Database FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database");



Duane Hookom said:
There is no real limit to the number of characters returned. If you are
experiencing a limit of 255 characters, I expect this is caused by something
else. If this is happening in a query, we need to see the SQL view. If in a
report, how about information regarding report section and control
properties. If this is from the result of an export, you should tell us.

--
Duane Hookom
Microsoft Access MVP


:

I'm using a Concatenate Function, that works great. The only problem is that
the new field created can only fit a certain number of characters. So not
all the data will be presented there. Is there a way to increase the number
of charcters in the new created query field so all my values would be shown.
Here is the function I used.

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
Click to expand...
Click to expand...
Click to expand...
 
A

Alex

Actually, I didn't come with the same results. It did work. But, it
appeared it didn't when I pasted it in Excel. What would be the best way to
export this data into Excel with the same results in the query.

Duane Hookom said:
You have a "GROUP BY" clause which is causing the truncation. You might first
create a query qgrpUsers:
SELECT ofa_user.ID, ofa_user.LastName, ofa_user.First_Name
FROM ofa_user
GROUP BY ofa_user.LastName, ofa_user.First_Name;

Then use the function:
SELECT LastName, First_Name, Concatenate("SELECT Database
FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database") AS Databases
FROM qgrpUsers;

--
Duane Hookom
Microsoft Access MVP


Alex said:
The function is used in a new created field in a query.

Here is the SQL view

SELECT ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT Database
FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database") AS Databases
FROM ofa_user
GROUP BY ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT
Database FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database");



Duane Hookom said:
There is no real limit to the number of characters returned. If you are
experiencing a limit of 255 characters, I expect this is caused by something
else. If this is happening in a query, we need to see the SQL view. If in a
report, how about information regarding report section and control
properties. If this is from the result of an export, you should tell us.

--
Duane Hookom
Microsoft Access MVP


:

I'm using a Concatenate Function, that works great. The only problem is that
the new field created can only fit a certain number of characters. So not
all the data will be presented there. Is there a way to increase the number
of charcters in the new created query field so all my values would be shown.
Here is the function I used.

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
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I don't know how to get all the data into Excel and would need to either test
a bunch of stuff or search google for an answer. It seems we are both at
about the same level of knowledge on this so if someone else doesn't reply,
you need to do some work.
--
Duane Hookom
Microsoft Access MVP


Alex said:
Actually, I didn't come with the same results. It did work. But, it
appeared it didn't when I pasted it in Excel. What would be the best way to
export this data into Excel with the same results in the query.

Duane Hookom said:
You have a "GROUP BY" clause which is causing the truncation. You might first
create a query qgrpUsers:
SELECT ofa_user.ID, ofa_user.LastName, ofa_user.First_Name
FROM ofa_user
GROUP BY ofa_user.LastName, ofa_user.First_Name;

Then use the function:
SELECT LastName, First_Name, Concatenate("SELECT Database
FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database") AS Databases
FROM qgrpUsers;

--
Duane Hookom
Microsoft Access MVP


Alex said:
The function is used in a new created field in a query.

Here is the SQL view

SELECT ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT Database
FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database") AS Databases
FROM ofa_user
GROUP BY ofa_user.LastName, ofa_user.First_Name, Concatenate("SELECT
Database FROM ofa_user WHERE ID =" & [ID] & " ORDER BY Database");



:

There is no real limit to the number of characters returned. If you are
experiencing a limit of 255 characters, I expect this is caused by something
else. If this is happening in a query, we need to see the SQL view. If in a
report, how about information regarding report section and control
properties. If this is from the result of an export, you should tell us.

--
Duane Hookom
Microsoft Access MVP


:

I'm using a Concatenate Function, that works great. The only problem is that
the new field created can only fit a certain number of characters. So not
all the data will be presented there. Is there a way to increase the number
of charcters in the new created query field so all my values would be shown.
Here is the function I used.

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
Click to expand...
Click to expand...
Click to expand...
 

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

2007 Concatenation problems 2
Query within a query 4
Error Message trying to concatenate from multiple records to a field 9
character limits 4
Concatenate Function 7
concatenate function 6
Concatenating a series of rows in a table 2
No value given for one or more required parameters 3

Top