Using date in SQL query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am writing a query to return appointment information where the date of the
appointment is greater than or equal to today, but it doesnt work. I am
returning all records for a given client. I have tried putting the date
function call within hashes (#) and without hashes. The code is below (with
hashes), any ideas?

strSqlStmt = "SELECT Appointment.Appointment_ID, Person.Surname,
Person.FirstName, " & _
"WorkType.Title, Appointment.Appointment_Date,
Appointment.Appointment_Start_Time " & _
"FROM Client_Attendee, Appointment, Employee_Attendee,
JobRole, Person, WorkType " & _
"WHERE Client_Attendee.fk1_Appointment_ID =
Appointment.Appointment_ID " & _
"AND Appointment.Appointment_ID =
Employee_Attendee.fk2_Appointment_ID " & _
"AND Employee_Attendee.fk1_JobRoleID = JobRole.JobRoleID " & _
"AND JobRole.fk2_PersonID = Person.PersonID " & _
"AND Appointment.fk1_TypeID = WorkType.TypeID " & _
"AND Client_Attendee.fk2_PersonID = " & Chr(39) &
gstrClientID & Chr(39) & _
" AND Appointment.Appointment_Date >= " & Chr(35) & Date &
Chr(35) & _
" AND Appointment.Status = 'B'"
 
Assuming this isn't a pass-through query, you definitely need the # symbols.

What's your Short Date format set to (or, more to the point, what does Date
return)? Regardless of what your Short Date format has been set to, you must
use mm/dd/yyyy format in SQL statements. (Okay, that's not strictly true:
you can use any unambiguous format, such as dd mmm yyyy or yyyy-mm-dd. The
point is, it's not going to work if Date is in dd/mm/yyyy format)

To kill two birds with one stone, I use:

" AND Appointment.Appointment_Date >= " & _
Format(Date, "\#mm\/dd\/yyyy\#") & _
 
Back
Top