Retrieving Excel data

[evankean]

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 

Try





cnt.Open()

adp.Fill(ds)

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

Catch Ex As Exception 

MessageBox.Show(Ex.Message)

Finally



cnt.Close()

cnt = Nothing

adp.Dispose()

ds.Dispose()

End Try

End Sub

[PerplexedB]

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),

Marc

[crapulency]

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'