Change Query Sql with VBA

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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)

Code:
    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
 
Last edited:
Upvote 0
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? :(
 
Upvote 0
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.
Code:
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)
 
Upvote 0
Re: Access: Change Query Sql with VBA

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

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top