Form - Date Field - Query question

[k3ll1n]

Afternoon all,

I'm not positive if this is a form question or a query question.
I was assisted with a query the other day (thanks again), and everything seems to work well from my perspective until I'm on the form I've created.

The query should listing in the combobox the appointment times that have yet to be booked for that day. Which it does. Though when I change the date field on the form to tomorrows date, the 2:00 PM appointment isn't listed in the dropdown because it's been booked the previous day.

My AppointmentDate field is a data type of Date/Time with a default value of =Date() and the Show Date Picker: For Dates. Changing the date here causes no effect on the cboAvailability dropdown.

I've tried adding a requery on change and after update to the AppointmentDate form field but I'm still new to this. Perhaps a relationship issue?

Here's the Appointment time query I'm working with in case that helps a bit.

SELECT DISTINCT tblAppointmentHoursDaily.Hours
FROM tblAppointmentHoursDaily LEFT JOIN tblAppointments ON tblAppointmentHoursDaily.[Hours] = tblAppointments.AppointmentTime
WHERE (((tblAppointments.AppointmentDate)<>Date() Or (tblAppointments.AppointmentDate) Is Null) AND ((tblAppointmentHoursDaily.Show)=True));

Cheers,
k3ll1n

 

[sneuberg]

If you expecting something to happen when you select a date with the date picker I believe you are out of luck. It apparently doesn't fire any events. I think you have to tab out of the field or move focus from the field before anything happens.

 

[k3ll1n]

If I manually set the date and tab off, it refreshes the form and should make the query pull new results. Is something wrong with the query in respect to how it searches for only for the date listed in AppointmentDate for that exact time?

2:00 PM - 2016.06.05 - Taken
2:00 PM - 2016.06.06 - Not listed because it's taken the previous day. Need it showing.

Is there something I need to tweak with the query in that case?

 

[sneuberg]

Your query has the Date function in as shown in red

SELECT DISTINCT tblAppointmentHoursDaily.Hours
FROM tblAppointmentHoursDaily LEFT JOIN tblAppointments ON tblAppointmentHoursDaily.[Hours] = tblAppointments.AppointmentTime
WHERE (((tblAppointments.AppointmentDate)<>[COLOR="Red"] Date()[/COLOR] Or (tblAppointments.AppointmentDate) Is Null) AND ((tblAppointmentHoursDaily.Show)=True));

You mention a date field on the form. Maybe you want a reference to that in the query instead something like:

SELECT DISTINCT tblAppointmentHoursDaily.Hours
FROM tblAppointmentHoursDaily LEFT JOIN tblAppointments ON tblAppointmentHoursDaily.[Hours] = tblAppointments.AppointmentTime
WHERE (((tblAppointments.AppointmentDate) <> [COLOR="Red"][Forms]![the name of the form]![the name of the date field][/COLOR] Or (tblAppointments.AppointmentDate) Is Null) AND ((tblAppointmentHoursDaily.Show)=True));

 

[k3ll1n]

Appreciate your help, though still not working as needed unfortunately. I believe this may be more of a query question now. I'll move the discussion over there.