Passing a varaible to SQL statement

[cpopham]

My user wants certain criteria to be in a combobox which is based on a dataview. I did not think it would be that hard. I named my variable mstrPOType. Depending on what my user selects using a radio button sets this variable. This is the code for setting the vairable:

 

If Me.rbtnCOTrans.Checked = True Then

mstrPOType = ">= 01"

Else

mstrPOType = "00"

End If

 

Now this is the SQL statement that uses the variable and is throwing the error (this statement worked fine until I put in a varaible):

 

"SELECT DISTINCTROW [tblReqNmbr(Master)].ReqNoPrefix & [ReqNo] & [ReqSplit] & [ReqChange] AS ReqNum " & _

"FROM [tblReqNmbr(Master)] " & _

"WHERE ((([tblReqNmbr(Master)].Status) <> 'X' AND ([tblReqNmbr(Master)].Status) IS NOT NULL " & _

"AND ([tblReqNmbr(Master)].ReqChange) '" & strPOType & "')) " & _

"ORDER BY [tblReqNmbr(Master)].ReqNoPrefix & [ReqNo] & [ReqSplit] & [ReqChange];"

 

 

This is the error message:

 

Syntax Error (missing operator) in query expression �((([tblReqNmbr(Master)].Status) <> 'X' AND ([tblReqNmbr(Master)].Status) IS NOT NULL AND ([tblReqNmbr(Master)].ReqChange) '= 00'))�.

 

 

Any ideas?

 

Chester

[PlausiblyDamp]

It looks like that in the line

"AND ([tblReqNmbr(Master)].ReqChange) '" & strPOType & "')) " & _

you have an extra ' after ].ReqChange) the line looks like it should be

"AND ([tblReqNmbr(Master)].ReqChange) " & strPOType & "')) " & _

[cpopham]

I thought that was how you passed string variables? I took that ' out and it threw the same exception and then I took them both out and got a datatyoe mismatch error.

 

I checked the sql statement in the access database and it is looking for this statement:

 

="00"

 

or

 

>="01"

 

How can I get the quotes to be in the correct place?

 

Chester

[PlausiblyDamp]

Access is it?

 

IIRC you can include double quotes in your strings by using double double quotes (i.e. "")

 

Also you would need to change the bit of VB code to

If Me.rbtnCOTrans.Checked = True Then
   mstrPOType = ">= ""01"""
Else
   mstrPOType = """00"""
End If

and then remove both single quotes from the SQL statement.

[cpopham]

Thanks for the help. I am going to use a less confusing way because this part resides on a datatier and I want to be able to reuse the code, I have made 2 copies of the SQL. I use a Select Case statement to decide which SQL statement to use.

 

Chester