Home > Software design >  Access VBA creating a query that uses a variant as a where condition
Access VBA creating a query that uses a variant as a where condition

Time:10-21

I am trying to write a vba code that will create a query in Access with a where condition based off of the value of a cell from another table. My issue is that when I am trying to pull a string from the varRecords(0,0) Variant it becomes a parameter for the new query and not a string. I tried using cstr() function but it was still becoming a parameter. If I use msgbox to test what is being created it appears as a string. I may have left some stuff out of the code but the basics are below.

Dim dbsEmails As DAO.Database
Dim rstEmails As DAO.Recordset
Dim varRecords As Variant
Dim RecordCounter As Long
Dim qdfTemp As QueryDef
Dim strSQL As String
Dim strSQL2 As String

Set dbsEmails = CurrentDb
strSQL = "Select Email, ID from tbl_Email"
Set rstEmails = dbsEmails.OpenRecordset(strSQL, dbOpenSnapshot)
rstEmails.MoveLast
RecordCounter = rstEmails.RecordCount
rstEmails.MoveFirst
varRecords = rstEmails.GetRows(RecordCounter)

strSQL2 = "Select ID, Stuff from tbl2 Where id =" & varRecords(0,0)

SetqdfTemp = CurrentDb.CreateQueryDef("Pending Report", strSQL2)
End Function

CodePudding user response:

Try making it a string:

strSQL2 = "Select ID, Stuff from tbl2 Where id = '" & varRecords(0,0) & "'"
  • Related