I have 3 tables, tblAppointment, tblCustomer, tblDoctor.
tblCustomer has a one-to-many relationship with tblAppointment
tblDoctor has a one-to-many relationship with tblAppointment
The following works and gets the appointment and customer info
I tried to include the Doctor info with something like
I get "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"
I tried various permutations without success. Any ideas very welcome.
tblCustomer has a one-to-many relationship with tblAppointment
tblDoctor has a one-to-many relationship with tblAppointment
The following works and gets the appointment and customer info
Code:
rst.Open "SELECT tblAppointment.lngPatientID AS lngPatientID, tblCustomer.lngCustomerID AS lngCustomerID, " _
& " tblAppointment.lngAppointDoctorID AS lngAppointDoctorID, tblAppointment.dtmAppointDate AS dtmAppointDate, " _
& " tblAppointment.dtmAppointTime AS dtmAppointTime, tblAppointment.txtAppointDesc AS txtAppointDesc, " _
& " tblCustomer.txtSurname + ' ' + tblCustomer.txtName + ' ' + tblCustomer.txtTelephone1 AS nvcqCustomerName " _
& " FROM tblAppointment " _
& " LEFT OUTER JOIN tblCustomer ON tblAppointment.lngPatientID = tblCustomer.lngCustomerID " _
& " WHERE (tblAppointment.lngAppointDoctorID = 1) AND (dtmAppointDate = #07/27/2015#) ORDER BY dtmAppointTime ASC ;", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
Code:
rst.Open "SELECT tblAppointment.lngPatientID AS lngPatientID, tblCustomer.lngCustomerID AS lngCustomerID, " _
& " tblAppointment.lngAppointDoctorID AS lngAppointDoctorID, tblAppointment.dtmAppointDate AS dtmAppointDate, " _
& " tblAppointment.dtmAppointTime AS dtmAppointTime, tblAppointment.txtAppointDesc AS txtAppointDesc, " _
& " tblDoctor.lngDoctorID AS lngDoctorID, tblDoctor.txtDoctorSurname AS txtDoctorSurname " _
& " tblCustomer.txtSurname + ' ' + tblCustomer.txtName + ' ' + tblCustomer.txtTelephone1 AS nvcqCustomerName " _
& " FROM tblAppointment " _
& " LEFT OUTER JOIN tblCustomer ON tblAppointment.lngPatientID = tblCustomer.lngCustomerID " _
& " LEFT OUTER JOIN tblDoctor ON tblDoctor.lngDoctorID = tblAppointment.lngAppointDoctorID " _
& " WHERE (tblAppointment.lngAppointDoctorID = 1) AND (dtmAppointDate = #07/27/2015#) ORDER BY dtmAppointTime ASC ;", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
I tried various permutations without success. Any ideas very welcome.