Creating Query from Code, Concatenate fields

[eww]

I'm having problems with a query I'm building from a SQL statement in VBA.

First I build the query using:

        Dim dbs As Database
        Dim strSQL
        Dim strQueryName As String
        Dim qryDef As QueryDef
            Set dbs = CurrentDb
            strQueryName = "qryBacklog"
            For Each qryDef In dbs.QueryDefs
                If qryDef.Name = strQueryName Then
                    dbs.QueryDefs.Delete strQueryName
                    Exit For
                End If
            Next

Then use a string to build the (numerous) fields:

                strSQL = "SELECT dbo_tblJobInfo.FabLoc, dbo_tblJobInfo.JobNum, dbo_tblJobInfo.JobName, dbo_tblJobInfo.Cstmr1, " & _
                "dbo_tblEmployees.FirstName & " " & dbo_tblEmployees.LastName AS Detlr, dbo_tblJobInfo.Sales, " & _
                "dbo_tblMrktCat.MrktCatDesc AS MrktCat, dbo_tblJobInfo.BillType, dbo_tblJobInfo.LastInvoice, " & _
                "dbo_tblJobInfo.EstWgt, dbo_tblJobInfo.AdjWgt, dbo_tblJobInfo.DelWgt, dbo_tblJobInfo.BalToDet, dbo_tblJobInfo.EstLastDel, " & _
                "dbo_tblJobInfo.Comments, dbo_tblJobInfo.DetStatus, dbo_tblJobInfo.JobStatus, Sum(dbo_tblSubmittals.DetWgt) AS DetWgt, " & _
                "([DelWgt]/([DetWgt]+[BalToDet])) AS PercDel, ([DetWgt]/([DetWgt]+[BalToDet])) AS PercDet, " & _
                "([AdjWgt]-([DetWgt]+[BalToDet])) AS OverUnder, ([AdjWgt]-[DelWgt]) AS BalToDel " & _
                "FROM dbo_tblEmployees INNER JOIN (dbo_tblMrktCat INNER JOIN (dbo_tblJobInfo LEFT JOIN dbo_tblSubmittals " & _
                "ON dbo_tblJobInfo.JobNum = dbo_tblSubmittals.JobNum) ON dbo_tblMrktCat.MrktCatCode = dbo_tblJobInfo.MrktCat) ON dbo_tblEmployees.Username = dbo_tblJobInfo.Detlr1 " & _
                "GROUP BY dbo_tblJobInfo.FabLoc, dbo_tblJobInfo.JobNum, dbo_tblJobInfo.JobName, dbo_tblJobInfo.Cstmr1, Detlr, " & _
                "dbo_tblJobInfo.Sales, dbo_tblMrktCat.MrktCatDesc, dbo_tblJobInfo.BillType, dbo_tblJobInfo.LastInvoice, dbo_tblJobInfo.EstWgt, " & _
                "dbo_tblJobInfo.AdjWgt, dbo_tblJobInfo.DelWgt, dbo_tblJobInfo.BalToDet, dbo_tblJobInfo.EstLastDel, dbo_tblJobInfo.Comments, " & _
                "dbo_tblJobInfo.DetStatus, dbo_tblJobInfo.JobStatus " & _
                "HAVING (((dbo_tblJobInfo.FabLoc) =" & lngFabDBID1 & ") And ((dbo_tblJobInfo.JobStatus) ='" & "Open" & "' Or (dbo_tblJobInfo.JobStatus) ='" & "On Hold" & "')) Or (((dbo_tblJobInfo.FabLoc) =" & lngFabDBID2 & ") And ((dbo_tblJobInfo.JobStatus) ='" & "Open" & "' Or (dbo_tblJobInfo.JobStatus) ='" & "On Hold" & "')) Or (((dbo_tblJobInfo.FabLoc) =" & lngFabDBID3 & ") And ((dbo_tblJobInfo.JobStatus) ='" & "Open" & "' Or (dbo_tblJobInfo.JobStatus) ='" & "On Hold" & "')) " & _
                "ORDER BY dbo_tblJobInfo.JobNum;"

Then finish the code off:

            Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)

Now the code is failing when trying to concatenate the Name fields highlighted above. I've tried to recreate the query through the query design, view the SQL, all of that and this is how it looks, however once I pair it with the string and quotations it just gets a little crazy.

[pbaldy]

Try

"dbo_tblEmployees.FirstName & ' ' & dbo_tblEmployees.LastName AS Detlr, dbo_tblJobInfo.Sales, " & _

[eww]

I think that's one of the many variations that I tried previous to posting this, but I went ahead and tried it again just in case and sure enough I got an error again:

"You tried to execute a query that does not include the specified expression 'dbo_tblEmployees.FirstName & ' ' & dbo_tblEmployees.LastName' as part of an aggregate function.

[pbaldy]

That's a whole 'nother issue. That one is because the field is not in the GROUP BY clause and isn't aggregated (Sum, Max, etc). Add it to the GROUP BY clause.

[eww]

I was just looking into that but I'm not sure how to add it to the GROUP BY clause...

Looks like it's as simple as adding that exact highlighted part into the GROUP BY section. I wouldn't have thought so (it seems to work fine as an Expression in a regular query so I wasn't sure that I needed to add it there) but looks like it solved the problem.

Thanks Paul.

[pbaldy]

In a "totals" query (one with a GROUP BY clause), every field in the SELECT clause must either be part of an aggregate function (Sum, Max, etc) or be included in the GROUP BY clause. That one wasn't. If you took

dbo_tblJobInfo.FabLoc

out of the GROUP BY clause you'd get the same error, so it doesn't really have anything to do with being an expression.