SQL Query syntax error? 1

[DeeMark]

Would be grateful if I could be advised on where I have syntax issue(s) with my strSQL string variable below.

Am using rs.Open strSQL ...etc but this is appears to be wrong.
Looking at the various posts on this subject I have noted the 'delimeters' etc but dont seem to have it clear in my head.
To avoid the syntax issues I have tried creating the sql string in the Query Design interface, however when I do this I get the [INSERT],[DELETE],[SELECT],[UPDATE] missing error eg

rs.Open "qryXXXXXX"

Would be grateful for guidance again. Tks in anticipation.

Deemark

strSQL = "SELECT tblappointments.patientID, tblappointments.paindiagimagepath, tblappointments.appointmentID "
strSQL = strSQL & "FROM tblappointments "
strSQL = strSQL & "WHERE " & (([tblappointments.appointmentID]) = Me.appointmentID) & ""
strSQL = strSQL & " AND " & (([tblappointments.patientID]) = [Forms]![frmpatientsappts]![patientID]) & ""

 

[Remou]

When using an SQL string in VBA, you must use values rather than references to a form, unless you are using the string to build a query. So:

strSQL = "SELECT a.patientID, a.paindiagimagepath, a.appointmentID "
strSQL = strSQL & "FROM tblappointments a "
strSQL = strSQL & "WHERE a.appointmentID = " & Me.appointmentID 
strSQL = strSQL & " AND a.patientID = " & Forms![frmpatientsappts]![patientID]

Note the alias : FROM tblappointments a
This is not essential, but it does make life easier. I am guessing that AppointmentID and PatientID are numeric, so you do not need delimiters.

http://lessthandot.com

 

[DeeMark]

Remou

Many thanks, your prompt response in correcting my sql syntax has been appreciated. All noted - am back on track with my project.
Deemark