concatenate function

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
 
J

John Spencer

Try the following.

Note that the & was replaced with the word AND and apostrophes were used to
delimit the specific string values of MU and Slave.

Concatenate("SELECT PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo FROM
PaymentsRoyalties WHERE PaymentsRoyalties.Use='MU' AND
PaymentsRoyalties.RecordType='Slave' ORDER BY PaymentsRoyalties.SerialNo,
PaymentsRoyalties.DiscNo",", ")

Obviously you need to replace the expression in both the SELECT and GROUP BY clauses
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
Click to expand...
 
S

shank

Thanks! That helped but now I'm having a problem not seeing the data the way
I want to. I would like to make this data:

Use ... RecordType ... SerialNo ... Agent ... PayableTo ... DiscNo
MU ... Slave ............. 14077 .... CL ........ CL .............. 3303
MU ... Slave ............. 14077 .... CL ........ CL .............. 7304
MU ... Slave ............. 14077 .... CR ........ CR .............. 3303
MU ... Slave ............. 14077 .... CR ........ CR .............. 7304

Look like this:
Use ... RecordType ... SerialNo ... Agent ... PayableTo ... DiscNo ... Discs
MU ... Slave ............. 14077 .... CL ........ CL .............. 3303
....... 3303, 7304
MU ... Slave ............. 14077 .... CR ........ CR .............. 7304
....... 3303, 7304

This query:
Discs: Concatenate("SELECT PaymentsRoyalties.DiscNo FROM PaymentsRoyalties
GROUP BY PaymentsRoyalties.DiscNo, PaymentsRoyalties.Use,
PaymentsRoyalties.RecordType, PaymentsRoyalties.SerialNo,
PaymentsRoyalties.Agent, PaymentsRoyalties.PayableTo HAVING
PaymentsRoyalties.Use='MU' AND PaymentsRoyalties.RecordType='Slave' AND
PaymentsRoyalties.SerialNo=14077",", ")

Results in this:
Use ... RecordType ... SerialNo ... Agent ... PayableTo ... DiscNo ... Discs
MU ... Slave ............. 14077 .... CL ........ CL .............. 3303
....... 3303, 7304, 3303, 7304
MU ... Slave ............. 14077 .... CR ........ CR .............. 3303
....... 3303, 7304, 3303, 7304

What am I missing here?
thanks!

= = = = = = = = = = = = = = = = = = = = = = = = = =
John Spencer said:
Try the following.

Note that the & was replaced with the word AND and apostrophes were used
to
delimit the specific string values of MU and Slave.

Concatenate("SELECT PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo
FROM
PaymentsRoyalties WHERE PaymentsRoyalties.Use='MU' AND
PaymentsRoyalties.RecordType='Slave' ORDER BY PaymentsRoyalties.SerialNo,
PaymentsRoyalties.DiscNo",", ")

Obviously you need to replace the expression in both the SELECT and GROUP
BY clauses
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
Click to expand...
Click to expand...
 
S

shank

Explaining is tougher than the original solution. Simply put, instead of
trying to do many things in one step, I created 3 different queries and
temptables to get the results I needed.
thanks!
 
J

John Spencer

You might try the following:

Discs: Concatenate("SELECT DISTINCT PaymentsRoyalties.DiscNo
FROM PaymentsRoyalties
WHERE PaymentsRoyalties.Use='MU'
AND PaymentsRoyalties.RecordType='Slave' AND
PaymentsRoyalties.SerialNo=14077
ORDER BY PaymentsRoyalties",", ")
Thanks! That helped but now I'm having a problem not seeing the data the way
I want to. I would like to make this data:

Use ... RecordType ... SerialNo ... Agent ... PayableTo ... DiscNo
MU ... Slave ............. 14077 .... CL ........ CL .............. 3303
MU ... Slave ............. 14077 .... CL ........ CL .............. 7304
MU ... Slave ............. 14077 .... CR ........ CR .............. 3303
MU ... Slave ............. 14077 .... CR ........ CR .............. 7304

Look like this:
Use ... RecordType ... SerialNo ... Agent ... PayableTo ... DiscNo ... Discs
MU ... Slave ............. 14077 .... CL ........ CL .............. 3303
...... 3303, 7304
MU ... Slave ............. 14077 .... CR ........ CR .............. 7304
...... 3303, 7304

This query:
Discs: Concatenate("SELECT PaymentsRoyalties.DiscNo FROM PaymentsRoyalties
GROUP BY PaymentsRoyalties.DiscNo, PaymentsRoyalties.Use,
PaymentsRoyalties.RecordType, PaymentsRoyalties.SerialNo,
PaymentsRoyalties.Agent, PaymentsRoyalties.PayableTo HAVING
PaymentsRoyalties.Use='MU' AND PaymentsRoyalties.RecordType='Slave' AND
PaymentsRoyalties.SerialNo=14077",", ")

Results in this:
Use ... RecordType ... SerialNo ... Agent ... PayableTo ... DiscNo ... Discs
MU ... Slave ............. 14077 .... CL ........ CL .............. 3303
...... 3303, 7304, 3303, 7304
MU ... Slave ............. 14077 .... CR ........ CR .............. 3303
...... 3303, 7304, 3303, 7304

What am I missing here?
thanks!

= = = = = = = = = = = = = = = = = = = = = = = = = =
John Spencer said:
Try the following.

Note that the & was replaced with the word AND and apostrophes were used
to
delimit the specific string values of MU and Slave.

Concatenate("SELECT PaymentsRoyalties.SerialNo, PaymentsRoyalties.DiscNo
FROM
PaymentsRoyalties WHERE PaymentsRoyalties.Use='MU' AND
PaymentsRoyalties.RecordType='Slave' ORDER BY PaymentsRoyalties.SerialNo,
PaymentsRoyalties.DiscNo",", ")

Obviously you need to replace the expression in both the SELECT and GROUP
BY clauses
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
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

Undefined function 'concatenate' in expression 0
character limits 4
Concatenating not working with primary key as text field 2
edit function to accommdate integer 1
Query within a query 4
2007 Concatenation problems 2
user defined not defined 1
No value given for one or more required parameters 3

Top