I have a query that does what I want it to when it calls this function, but it takes at least 10 minutes to compile right now, and if you scroll then it starts all over again, so the only solution is to copy/paste into a table or excel after the first execution. If anyone can provide some solutions for more efficient code, that would be wonderful. You can find a link to my previous problem which the code was provided here: http://www.experts-exchange.com/Databases/MS_Access/Q_21912158.html
Here is the code I have now.
Public Function Concat(CoyID As Double, fld As String) As String
Dim mydb As DAO.Database, myrs As DAO.Recordset, strSQL As String
strSQL = "SELECT " & fld & " FROM myQry WHERE CompanyID = " & CoyID & ";"
Set mydb = CurrentDb
Set myrs = mydb.OpenRecordset(strSQL)
Do While Not myrs.EOF
If myrs.Fields(0).Value <> "" Then
Concat = Concat & Trim(myrs.Fields(0).Value)
& ", "
Set myrs = Nothing
Set mydb = Nothing
If Len(Concat) <> 0 Then
Concat = Left(Concat, Len(Concat) - 2)
This is the query SQL:
SELECT myQry.CompanyID, Concat(CompanyID,"ProjMgr"
) AS ProjMgr, Concat(CompanyID,"ProgMgr"
) AS ProgMgr, Concat(CompanyID,"Coach") AS Coach, Concat(CompanyID,"ProjAdm"
) AS ProjAdm, myQry.vchCompanyName, myQry.vchAddress1, myQry.vchCity, myQry.chRegionCode, myQry.chCountryCode, myQry.MinOfFoM, myQry.MaxOfFoM, myQry.WSDate
GROUP BY myQry.CompanyID, myQry.vchCompanyName, myQry.vchAddress1, myQry.vchCity, myQry.chRegionCode, myQry.chCountryCode, myQry.MinOfFoM, myQry.MaxOfFoM, myQry.WSDate
ORDER BY myQry.CompanyID, myQry.WSDate, myQry.vchAddress1, myQry.vchCity;
I know that conditionals and loops require a lot of processing time, but I'm not fresh enough on my programming, and don't have too much time to devote to this to improve it myself. Any help is appreciated, and thank you in advance.