SQL VBA issue when converting Query to Module (1 Viewer)

[Trevor G]

I am trying to convert a query SQL code to use in a Module to run through VBA but I am encountering an issue with Where statement. what I have in the Query is indicated below:

SELECT *
FROM TempImportedOld
WHERE (((TempImportedOld.ThisYear)=Year(DateAdd("yyyy",-1,Date()))));

Trying to use this in a module as follows:

Sub createQuery3()
Dim qdfNew As DAO.QueryDef
Dim strSQL As String
Dim strSelect1 As String

strSelect1 = "SELECT * FROM TempImportedOld " & _
" WHERE TempImportedOld.ThisYear='Year(DateAdd(""yyyy"",-1,Date()))'"
strSQL = strSelect1
With CurrentDb
.QueryDefs.Delete ("My_Query") 'Look to delete the query
Set qdfNew = .CreateQueryDef("My_Query", strSQL) 'Create the query
End With
DoCmd.OpenQuery "My_Query"
End Sub

When I run this it will fail as in the criteria of the query it shows me this with a single quote mark. 'Year(DateAdd("yyyy",-1,Date()))'

I have attached a copy of the database if someone can point me in the right direction that would be very helpful.

 

[spikepl]

Make your query the way you want in the query designer. Inspect the SQL and compare with your strSQL in the Immediate window.

 

[Trevor G]

Thank you for your advice, I have adjusted the SQL statement and it now works in the way I want it to, so I can now take it to the next level.

 

[Pat Hartman]

Embedding SQL strings in VBA is not taking it to the next level. SQL strings in code cause database bloat. It is better to leave them as querydefs.