Null Values in Date Fields Are Driving Me Insane

[Gary Childress]

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:

Like [FORMS]![SWITCHBOARD].[FilterByAppointmentDates] & "*"

in the query design view in order to filter by date.

I tried using

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.

[Welshgasman]

Use the NZ() function to replace Null with whatever you like.

However as the date field has no value, it will never be valid for your search for a certain date or date range?

[Gary Childress]

Thanks for the quick reply. Would you be able to include an example of how the NZ formula could be used as a work around? Thanks!

[orange]

Nz function


Sample usage: To give a future date #3/28/2022# ===28th of March

SELECT
EmployeeDaysLate.EmployeeId
, Nz([DateLate],#3/28/2022#)
FROM EmployeeDaysLate;

[June7]

Use a specific alias field name or Access will assign generic Expr1.

I set up date textbox with InputMask to facility typing in date instead of relying on date picker - don't like the bugger.

I try to build data entry forms to minimize mousing and allow all typed input.

[Welshgasman]

I hate input masks, as you generally do not start at the beginning of a control if you click into it.

Not just Access either, other programs have the same proble I have noticed.

[June7]

Yes, so it is a balancing act between pet peeves and what is best for situation. Data entry that needs fast and efficient typed input is best without throwing in the oddball moused input. Start with first control on form and tab through and type. Never lift hands from keyboard. If user makes mistake and has to click back (unless they learn about Shift + Tab), that's on them. I even set up buttons with Alt+ shortcut keys so again, don't have to leave keyboard.

[CJ_London]

think you don't know what a date field actually is - it is a number. Today is 44634 - the number of days since 31/12/1899. Ao

so something like
Like [FORMS]![SWITCHBOARD].[FilterByAppointmentDates] & "*"
is not going to work as you expect.

now think about the logic of what this statement is actually saying

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.

leaving aside the issue mentioned above, your criteria is saying

'return every record where mydate field is not null'

with only a description of your issue and no example data and the outcome required, I think what you need as a criteria is

=[FORMS]![SWITCHBOARD].[FilterByAppointmentDates] OR [FORMS]![SWITCHBOARD].[FilterByAppointmentDates] Is Null