Concatenation of Text as Expression In A Query

The 2nd portion of the query gives me this -------

ORDER_NO ORDER_LINE TextLines
0000472 0010 EFGH, EFGH
0000472 0030 XYZ, XYZ

instead of this -----------

ORDER_NO ORDER_LINE TextLines
0000472 0010 ABCD, EFGH
0000472 0030 UVW, XYZ

It seems to be ignoring the TEXT_SEQ ORDER BY

Just for my comfort, please post back with the SQL views of your queries as
well as the code in your Concatenate function. Have you attempted to set a
break point and step through the code?

--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
...
The 2nd portion of the query gives me this -------

ORDER_NO ORDER_LINE TextLines
0000472 0010 EFGH, EFGH
0000472 0030 XYZ, XYZ

instead of this -----------

ORDER_NO ORDER_LINE TextLines
0000472 0010 ABCD, EFGH
0000472 0030 UVW, XYZ

It seems to be ignoring the TEXT_SEQ ORDER BY

SQL for 1st Query ------

SELECT V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE
FROM V_ORDER_TEXT
WHERE (((V_ORDER_TEXT.ORDER_NO)="0000472") AND
((V_ORDER_TEXT.ORDER_LINE)"ZZZZ"))
GROUP BY V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE;

SQL for 2nd Query -------

SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" & ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as TextLines
FROM qselOrders;

The code from your module:

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

If I do need to do something extra with the code, I wouldn't know what that
would be since I'm not familiar with the VBA side.


"Duane Hookom" wrote:

Just for my comfort, please post back with the SQL views of your queries as
well as the code in your Concatenate function. Have you attempted to set a
break point and step through the code?

--
Duane Hookom
MS Access MVP
--

To insert a break point, you would click in the vertical bar to the left of
your code. For instance, you could click to the left of
| |
| O | With rs
| |
This will add the dot and highlight the line of code. When you run the
query, the code will pause and allow you to see the value of variables by
placing your mouse over the code. You can step through the code by pressing
F8 for one line at a time or F5 to process until the next break point or the
end of the code.

If you are totally lost, I would consider getting a sampling of your data in
a compacted and zipped MDB file. You would need to send me a private email
if you would like to do this.

--
Duane Hookom
MS Access MVP
--

"la knight" wrote in message
...
SQL for 1st Query ------

SELECT V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE
FROM V_ORDER_TEXT
WHERE (((V_ORDER_TEXT.ORDER_NO)="0000472") AND
((V_ORDER_TEXT.ORDER_LINE)"ZZZZ"))
GROUP BY V_ORDER_TEXT.ORDER_NO, V_ORDER_TEXT.ORDER_LINE;

SQL for 2nd Query -------

SELECT ORDER_NO, ORDER_LINE,
Concatenate("SELECT [TEXT] FROM V_ORDER_TEXT WHERE ORDER_NO =""" &
ORDER_NO
& """ AND ORDER_LINE=""" & ORDER_LINE & """ ORDER BY TEXT_SEQ") as
TextLines
FROM qselOrders;

The code from your module:

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

If I do need to do something extra with the code, I wouldn't know what
that
would be since I'm not familiar with the VBA side.


"Duane Hookom" wrote:

Just for my comfort, please post back with the SQL views of your queries
as
well as the code in your Concatenate function. Have you attempted to set
a
break point and step through the code?

--
Duane Hookom
MS Access MVP
--