A little, but ... look back at the questions I asked. Did you answer them
all? Again, I am not going to be able to provide a working solution.
So have you downloaded the sample database containing the function you need?
Once you import the module containing the function, you'll be able to use it
in a query. The idea is to create two saved queries, the first to calculate
the wins and losses per year:
select [year],player, sum(wins),sum(losses)
from tablename
group by [year],player
and a second that uses Duane's function (I forget the syntax so you will
need to look at the documentation to get it exactly right):
select [year],player,
Concatenate("tablename", "[year],player","Team") As Teams
from tablename
group by [year],player
Then join the two queries in a third query.
Alternatively,
a. you could forget about concatenating and use the crosstab query wizard to
create a saved query to pivot the teams. Then join the two queries as above.
b. you could forget about concatenating and simply display the teams ina
subreport in the report that uses the first query to show the totals.
Incidently, "Year" is a reserved keyword and should not be used for a field
name. As it is the name of a VBA function, its use as a field name can lead
to very hard to debug problems down the line. At the very least, you need to
remember to use brackets around it when you use it in queries. A better
solution is to simply rename the field, making it more descriptive so that
two years from now, you'll be able to tell from looking at it what it'sthe
year of.- Hide quoted text -
tried using that concatenate function, but when i then run the query
i get an error. Missing syntax error (missing operator) in query
expression 'PlayerID=Tim Cooney'
here is my actual query sql
SELECT Archive_tbl_Final_Stats_Totals.PlayerID, Concatenate("SELECT
[Team Name] FROM qryAll_Teams WHERE PlayerID =" & [PlayerID]) AS
[Team(s)]
FROM Archive_tbl_Final_Stats_Totals;
here is the function syntax
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
The error shows during the
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
part of the function. At this point i am clueless.- Hide quoted text -
- Show quoted text -
