Null Value Query

[Teri Bridges]

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]

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]

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]

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]

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]

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]

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]

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)

Review_date
Submitted_date
Approved_date
Completed_date

 

[The_Doc_Man]

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]

Better still:

ReviewDate
SubmittedDate
ApprovedDate
CompletedDate

 

[The_Doc_Man]

That too. All a matter of preference once you get rid of spaces and special characters that require the use of brackets.