Query problem 1

[ZOR]

I have a query that works okay:

mysql = "SELECT * FROM ( SELECT TOP 5 TXMASTERS.ID1, TXCLIPS.ID2, "" "" AS Persons,"
mysql = mysql & " TXCLIPS.ID2, TXCLIPS.Comments, TXCLIPS.Start AS [Time In], TXCLIPS.End AS [Time Out],"
mysql = mysql & " TXCLIPS.ID1 FROM TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1 = TXCLIPS.ID1"
mysql = mysql & " WHERE (((TXCLIPS.ID1)=[FORMS]![Mainform1].[FORM].[ID1])) "
mysql = mysql & " ORDER BY TXCLIPS.ID2 DESC ) T"
mysql = mysql & " ORDER BY ID2 ASC"


However I want to include a concaniated field but pasting in the relevant part produces no output from the query. I know some records do not have names, but others do. Can someone explain how I can add this in?

AS Persons, StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) AS NName


Many thanks

 

[traingamer]

Since when is CONCATENATE a function?

Looks like you should be adding a LEFT JOIN to qselJunctionNames ON qselJunctionNames.ID2=[TXCLIPS].[ID2]

but I could be wrong...

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[ZOR]

Thanks Greg. I have a function: Hope I am not doing something wrong putting this up? Regards

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'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
'(This SQL statement assumes FamID is numeric)
'===================================
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'===================================
'
'If the FamID is a string then the SQL would be
'===================================
'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

 

[Golom]

Looks OK ... although you have TXCLIPS.ID2 in your select twice and that will result in some odd field names.

Insert this after you open the recordset in the Concatenate Function and see what is happening.

Debug.Print pstrSQL
If Not rs.EOF Then
    rs.MoveLast
    rs.MoveFirst
    Debug.Print "Records = " & rs.RecordCount
Else
    Debug.Print "No Records"
End If

 

[ZOR]

Many thanks Golam. I have been staring at the code for hours, and just noticed I had added AS Persons, StrConv(Concatenate(""SELECT Athlete FROM qselJunctionNames WHERE ID2="" & [TXCLIPS].[ID2]),3) AS NName" but had not put a comma after NName. I should have posted the whole bit that I had done and you/someone would have spotted it before me. Instead I've been pulling out what hair I've got left out!

SThanks all for the help, and thanks for your bit of code, I will put that in remmed out for future diagnosis.