Syntax Error

[joevb84]

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.

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

[techgnome]

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

[joevb84]

Thank you for the response

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

[akhileshbc]

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 & "'"

...

[joevb84]

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

[MartinLiss]

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

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