Save VBA Where Statement in Querydef (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 10:01
Joined
Dec 20, 2007
Messages
2,061
This string Variable is created by concatenating the value of a combo box and the results of a multi-select list. They could contain several other addition filters but it is like this for simplicity.

The variable is strWhere

Code:
debug.print  strWhere
returns this:

Code:
 ([lrStandardSpecificationID] = 147) AND lrLabResultStatusID IN (4,3)

Code:
Dim Q As QueryDef, DB As Database
        Set DB = CurrentDb()
        Set Q = DB.QueryDefs("qryLaboratoryRecordListExcel")
        Q.SQL = "Select * From qryLaboratoryRecordList Where strWhere "
      
Debug.Print Q.SQL
returns this

Code:
Select * From qryLaboratoryRecordList Where strWhere


If I manually replace the strWhere with the outfrom the Debug.Print strWhere and paste it in a sql Statement of a querydef it works perfectly.

Can someone tell me what I am doing wrong please?

Thanks
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:01
Joined
Feb 19, 2013
Messages
16,555
you need to concatenate the strings together

Q.SQL = "Select * From qryLaboratoryRecordList Where " & strWhere
 

Thales750

Formerly Jsanders
Local time
Today, 10:01
Joined
Dec 20, 2007
Messages
2,061
you need to concatenate the strings together

Q.SQL = "Select * From qryLaboratoryRecordList Where " & strWhere

Wow, that is so sad I missed that. Thank you
 

Users who are viewing this thread

Top Bottom