Good morning all;
This one is blowing my mind and I could use a little expertise. I'm retrieving data from an Excel sheet, using a simple SQL query using a textbox as input to my variable. It works great when I enter an integer to search for (such as 213 for example) but when I put in a mixture of numbers and letters (48A for example) I get the following error: Syntax error (missing operator) in query expression '[P&L]=48A'. Can anyone tell me the fix for this? I know it has something somewhere to do with integers. Please help. My code is as follows...

Private Sub MPLS3() 

Dim BrNr As String

BrNr = TextBox1.Text

Dim stSQL As String = "SELECT [MPLS Date] FROM [Master Data$] WHERE [P&L]=" & BrNr & ";"

Dim stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _ 

& "Data Source=c:\branchinfo\BranchData.xls;" _ 

& "Extended Properties=""Excel 8.0;HDR=YES"";"

Dim cnt As New OleDbConnection(stCon) 

Dim cmd As New OleDbCommand(stSQL, cnt) 

Dim adp As New OleDbDataAdapter(cmd) 

Dim ds As New DataSet 




Me.Label1.Text = ds.Tables(0).Rows(0).Item(0) 

Catch Ex As Exception 




cnt = Nothing



End Try

End Sub

Not sure I understand what you're doing but you might want to change your sql statement into

Dim stSQL As String = "SELECT [MPLS Date] FROM [Master Data$] WHERE [P&L]='" & BrNr & "';"

HTH (although I have my doubts),


Hi evankean Think PerplexedB is right. Your [P&L] field is probably some kind of a numeric field (Integer prob) If you want [P&L]=48A to work, you must change the field in the Access database to a text field. Then Perplexed' suggestion will work i.e. including the single quotes like this: [P&L]='48A'

This article has been dead for over six months. Start a new discussion instead.