Hello,
I am trying to write an sql statement but an error keeps coming up.
I need to retrieve data from more than one table so I a using the INNer JOIN statement, but still no luck.
The error is in the from part according to the error message.
Any help welcome
The code part is
SQLStr = SQLStr & "DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge "
SQLStr = SQLStr & "FROM (Consultation INNER JOIN Surgery ON Consultation.SurgeryID = Surgery.SurgeryID) "
SQLStr = SQLStr & "INNER JOIN Patient ON Consultation.PatientID = Patient.PatId "
SQLStr = SQLStr & "FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId) "
SQLStr = SQLStr & "(INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID) AND "
SQLStr = SQLStr & " (Clinicians ON Consultation.ClinicianID=Clinicians.ClinicianID) "
SQLStr = SQLStr & " WHERE ((Consultation.BatchNum) = " & intCurrentBatch & ") "
SQLStr = SQLStr & "ORDER BY Consultation.ConsultId;"
and Part of the debug print is
SELECT Consultation.ConsultId,Consultation.ConsultDate , Consultation.HowLongEnterData,Clincians.name, Clinicians.RPSGB, Surgery.SurgeryName, Patient.PostCode1, MID$(Patient.PostCode2,1,1) AS PostCode2, Patient.Ethnicity_ID, Patient.Ethnicity_Other_text, DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId) (INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID) AND (Clinicians ON Consultation.ClinicianID=Clinicians.Clinician
ID) WHERE ((Consultation.BatchNum) = 2) ORDER BY Consultation.ConsultId;
I am trying to write an sql statement but an error keeps coming up.
I need to retrieve data from more than one table so I a using the INNer JOIN statement, but still no luck.
The error is in the from part according to the error message.
Any help welcome
The code part is
SQLStr = SQLStr & "DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge "
SQLStr = SQLStr & "FROM (Consultation INNER JOIN Surgery ON Consultation.SurgeryID = Surgery.SurgeryID) "
SQLStr = SQLStr & "INNER JOIN Patient ON Consultation.PatientID = Patient.PatId "
SQLStr = SQLStr & "FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId) "
SQLStr = SQLStr & "(INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID) AND "
SQLStr = SQLStr & " (Clinicians ON Consultation.ClinicianID=Clinicians.ClinicianID) "
SQLStr = SQLStr & " WHERE ((Consultation.BatchNum) = " & intCurrentBatch & ") "
SQLStr = SQLStr & "ORDER BY Consultation.ConsultId;"
and Part of the debug print is
SELECT Consultation.ConsultId,Consultation.ConsultDate , Consultation.HowLongEnterData,Clincians.name, Clinicians.RPSGB, Surgery.SurgeryName, Patient.PostCode1, MID$(Patient.PostCode2,1,1) AS PostCode2, Patient.Ethnicity_ID, Patient.Ethnicity_Other_text, DateDiff('yyyy', Patient.dob,Consultation.ConsultDate) AS PatientAge FROM (Consultation INNER JOIN Patient ON Consultation.PatientID = Patient.PatId) (INNER JOIN Surgery ON Patient.SurgeryID = Surgery.SurgeryID) AND (Clinicians ON Consultation.ClinicianID=Clinicians.Clinician
ID) WHERE ((Consultation.BatchNum) = 2) ORDER BY Consultation.ConsultId;