Looking for date field that is empty

[Aurora]

I am using Access 2003.
I have a query in which I want to search for records that include a date
field that is empty. I tried - isnull - but that did not work. I must be
having a senior moment because for the life of me I can not think of how to
search for records where the date field is empty. I am sure I have done this
before but ....
Can anyone help me?

Thank you - aurora

 

[Jeff Boyce]

Aurora

Open a query in design view. Add the table and the date field (plus any
others you want to see).

In the Selection Criterion under the date field, put:
Is Null

(or just type "Null"... Access will modify it to "Is Null").

Note: if this doesn't return any records, perhaps you don't have "Nulls" in
the field, but "zero-length strings"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane Hookom]

If this is truly a date/time data type, try set the criteria in your query to:
Is Null

 

[Marshall Barton]

I am using Access 2003.
I have a query in which I want to search for records that include a date
field that is empty. I tried - isnull - but that did not work.

The criteria for that is:

Is Null

IsNull is the name of a VBA function that would be used in a
different way that is not needed/appropriate in a query.

 

[Susan May]

I am trying to strip out any email addresses that are null. I've tried is
not null and the query returns all the records with and without email
address. How do I only get the records that have email addresses?

Thanks for your help.

Susan

 

[Duane Hookom]

You may have spaces or zero-length-strings in your email address field. Try
create a new column in your query like:
TrimEmail: Len(Trim([EmailField] & ""))
set the criteria under this column to

 

[Susan May]

Hi Duane:

By God that worked. Thank you so much!!

Susan

You may have spaces or zero-length-strings in your email address field. Try
create a new column in your query like:
TrimEmail: Len(Trim([EmailField] & ""))
set the criteria under this column to

0

--
Duane Hookom
Microsoft Access MVP

I am trying to strip out any email addresses that are null. I've tried is
not null and the query returns all the records with and without email
address. How do I only get the records that have email addresses?

Thanks for your help.

Susan

 

[Duane Hookom]

Don't act so surprised ;-)
--
Duane Hookom
Microsoft Access MVP

Hi Duane:

By God that worked. Thank you so much!!

Susan

You may have spaces or zero-length-strings in your email address field. Try
create a new column in your query like:
TrimEmail: Len(Trim([EmailField] & ""))
set the criteria under this column to

0

--
Duane Hookom
Microsoft Access MVP

I am trying to strip out any email addresses that are null. I've tried is
not null and the query returns all the records with and without email
address. How do I only get the records that have email addresses?

Thanks for your help.

Susan

:

Aurora wrote:

I am using Access 2003.
I have a query in which I want to search for records that include a date
field that is empty. I tried - isnull - but that did not work.


The criteria for that is:

Is Null

IsNull is the name of a VBA function that would be used in a
different way that is not needed/appropriate in a query.

 

[Marshall Barton]

I am trying to strip out any email addresses that are null. I've tried is
not null and the query returns all the records with and without email
address. How do I only get the records that have email addresses?

The problem is probably because you have the field in the
table with AllowZeroLength set to Yes. If so, you have zero
length strings instead of nulls in some/most/all records
where an email address was not entered.

You should think about why you decided to have
AllowZeroLength set to Yes. Also, check the Required
property for Yes and think about each of these properties in
conjunction with the other one. Setting Required to Yes
means that you can not have null in the field. The
AllowZeroLength determind if you can have "" in the field
(this has relatively little utility). Without a good
reason, the standard combination would be to have both of
them set to No.

If you decide that you don't really have a reason for it,
then it might be better to fix the data by using an UPDATE
query:

UPDATE table Set field = Null
WHERE field = ""

Then you can change the field's properties.