Another Concatenation Query

[Dave177]

Hello

I have a query qryContactDetails which is a parameter query with the following SQL:

SELECT 1 AS SelectionID, tblPeople.ID, tblPeople.Title, tblPeople.FirstName, tblPeople.LastName, tblPeople.Department, tblPeople.JobTitle, tblPeople.BusinessStreet, tblPeople.BusinessCity, tblPeople.BusinessState, tblPeople.BusinessPostalCode, tblPeople.BusinessFax, tblPeople.BusinessPhone, tblPeople.BusinessPhone2, tblPeople.MobilePhone, tblPeople.OtherFax, tblPeople.Pager, tblPeople.EmailAddress, tblPeople.EmailType, tblPeople.EmailDisplayName, tblPeople.Initials, tblPeople.Picture, IIf(([Picture]) Is Not Null,"Picture Available","Picture Not Available") AS PictureAvailable
FROM tblPeople
WHERE (((tblPeople.Department)=[Forms]![frmContacts]![cmbDepartment]) AND ((InStr([JobTitle],[Forms]![frmContacts].[cmbInd]))<>0)) OR (((InStr([JobTitle],[Forms]![frmContacts].[cmbInd]))<>0) AND (([Forms]![frmContacts]![cmbDepartment])="*"))
GROUP BY 1, tblPeople.ID, tblPeople.Title, tblPeople.FirstName, tblPeople.LastName, tblPeople.Department, tblPeople.JobTitle, tblPeople.BusinessStreet, tblPeople.BusinessCity, tblPeople.BusinessState, tblPeople.BusinessPostalCode, tblPeople.BusinessFax, tblPeople.BusinessPhone, tblPeople.BusinessPhone2, tblPeople.MobilePhone, tblPeople.OtherFax, tblPeople.Pager, tblPeople.EmailAddress, tblPeople.EmailType, tblPeople.EmailDisplayName, tblPeople.Initials, tblPeople.Picture, IIf(([Picture]) Is Not Null,"Picture Available","Picture Not Available"), tblPeople.LastName
ORDER BY tblPeople.LastName, tblPeople.FirstName;

I wanted to then produce another query which would concatenate the EmailDisplayName fields for all those records in the above query.
I tried to lay it out in the much used Family example and that is why I made the [SelectionID] field as above. However, I still can't get it to work with the following code:

SELECT qryContactDetails.SelectionID, Concatenate("SELECT EmailDisplayName FROM qryContactDetails WHERE SelectionID =" & [SelectionID]) AS Emails
FROM qryContactDetails;

It just comes back with : "Error 3061 - too few parameters. Expected 2"

Does anybody know what is wrong??

 

[SoCalAccessPro]

Are you trying to run the query using VBA ?

If so, VBA will return that error when using a paramaeter query. You have to satisfy the parameters using the parameters property of the command object.


Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Dave177]

Tyrone,

Thanks for the response. My Concatenate function is in vba as follows (copied)

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

You said:

You have to satisfy the parameters using the parameters property of the command object.

Sorry, I'm not too great at this stuff but how do I do that? I assume it's in the brackets at the top?

Thanks for any help.

David

 

[SoCalAccessPro]

OK,So I'm assuming pstrSQL includes qryContactDetails , which includes (([Forms]![frmContacts]![cmbDepartment])="*")), right ?

You'll need to do something like this to satisfy the parameter. This may not work if you cut and paste, modify it to your situation.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

    
Set db = CurrentDb
Set qdf = db.QueryDefs("qryContactDetails")
qdf.Parameters(0) = _
  (([Forms]![frmContacts]![cmbDepartment]Set rst = 
qdf.Close

See

http://msdn.microsoft.com/en-us/library/aa160564(office.11).aspx

For more on Parameter Queries and VBA.




Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com