I have a form called "Switchboard". On the Form called "Switchboard" is a combo box field called "FilterByAppointmentDates" that draws from a query called "AppointmentDates". The query "AppointmentDates" draws dates from a field in the table "MainDataTable". Using the "Group by" function I have excluded duplicate dates so that the query "AppointmentDates" only shows one instance of each date where there are duplicates.
When I select a date on the form "Switchboard" I click the button "GetData" and it pulls all the records from the field "AppointmentDate" in the table "MainDataTable" with that date and brings them up in a query called "MainDataTableQuery". The query "MainDataTableQuery" is the record source for my form called "MainDataForm". Unfortunately I can't seem to get it to work when I have date fields with null values. It seems to confuse Access to no end to have null values. I have the same problem with other fields too so with the other fields I eliminate null values by creating a default value in the field. However, I don't want a default value in a date field. I want the field empty or else to have characters that are clear and easy to spot to indicate that the field has not been filled out. I tried putting the date "1/1/1999" as a default value. It's not an ideal state of affairs, however, when the default value is "1/1/1999" and I use the date picker to enter the real date, I have to scroll from 1999 to 2022 to enter the correct date.
Is there a way to get this to work sensibly?
I've been using the expression:
Code:
Like [FORMS]![SWITCHBOARD].[FilterByAppointmentDates] & "*"
in the query design view in order to filter by date.
I tried using
Code:
Like [FORMS]![SWITCHBOARD].[FilterByAppointmentDates] & "*" And Is Not Null
, however, that seems to cause the query to return every date in that field in the table that is not null, defeating the purpose of having a date query.
I'm stubbed.