Change Query Sql with VBA

[gheyman]

This is all new to me. I am reading posts and trying to piece together how you change the Sql code of a query using VBA. Here is what I did - but it does not work. In my code the Line that starts with WHERE (((.... turned Red. When I try to run it, I get a message that reads: Compile Error: Sub or Function not defined.

Private Sub createQry()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    Dim newSQL As String
    
    newSQL = "SELECT tbl_NIS_TSL.ID_NISTSL, tbl_NIS_TSL.VendorName, tbl_NIS_TSL.VendorID, tbl_NIS_TSL.CityName, tbl_NIS_TSL.MailState, tbl_NIS_TSL.PostalCode, tbl_NIS_TSL.CountryCode, tbl_NIS_TSL.Vendor_Status, tbl_NIS_TSL.CommType, tbl_NIS_TSL.Debarred, tbl_NIS_TSL.Approval_Status, tbl_NIS_TSL.TSL_Trend, tbl_NIS_TSL.Complexity_Low, tbl_NIS_TSL.Complexity_Medium, tbl_NIS_TSL.Complexity_High, tbl_NIS_TSL.Volume_Low, tbl_NIS_TSL.Volume_Medium, tbl_NIS_TSL.Volume_High, tbl_NIS_TSL.Responsiveness_Rating, tbl_NIS_TSL.RTV_Support, tbl_NIS_TSL.Failure_Analysis, tbl_NIS_TSL.Other_Capabilities, tbl_NIS_TSL.NumberOf_Assemblies, tbl_NIS_TSL.Materials, tbl_NIS_TSL.Restrictions, tbl_NIS_TSL.Comments, tbl_NIS_TSL.CreatedBy, tbl_NIS_TSL.CreatedDate"
FROM tbl_NIS_TSL
WHERE (((tbl_NIS_TSL.VendorName) Like [Forms]![frm_NIS_TSL]![Combo227]) And ((tbl_NIS_TSL.CommType) Like [Forms]![frm_NIS_TSL]![Combo232]) And ((tbl_NIS_TSL.Debarred) Like [Forms]![frm_NIS_TSL]![Combo229]) And ((tbl_NIS_TSL.Approval_Status) Like [Forms]![frm_NIS_TSL]![Combo234]))ORDER BY tbl_NIS_TSL.VendorName, tbl_NIS_TSL.CreatedDate DESC;"
    
    
    Set qdf = db.CreateQueryDef("qry_Temp", newSQL)
End Sub

any help is appreciated

Ultimately what I would like to do is an If statement that switches the SQL from one to another

 

[gheyman]

Re: Access: Change Query Sql with VBA

When the code is too long it can be wrapped with " & _
Each line has to end with " & _
qdf.SQL = "SELECT tbl_NIS_TSL.ID_NISTSL, tbl_NIS_TSL.VendorName, tbl_NIS_TSL.VendorID, tbl_NIS_TSL.CityName, " & _
(There needs to be a space in between them)

    Private Sub updateQuery()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim qdf As DAO.QueryDef
    
    Set qdf = db.QueryDefs("qry_Temp")
    qdf.SQL = "SELECT tbl_NIS_TSL.ID_NISTSL, tbl_NIS_TSL.VendorName, tbl_NIS_TSL.VendorID, tbl_NIS_TSL.CityName, tbl_NIS_TSL.MailState, tbl_NIS_TSL.PostalCode, tbl_NIS_TSL.CountryCode, tbl_NIS_TSL.Vendor_Status, tbl_NIS_TSL.CommType, tbl_NIS_TSL.Debarred, tbl_NIS_TSL.Approval_Status, tbl_NIS_TSL.TSL_Trend, tbl_NIS_TSL.Complexity_Low, tbl_NIS_TSL.Complexity_Medium, tbl_NIS_TSL.Complexity_High, tbl_NIS_TSL.Volume_Low, tbl_NIS_TSL.Volume_Medium, tbl_NIS_TSL.Volume_High, tbl_NIS_TSL.Responsiveness_Rating, tbl_NIS_TSL.RTV_Support, tbl_NIS_TSL.Failure_Analysis, tbl_NIS_TSL.Other_Capabilities, tbl_NIS_TSL.NumberOf_Assemblies, tbl_NIS_TSL.Materials, tbl_NIS_TSL.Restrictions, tbl_NIS_TSL.Comments, tbl_NIS_TSL.CreatedBy, tbl_NIS_TSL.CreatedDate " & _
    "FROM tbl_NIS_TSL INNER JOIN SubQry_NIS_TSL ON (tbl_NIS_TSL.VendorName = SubQry_NIS_TSL.VendorName) AND (tbl_NIS_TSL.CommType = SubQry_NIS_TSL.CommType) AND (tbl_NIS_TSL.CreatedDate = SubQry_NIS_TSL.MaxOfCreatedDate)" & _
    "WHERE (((tbl_NIS_TSL.VendorName) Like [Forms]![frm_NIS_TSL]![Combo227]) And ((tbl_NIS_TSL.CommType) Like [Forms]![frm_NIS_TSL]![Combo232]) And ((tbl_NIS_TSL.Debarred) Like [Forms]![frm_NIS_TSL]![Combo229]) And ((tbl_NIS_TSL.Approval_Status) Like [Forms]![frm_NIS_TSL]![Combo234]))ORDER BY tbl_NIS_TSL.VendorName, tbl_NIS_TSL.CreatedDate DESC;"
End Sub

 

[welshgasman]

Re: Access: Change Query Sql with VBA

I always put my spaces at the beginning of the line. That way you do not have to scroll all the time to check your string.?

Also I'd start using meaningful names for controls, Combo227 is not going to mean anything to anyone 3 months down the line?

 

[Micron]

Re: Access: Change Query Sql with VBA

then there's assigning sql to a variable rather than line continuation characters. Consider aliasing a long table name that get repeated umpteen times.

Dim strSql As String

strSql = "SELECT T1.ID_NISTSL, T1.VendorName, T1.VendorID, T1.CityName, T1.MailState, T1.PostalCode, T1.CountryCode, "
strSql = strSql & "T1.Vendor_Status, T1.CommType, T1.Debarred, T1.Approval_Status, T1.TSL_Trend, 1.Complexity_Low, "
strSql = strSql & "T1.Complexity_Medium, T1.Complexity_High, T1.Volume_Low, T1.Volume_Medium, T1.Volume_High, "
strSql = strSql & "T1.Responsiveness_Rating, T1.RTV_Support, T1.Failure_Analysis, T1.Other_Capabilities, "
strSql = strSql & "T1.NumberOf_Assemblies, T1.Materials,  T1.Restrictions, T1.Comments, T1.CreatedBy, T1.CreatedDate "
strSql = strSql & "FROM tbl_NIS_TSL AS T1 INNER JOIN SubQry_NIS_TSL ON (T1.VendorName = SubQry_NIS_TSL.VendorName) "
strSql = strSql & "AND (T1.CommType = SubQry_NIS_TSL.CommType) AND (T1.CreatedDate = SubQry_NIS_TSL.MaxOfCreatedDate) "
strSql = strSql & "WHERE (((T1.VendorName) Like [Forms]![frm_NIS_TSL]![Combo227]) And ((T1.CommType) Like "
strSql = strSql & "[Forms]![frm_NIS_TSL]![Combo232]) And ((T1.Debarred) Like [Forms]![frm_NIS_TSL]![Combo229]) And "
strSql = strSql & "((T1.Approval_Status) Like [Forms]![frm_NIS_TSL]![Combo234])) "
strSql = strSql & "ORDER BY T1.VendorName, T1.CreatedDate DESC;"

Set qdf = db.CreateQueryDef("qry_Temp", strSql)

 

[welshgasman]

Re: Access: Change Query Sql with VBA

Yes I admit, I use that method rather than continuation characters.