SQL statement error

[shery1995]

Hi All

I am trying to execute the following SELECT statement on my form but getting error:

sql = "SELECT DoctorsID,AppointDate,AppointTime FROM qrySubformAppoints WHERE DoctorsID=" & Me.ID & _
"AND AppointDate=#" & Me.txtAppointDate & "#;"

I would be grateful if can help.

Many thanks

 

[cheekybuddha]

You need a space before AND

 

[cheekybuddha]

sql = "SELECT DoctorsID,AppointDate,AppointTime FROM qrySubformAppoints WHERE DoctorsID=" & Me.ID & _
" AND AppointDate=#" & Me.txtAppointDate & "#;"

 

[cheekybuddha]

After assigning your SQL string use Debug.Print to view the output in the Immediate Window (Ctrl+G)

sql = "..."
Debug.Print sql

 

[shery1995]

sql = "SELECT DoctorsID,AppointDate,AppointTime FROM qrySubformAppoints WHERE DoctorsID=" & Me.ID & _
" AND AppointDate=#" & Me.txtAppointDate & "#;"

Thank you for your reply. Still getting error message (Run-time error '3061': Too few parameters. Expected 1.

 

[cheekybuddha]

Then you have mis-spelt one of the field names or Me.txtAppointDate does not contain a valid date

 

[cheekybuddha]

See my post #4 and copy and paste the output from the Immediate Window here

 

[shery1995]

See my post #4 and copy and paste the output from the Immediate Window here

SELECT DoctorsID,AppointDate,AppointTime FROM qrySubformAppoints WHERE DoctorsID=1 AND AppointDate=#14/09/2022#

many thanks in advance

 

[cheekybuddha]

What is the SQL of qrySubformAppoints?

 

[shery1995]

What is the SQL of qrySubformAppoints?

SELECT tblApp.DoctorID, tblApp.ClientID, tblApp.AppointTime, tblApp.AppointDate, tblClients.ClientName
FROM tblSchedule INNER JOIN (tblClients INNER JOIN tblApp ON tblClients.ClientID = tblApp.ClientID) ON tblSchedule.DoctorsID = tblApp.DoctorID;

 

[isladogs]

In SQL dates must be jn mm/dd/yyyy or yyyy-mm-dd format.
9/14/2022 NOT 14/09/2022

 

[cheekybuddha]

9/14/2022 NOT 14/09/2022

Whilst generally true, it shouldn't make a difference in this specific instance since the date, although in UK format, is not ambiguous.

The problem is that you are selecting DoctorsID instead of DoctorID, and again the WHERE clause.

 

[cheekybuddha]

Try it like:

sql = "SELECT DoctorID, AppointDate, AppointTime " & _
      "FROM qrySubformAppoints " & _
      "WHERE DoctorID = " & Me.ID & _
      "  AND AppointDate = " & Format(Me.txtAppointDate, "\#yyyy\-mm\-dd\#") & ";"

 

[shery1995]

Try it like:

sql = "SELECT DoctorID, AppointDate, AppointTime " & _
      "FROM qrySubformAppoints " & _
      "WHERE DoctorID = " & Me.ID & _
      "  AND AppointDate = " & Format(Me.txtAppointDate, "\#yyyy\-mm\-dd\#") & ";"

Thank you very much for your help and advice. Fall sick and could not check the message.