Converting query builder SQL to VBA (1 Viewer)

[dmorgan20]

Morning

I am trying to put query builder SQL in to VBA but struggling.

Current code is:

SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field12, Count(tbl_plans_yesterday.[User ID]) AS cntUID, ([field7]+[field12]) AS calls, Format([cntUID]/[calls],"Percent") AS eff, [_tbl_Structure].Supervisor
FROM (_tbl_Structure INNER JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5) INNER JOIN tbl_plans_yesterday ON tbl_Genesys_Daily.Field5 = tbl_plans_yesterday.[User ID]
GROUP BY [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field12, [_tbl_Structure].Supervisor
HAVING ((([_tbl_Structure].Supervisor)=[Forms]![frm_Manager_Stats_NEW]![Text279]));

When I convert it to VBA it clearly needs to be set differently, this is what I have that's not working:

CODE:

With CurrentDb.CreateQueryDef("", "SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field12, Count(tbl_plans_yesterday.[User ID]) AS cntUID, ([field7]+[field12]) AS calls, Format([cntUID]/[calls],"Percent") AS eff, [_tbl_Structure].Supervisor FROM (_tbl_Structure INNER JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5) INNER JOIN tbl_plans_yesterday ON tbl_Genesys_Daily.Field5 = tbl_plans_yesterday.[User ID] GROUP BY [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field12, [_tbl_Structure].Supervisor WHERE ((([_tbl_Structure].Supervisor)=?));")
    .Parameters(0) = [Forms]![frm_Manager_Stats_NEW]![Text279]
    Set rs = .OpenRecordset
End With

 

[dmorgan20]

I have solved it:

With CurrentDb.CreateQueryDef("", "SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field12, Count(tbl_plans_yesterday.[User ID]) AS cntUID, ([field7]+[field12]) AS calls, Format([cntUID]/[calls],'Percent') AS eff, [_tbl_Structure].Supervisor FROM (_tbl_Structure INNER JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5) INNER JOIN tbl_plans_yesterday ON tbl_Genesys_Daily.Field5 = tbl_plans_yesterday.[User ID] GROUP BY [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field7, tbl_Genesys_Daily.Field12, [_tbl_Structure].Supervisor HAVING ((([_tbl_Structure].Supervisor)=?));")
    .Parameters(0) = [Forms]![frm_Manager_Stats_NEW]![Text279]
    Set rs1 = .OpenRecordset
End With