vb6 and ADO syntax error

[sl101]

Hello. For some reason I get a syntax error for the bold line below. Any one know why?

Private Sub cmdClockOut_Click()
Dim cn2 As New ADODB.Connection
Dim rs2 As New ADODB.Recordset
Dim EN As String
EN = lblEmployeeNumber.Caption

'Connect to database
cn2.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn2.ConnectionString = "Data Source='\Thorpe Park ICT6\TP.mdb';"
cn2.Open

rs2.Open "Select * from tbl_TimeSheet WHERE Employee_Number = '" & lblEmployeeNumber.Caption & "' AND Date = '" & Date & "'", cn2, adOpenDynamic

End Sub

 

[RoyVidar]

Perhaps is we get the errormessage?

I'm guessing it might have to do with using reserved words as name of field (Date). If that field is also of datatype Date/Time, then you'll get an error when using single quote as delimiter (') it needs #, and you probably also need to format the data to an unambiguous format. Then, if the equipment number is actually a number, you need to remove the single quotes there, too. I'm not sure, but I think your connection string might be wrong, too, check out http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet, the one with network share.

I think we'd need to know the datatype of the fields, and also what you're passing. Try doing a

Debug.Print "Select * from tbl_TimeSheet WHERE Employee_Number = '" & lblEmployeeNumber.Caption & "' AND Date = '" & Date & "'"

in stead of trying to open the recordset.

And also, I'd recommend being more explicit in the declarations/instantiatins.

Dim cn2 As ADODB.Connection
Dim rs2 As ADODB.Recordset

set cn2 = New ADODB.Connection
set rs2 = New ADODB.Recordset