S
shank
The below function was modified for me a couple years ago.
I've been using it frequently for various tasks without issue.
Today, I've been trying to use it for the below query and I'm getting the
following error.
"No value given for one or more required parameters."
Can you lend some insight?
thanks!
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - -
SELECT PaymentsRoyalties.SerialNo, PaymentsRoyalties.Use,
PaymentsRoyalties.RecordType, PaymentsRoyalties.DiscNo, Concatenate("SELECT
PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo FROM PaymentsRoyalties
WHERE PaymentsRoyalties.Use=MU & PaymentsRoyalties.RecordType=Slave ORDER BY
PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo",", ") AS Discs INTO
TempDiscNoConcat
FROM PaymentsRoyalties
GROUP BY PaymentsRoyalties.SerialNo, PaymentsRoyalties.Use,
PaymentsRoyalties.RecordType, PaymentsRoyalties.DiscNo, Concatenate("SELECT
PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo FROM PaymentsRoyalties
WHERE PaymentsRoyalties.Use=MU & PaymentsRoyalties.RecordType=Slave ORDER BY
PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo",", ")
HAVING (((PaymentsRoyalties.SerialNo)=14077) AND
((PaymentsRoyalties.Use)="MU") AND ((PaymentsRoyalties.RecordType)="Slave")
AND ((PaymentsRoyalties.DiscNo)>0));
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - -
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
I've been using it frequently for various tasks without issue.
Today, I've been trying to use it for the below query and I'm getting the
following error.
"No value given for one or more required parameters."
Can you lend some insight?
thanks!
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - -
SELECT PaymentsRoyalties.SerialNo, PaymentsRoyalties.Use,
PaymentsRoyalties.RecordType, PaymentsRoyalties.DiscNo, Concatenate("SELECT
PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo FROM PaymentsRoyalties
WHERE PaymentsRoyalties.Use=MU & PaymentsRoyalties.RecordType=Slave ORDER BY
PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo",", ") AS Discs INTO
TempDiscNoConcat
FROM PaymentsRoyalties
GROUP BY PaymentsRoyalties.SerialNo, PaymentsRoyalties.Use,
PaymentsRoyalties.RecordType, PaymentsRoyalties.DiscNo, Concatenate("SELECT
PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo FROM PaymentsRoyalties
WHERE PaymentsRoyalties.Use=MU & PaymentsRoyalties.RecordType=Slave ORDER BY
PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo",", ")
HAVING (((PaymentsRoyalties.SerialNo)=14077) AND
((PaymentsRoyalties.Use)="MU") AND ((PaymentsRoyalties.RecordType)="Slave")
AND ((PaymentsRoyalties.DiscNo)>0));
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - -
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