passing Variable to WHERE clause in VBA

[zmsm18]

I'm trying to pass a variable into the WHERE clause, but it is not recognizing it. Is it possible to do this?
If I use the WHERE that is commented out in the code below it works, but If I try to use the strCode variable inplace of it, it will not work. I have played around with ' and " aroung the variable but can't get it to work.

I put in the Me![Text5] = strCode to verify on the form that the string is what I want. The [01BO] is the acutal name of the field in the table. I'm looking for [01BO] to have a value of -1. The user selects the field on the form

Any help would be appreciated

Private Sub Command1_Click()
Dim sqlReq As String
Dim strCode

strCode = "tbl_PersTrRequired." & [Forms]![FrmTest]![txtCode].[Value]
Me![Text3] = strCode

sqlReq = "SELECT forms!FrmTest!txtCode.value AS code INTO code " _
& " FROM tbl_PersTrRequired " _
& " WHERE (((strCode)=""-1"")) ;"

DoCmd.RunSQL sqlReq

' This works:
' & "WHERE (((tbl_PersTrRequired.[01B0])=""-1""));"
----------------------------------------------------
Thanks
Mark

 

[SkipVought]

Hi,

sqlReq = "SELECT forms!FrmTest!txtCode.value AS code INTO code  " _
& " FROM tbl_PersTrRequired " _
& " WHERE  [b][01BO] ='" & strCode & "'[/b];"

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[PHV]

& " WHERE " & strCode & "=-1"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zmsm18]

Close,
but still not working. I suspect the problem is that the variables end up with quotes around them, but what I need is just the value without quotes
What I want to end up with:

WHERE tbl_PersTrRequired.01BO = "-1"

What I suspect I am getting:

WHERE " tbl_PersTrRequired.01BO " = " -1