SQL ? (WHERE clause and DATE format)
I'm using a simple SQL query to return a RecordSet WHERE a field labeled DoctorID equals a string variable AND the ApptDateTime field equals a variable holding a Date. The query works when I omit the second WHERE clause (it only matches the DoctorID), so I know the error is either how I'm setting up my query with 2 WHERE clauses compounded, or how I'm referring to the date. The date passed to the variable matches the date in my database exactly though.
Here's the code in my function:
VB Code:
Private Function DuplicateAppt(DocID As String, DateTime As Date) As Boolean
Dim strSQL As String
strSQL = "SELECT * FROM Appointments WHERE DoctorID ='" _
& DocID & "' AND ApptDateTime = %" & DateTime & "%;"
Call rsAppointments.Open(strSQL, cnSchedule, adOpenStatic, adCmdText)
DuplicateAppt = Not (rsAppointments.EOF)
rsAppointments.Close
End Function
The value I'm passing to the DateTime argument works like this:
VB Code:
dtpDate.Value + TimeValue(dtpTime.Value)
and it looks fine when I debug to check the value that gets passed.
The error message I get when I run it is:
Run-time error '-2147217900 (80040e14)
Syntax error in query expression 'DoctorID = '1005' AND ApptDateTime = %11/27/2002 9:00:00 AM%'.
Am I passing the date wrong? Am I doing my multiple WHERE clauses wrong? I've played around with both and still get similar error message every time.
Thanks!
