CreateQueryDef doesn't create query def

[rb608]

I'm getting back into minor database design after a long layoff, and I've forgotten too much. Alas, I'm stumped on this one.

I'm using code behind a command button on a form to create a query definition from which I'm printing a report. The query definition is based on which form controls are utilized as selection criteria. If a control on the selection form is blank, it is omitted from the query; if not, it is included. I've included the code below.

My issue is that the code seems to create the SELECT string just fine, but it does not create the query. (To verify, I'm transferring the strSelect string to a text box on the form for review.) I've also copied that string and pasted it into a blank query, and it works fine that way.

To clarify, the code as written below DOES create the query qry_ReportQuery; but it creates a blank query with no fields. The query is created, but there is no SQL string assigned.

What am I missing here? Also, if there's a simpler way to do this, I'm all ears.

FWIW - If all of the selection fields are populated, the SELECT string becomes:

SELECT tbl_Facilities.FacilityID As FacilityID, tbl_Facilities.FacName As FacName, tbl_Facilities.DistrID, tbl_Facilities.FacType, tbl_Facilities.[Address 1] As Address1, tbl_Facilities.[Address 2] as Address2, tbl_Facilities.City as City, tbl_Facilities.State as State, tbl_Facilities.[Zip Code] as Zipcode, tbl_Facilities.County as County FROM tbl_Facilities INNER JOIN tbl_Buildings ON tbl_Facilities.FacilityID = tbl_Buildings.FacilityID GROUP BY tbl_Facilities.FacilityID, tbl_Facilities.FacName, tbl_Facilities.DistrID, tbl_Facilities.FacType, tbl_Facilities.[Address 1], tbl_Facilities.[Address 2], tbl_Facilities.City, tbl_Facilities.State, tbl_Facilities.[Zip Code], tbl_Facilities.County, tbl_Buildings.BldgType HAVING (tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA1] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA2] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA3] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA4] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA5] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA6] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA7]) AND tbl_Facilities.County =[Forms]![frm_ReportMenu]![txtCounty] AND tbl_Facilities.FacilityID =[Forms]![frm_ReportMenu]![txtFacilityID] AND tbl_Buildings.BldgType =[Forms]![frm_ReportMenu]![txtBldgType]



Thanks in advance.



Private Sub Command101_Click()
Dim strSelect As String, qdfReport As QueryDef, dbsA As DAO.Database


'
' Create initial string specifying SHA Districts
'
strString = "SELECT tbl_Facilities.FacilityID As FacilityID, tbl_Facilities.FacName As FacName, tbl_Facilities.DistrID, tbl_Facilities.FacType, tbl_Facilities.[Address 1] As Address1, tbl_Facilities.[Address 2] as Address2, tbl_Facilities.City as City, tbl_Facilities.State as State, tbl_Facilities.[Zip Code] as Zipcode, tbl_Facilities.County as County " _
& " FROM tbl_Facilities INNER JOIN tbl_Buildings ON tbl_Facilities.FacilityID = tbl_Buildings.FacilityID " _
& "GROUP BY tbl_Facilities.FacilityID, tbl_Facilities.FacName, tbl_Facilities.DistrID, tbl_Facilities.FacType, tbl_Facilities.[Address 1], tbl_Facilities.[Address 2], tbl_Facilities.City, tbl_Facilities.State, tbl_Facilities.[Zip Code], tbl_Facilities.County, tbl_Buildings.BldgType " _
& "HAVING (tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA1] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA2] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA3] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA4] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA5] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA6] Or tbl_Facilities.DistrID=[Forms]![frm_ReportMenu]![txtchkSHA7])"
'
' If a county is selected on the form, add criteria to string
'
If Forms!frm_ReportMenu!txtCounty <> "" Then strString = strString & " AND tbl_Facilities.County =[Forms]![frm_ReportMenu]![txtCounty]"
'
'If a Facility is selected on the form, add criteria to string
'
If Forms!frm_ReportMenu!txtFacilityName <> "" Then strString = strString & " AND tbl_Facilities.FacilityID =[Forms]![frm_ReportMenu]![txtFacilityID]"
'
'If a building type is selected on the form, add criteria to string
'
If Forms!frm_ReportMenu!txtBldgType <> "" Then strString = strString & " AND tbl_Buildings.BldgType =[Forms]![frm_ReportMenu]![txtBldgType]"
'
' Show final string in text box
'
Me!txtString = strString
'
' Create report query
'
Set dbsA = CurrentDb
DoCmd.DeleteObject acQuery, "qry_ReportQuery"
Set qdfReport = dbsA.CreateQueryDef("qry_ReportQuery", strSelect)
'
' Print report
'
DoCmd.OpenReport "rpt_OverallReport", acViewPreview
'
End Sub

[Robeen]

You're using strSelect [declared] in your CreateQueryDef . . . and strString [not declared] to build your SQL string.
You're populating the strString - but not the strSelect.

This [in red]:

Set qdfReport = dbsA.CreateQueryDef("qry_ReportQuery", strSelect)

could be your problem.

Hope this helps.

[rb608]

D'oh! I knew it had to be something like that. Thanks for the second pair of eyes.