Run time error 3079 "The specified field could rever to more than one table listed in the FROM clause of your SQL statement

Using VBA and writing my own SQL statements, I have three tables to extract data from.  

Key values are:
  tblPatientInformation.PatientID
  tblPulmonary.EvaluationID
  tblFunctionalImpairmentsMonitor.EvaluationID

I have done the appropriate relationships using the Relationships tool

The code.
'******************************************************************************
 'Construct SQL statement
Dim strSQL
strSQL = " SELECT PD.*, PI.PatientID, PI.LastName, FIM.O2_Sat_RA" + _
             " FROM tblPulmonaryDisease PD, tblPatientInformation PI, tblFunctionalImpairmentsMonitor FIM" + _
             " WHERE (PD.PatientID = PI.PatientID) And (PD.EvaluationID = FIM.EvaluationID) And " + _
              (PD.PatientID = " 
strSQL = strSQL + "'" + Me.textboxPatientID.Value + "'" + ") "
strSQL = strSQL + " ORDER BY PD.EvaluationID;"
'******************************************************************************

I get back the 3079 run time error.  Please help.

Thanks,
Jim Botts
jsbotts713@leaco.net
 
jbottsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Data-ManConnect With a Mentor COOCommented:
Try this if PatientID is a number

Dim strSQL
strSQL = "SELECT PD.*, PI.PatientID, PI.LastName, FIM.O2_Sat_RA" & _
             " FROM tblPulmonaryDisease PD, tblPatientInformation PI, tblFunctionalImpairmentsMonitor FIM" & _
             " WHERE (PD.PatientID = PI.PatientID) And (PD.EvaluationID = FIM.EvaluationID) And " & _
             " (PD.PatientID = " & Me.textboxPatientID.Value & ") ORDER BY PD.EvaluationID;"


if PatientID is a string do this

strSQL = "SELECT PD.*, PI.PatientID, PI.LastName, FIM.O2_Sat_RA" & _
             " FROM tblPulmonaryDisease PD, tblPatientInformation PI, tblFunctionalImpairmentsMonitor FIM" & _
             " WHERE (PD.PatientID = PI.PatientID) And (PD.EvaluationID = FIM.EvaluationID) And " & _
             " (PD.PatientID = '" & Me.textboxPatientID.Value & "') ORDER BY PD.EvaluationID;"

Mike
0
All Courses

From novice to tech pro — start learning today.