Passing number from Access text box to SQL query using vba

[DrDave3144]

I am building an application with a number of queries that identify which piece of pipe (or pieces) meet various criteria. In several cases, the query involves matching "Pipeline Number" (actually a string type)from a dropdown list of a form, and finding which piece would contain a location ("stationing value") supplied from user input in a text box on the same form.

The value from the text box (when introduced to the query as a reference in the sql statement) invokes a text comparison-using numbers...no good). Access SQL doesn't seem to recognize CONVERT or CAST and Access doesn't seem to recognize variables (or use the numeric value) in the vba code when I try to pass the SQL query as a text string.

I was hoping to get ideas on how to get this to work.

Thanks in advance!


Public Sub vbqrySelectByPipeNStat()


Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim cmd2 As ADODB.Command
Dim rst2 As ADODB.Recordset
Dim strSQL As String
Dim strPipeline As String
Dim dblCount As Double
Dim dblTest As Double

strPipeline = Forms!SelectStickByPipelineAndStationing.PipesList.Value

'SELECT GIS_Pipes_04_09 is the table the query runs against
'FROM_STATI and TO_STATION are fields in the table that hold numeric address
'at the begining and end of the pipe pieces (type is double)
'StatNo1 is the TextBox on the form


strSQL = "SELECT GIS_Pipes_04_09.* " _
& "FROM GIS_Pipes_04_09 " _
& "WHERE GIS_Pipes_04_09.[PIPELINE]= '" & strPipeline & "' " _
& "AND Cdbl(Forms!SelectStickByPipelineAndStationing.StatNo1.Value) BETWEEN ' " _
& "GIS_Pipes_04_09.[FROM_STATI] " _
& "AND GIS_Pipes_04_09.[TO_STATION] " _
& "ORDER BY ORDER_SEQ;"


'Passes StatNo1 Value As Text....
' strSQL = "SELECT GIS_Pipes_04_09.* "
'& "FROM GIS_Pipes_04_09 WHERE GIS_Pipes_04_09.[PIPELINE]= '" & strPipeline & "' "
'& "AND GIS_Pipes_04_09.[FROM_STATI] <= Forms!SelectStickByPipelineAndStationing.StatNo1.Value "
'& "AND GIS_Pipes_04_09.[TO_STATION] >= Forms!SelectStickByPipelineAndStationing.StatNo1.Value "
'& "ORDER BY ORDER_SEQ;"


'MsgBox strSQL


cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qryStickPipeAndStat").Command
cmd.CommandText = strSQL
Set cat.Views("qryStickPipeAndStat").Command = cmd
Set cat = Nothing

DoCmd.Echo False
DoCmd.OpenQuery "qryStickPipeAndStat"
DoCmd.Close acQuery, "qryStickPipeAndStat"
dblCount = DCount("[ORDER_SEQ]", "qryStickPipeAndStat")
DoCmd.Echo True
Forms!SelectStickByPipelineAndStationing.RecsSelected = dblCount

Set cat = Nothing
Set cmd = Nothing

End Sub

 

[PHV]

Don't use single quotes with the numeric values:

   strSQL = "SELECT * " _
    & "FROM GIS_Pipes_04_09 " _
    & "WHERE PIPELINE='" & strPipeline & "' " _
    & "AND " & Cdbl(Forms!SelectStickByPipelineAndStationing!StatNo1) _
    & " BETWEEN FROM_STATI And TO_STATION " _
    & "ORDER BY ORDER_SEQ;"

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

 

[DrDave3144]

That did it! THANKS!

Dave