Need some help revising some code...

[seashton]

I am not very good at revising code and need suggestions how to get some code to do the things I want. Below I have code that gets the appointments for the active date BUT I also need to get those appointments by ApptLocation field as well as the ApptDate from tblAppointments. Can some make some suggestions?

strSQL = "SELECT tblAppointments.*, tblHour.HourID " & _


"FROM tblAppointments INNER JOIN tblHour " & _
"ON tblAppointments.ApptStartTime = tblHour.Hours " & _
"WHERE tblAppointments.ApptDate = #" & dtePubMyDate & "# " & _
"ORDER BY ApptStartTime;"

[weekend00]

since

strSQL = "SELECT tblAppointments.*, tblHour.HourID " & _
"FROM tblAppointments INNER JOIN tblHour " & _
"ON tblAppointments.ApptStartTime = tblHour.Hours " & _
"WHERE tblAppointments.ApptDate = #" & dtePubMyDate & "# " & _
"ORDER BY ApptStartTime;"

is to get appointment on that date, so dtePubMyDate looks like a textbox in the form. if you want to get appointment by location, assume there is a control apptLocation in the form, then the code will be:

strSQL = "SELECT tblAppointments.*, tblHour.HourID " & _
"FROM tblAppointments INNER JOIN tblHour " & _
"ON tblAppointments.ApptStartTime = tblHour.Hours " & _
"WHERE tblAppointments.Apptlocation = '" & apptlocation & "' ORDER BY ApptStartTime;"

if you want to get appointment by both date and location:

strSQL = "SELECT tblAppointments.*, tblHour.HourID " & _
"FROM tblAppointments INNER JOIN tblHour " & _
"ON tblAppointments.ApptStartTime = tblHour.Hours " & _
"WHERE tblAppointments.ApptDate = #" & dtePubMyDate & "# and tblAppointments.Apptlocation = '" & apptlocation & "' ORDER BY ApptStartTime;"

[seashton]

Thanks, your suggestion has worked but I do not think I have my logic is quite right for this form. The ApptLocation control is a combo box based on tblAppointments records and it needs to reflect the records associated with the date shown in Date textbox on the form and this is not working.

I do not think the ApptLocation control is the way to do this. I would like to have this form open so the ApptLocation control populates based on the date shown in the date textbox or do I need to to make the ApptLocation a textbox also and have code populate it the same way the Date textbox does. Below is how the Date textbox is updated on form.

'Update the active date in the form header
Me.lblDate.Caption = Format(dtePubMyDate, "Long Date")

There also could be many ApptLocation records for each date, is there a way to place controls on form to pick a diiferent ApptLocation like I have the ability to pick different dates with the Date textbox on the form (see attached sceenshot of form for reference)
[IMG]file:///C:/Users/seashton/AppData/Local/Temp/moz-screenshot-4.png[/IMG][IMG]file:///C:/Users/seashton/AppData/Local/Temp/moz-screenshot-5.png[/IMG]