Syntax Error

Hello Everyone

I am having a problem with the below mentioned code. It says Datatype mismatch. The bill no is a text field and not a numeric.

Code Code:

Private Sub RetErive_Click()
Dim e As String
Dim sp As String
 
e = InputBox("Please Enter the Bill No", "Retrive")
 
Set ln = New ADODB.Connection
Set ls = New ADODB.Recordset
ln.ConnectionString = "Provider=microsoft.jet.oledb.4.0;" & "Data source =" & App.Path & "\Bills_Service.mdb"
ln.Open
sp = "Select * FROM BILLS WHERE Bill_No = " & e & ""
 
With ls
[COLOR="Red"].Open sp, ln, adOpenKeyset, adLockPessimistic, adCmdText[/COLOR]
End With
 
RID.Text = ls.Fields("RID").Value
Dob.Value = ls.Fields("Date of Bill").Value
Vend.Text = ls.Fields("Name of Vendor").Value
BillNo.Text = ls.Fields("Bill_No").Value
BillAmt.Text = ls.Fields("Bill Amount").Value
Stat.Text = ls.Fields("Del To").Value
Statdt.Value = ls.Fields("Del On").Value
Remark.Text = ls.Fields("Remarks").Value
CMC.Text = ls.Fields("CMC Ref No").Value
CheqNo.Text = ls.Fields("Cheque No").Value
Cheqdt.Value = ls.Fields("Cheque Date").Value
 
ls.Close
ln.Close
 
End Sub

Can anybody tell me what is wrong with code

Thanks

since your field is text, then it needs to be in tick marks, to denote it as text:
sp = "Select * FROM BILLS WHERE Bill_No = '" & e & "'"

-tg

Thank you for the response

Infact i had did that Initially, which gave me the same problem

Some of the fields in that BILLS table contains <spaces> in field names. That will probably gives you some problems. Better try to create it as oneword or join it with underscore sign.

Example:

Date of Bill --> Date_of_Bill
Name of Vendor --> Name_of_Vendor
...etc..

Also, when text fields are used, you have to enclose them in single quotes ( ' ). techgnome already mentioned that.

Eg:

sp = "Select * FROM BILLS WHERE Bill_No = '" & e & "'"

...:wave:

Thank you, It was the spaces in the field names in access that created problem.

It's better not to have spaces in the names but you can do this:

sp = "Select * FROM BILLS WHERE [Bill No] = '" & e & "'"