Null Value Query (1 Viewer)

Teri Bridges

Member
Local time
Today, 08:22
Joined
Feb 21, 2022
Messages
186
I am querying my table for four field values (date values) I get the results but I get results when the field is empty meaning no date has been entered. I would like to exclude the results if the date is not entered.
I have
Review (date)
Submitted (date)
Approved (date)
Completed (date)
I do not know how to write the criteria for example to say IF the review date is empty do not show
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:22
Joined
Feb 19, 2002
Messages
42,970
Is it just the one date? If so,
Where ReviewDT Is Not Null

If you are using the QBE, open the query builder. Select the table. Select the columns and in the where line under the Review column, put

Is Not Null

I hope your names don' treally include spaces and special characters. If they do, you might consider changing them before moving on.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:22
Joined
Feb 28, 2001
Messages
26,998
The question will have to include exactly how you defined the fields. It is possible to set a field to Null but the default might instead be 0 for a given numeric type. Further, if the field is normally loaded from an external source, the odds favor that ITS default for that field won't be null, but might be a zero-length-string (ZLS, or empty string). If numeric, might not be a ZLS but might be a 0.

There are various tests, but if the fields are truly of type DATE then IF NZ(date-field, 0) = 0 would work as a test for each individual field since date fields can't return a ZLS directly.
 

Teri Bridges

Member
Local time
Today, 08:22
Joined
Feb 21, 2022
Messages
186
Is it just the one date? If so,
Where ReviewDT Is Not Null

If you are using the QBE, open the query builder. Select the table. Select the columns and in the where line under the Review column, put

Is Not Null

I hope your names don' treally include spaces and special characters. If they do, you might consider changing them before moving on.
Thank you
 

Teri Bridges

Member
Local time
Today, 08:22
Joined
Feb 21, 2022
Messages
186
The question will have to include exactly how you defined the fields. It is possible to set a field to Null but the default might instead be 0 for a given numeric type. Further, if the field is normally loaded from an external source, the odds favor that ITS default for that field won't be null, but might be a zero-length-string (ZLS, or empty string). If numeric, might not be a ZLS but might be a 0.

There are various tests, but if the fields are truly of type DATE then IF NZ(date-field, 0) = 0 would work as a test for each individual field since date fields can't return a ZLS directly.
Thank you
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:22
Joined
Feb 19, 2002
Messages
42,970
Once you have a value stored as a datetime data type, it is numeric. Therefore, it can never contain a ZLS. It is either null or a date.
 

Teri Bridges

Member
Local time
Today, 08:22
Joined
Feb 21, 2022
Messages
186
Thank you
Thank you I did change my names as suggested. I am just learning and typing with a new mind set catches me. My tables are small for now whine I try and apply what I learn on line. So again thank you for your help.
 

SHANEMAC51

Active member
Local time
Today, 16:22
Joined
Jan 28, 2022
Messages
310
I have
Review (date)
Submitted (date)
Approved (date)
Completed (date)
I prefer more readable field names (with underscores and without other delimiters such as spaces, dots, commas, brackets)
Code:
Review_date
Submitted_date
Approved_date
Completed_date
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:22
Joined
Feb 28, 2001
Messages
26,998
ShaneMac51's advice is good. The name Review (date) will ALWAYS require bracketing with [ ] wherever it appears. The alternative, Review_date, will not. Further, having parenthesis in the field name will confuse Access if EVER this variable name appears outside of bracketing, since it might look like a function OR it might be interpreted as an expression that is missing an operator.
 

isladogs

MVP / VIP
Local time
Today, 13:22
Joined
Jan 14, 2017
Messages
18,186
Better still:
Code:
ReviewDate
SubmittedDate
ApprovedDate
CompletedDate
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:22
Joined
Feb 28, 2001
Messages
26,998
That too. All a matter of preference once you get rid of spaces and special characters that require the use of brackets.
 

Users who are viewing this thread

Top Bottom