Is Null vs. ""

[Guest]

I have a query/report where I'm grouping by a text field (Clinic Name) and
for the records with no data in the Clinic Name field, I use an IIF function
to insert "No Clinic Name Specified" in the Group Header (also in the details
section).

My formula is: =IIf(IsNull([Group-Clinic Name]),"(No Group-Clinic
Specified)",[Group-Clinic Name])

I noticed that there are now two "groups" of records with empty Clinic Name
fields. One group displays "(No Group-Clinic Name Specified)" , while the
next group has an empty header. I have gone through and deleted the contents
of all of the supposedly empty Clinic Name fields, first with the Del key on
the keyboard (navigating from field cell to field cell with the up/down
arrows, which should select/highlight all data in the field), then the same
process with the Backspace key, then finally one more trip through selecting
the cell with the "white cross" mouse pointer, which selects/highlights the
entire cell, and using the "Delete" command in the Edit menu. None of these
approaches seems to have completely cleared the field, because there are
still two groups.

I notice that if I use [Clinic Name]<>"" instead of IsNull([Clinic Name])
I get the "2nd" group of blank clinic names. This makes me think that there
is still some shred or artifact in the field that Access is picking up that
makes it "not null".

Any ideas why some of the supposedly empty fields are being treated as if
they contain something?

Thanks in advance,

Steve

 

[Jeff Boyce]

Steve

Have you looked into using something like:

IIF(Nz([Group-ClinicName],"")="","(No Group-Clinic...

This would allow you to test for either null or "".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

I have a query/report where I'm grouping by a text field (Clinic Name) and
for the records with no data in the Clinic Name field, I use an IIF function
to insert "No Clinic Name Specified" in the Group Header (also in the details
section).

My formula is: =IIf(IsNull([Group-Clinic Name]),"(No Group-Clinic
Specified)",[Group-Clinic Name])

I noticed that there are now two "groups" of records with empty Clinic Name
fields. One group displays "(No Group-Clinic Name Specified)" , while the
next group has an empty header. I have gone through and deleted the contents
of all of the supposedly empty Clinic Name fields, first with the Del key on
the keyboard (navigating from field cell to field cell with the up/down
arrows, which should select/highlight all data in the field), then the same
process with the Backspace key, then finally one more trip through selecting
the cell with the "white cross" mouse pointer, which selects/highlights the
entire cell, and using the "Delete" command in the Edit menu. None of these
approaches seems to have completely cleared the field, because there are
still two groups.

I notice that if I use [Clinic Name]<>"" instead of IsNull([Clinic Name])
I get the "2nd" group of blank clinic names. This makes me think that there
is still some shred or artifact in the field that Access is picking up that
makes it "not null".

Any ideas why some of the supposedly empty fields are being treated as if
they contain something?

That's what happens when the field in the table has its
AllowZeroLength property set to Yes.

If you don't have a good reason for that setting, run an
Update query to change the ZLS to Null:

UPDATE table
SET [Clinic Name]=Null
WHERE [Clinic Name] = ""

Then set the fields property to No so you don't accumulate
any more records with this problem.

If you really need AllowZeroLength set to Yes, then change
your grouping expression to:

=IIf(INz([Group-Clinic Name], "") = "", "(No Group-Clinic
Specified)",[Group-Clinic Name])

 

[Guest]

Thank you Jeff and Marshall! Both your responses were right on the money,
and helped me to solve my problem. Much appreciation!

Steve

I have a query/report where I'm grouping by a text field (Clinic Name) and
for the records with no data in the Clinic Name field, I use an IIF function
to insert "No Clinic Name Specified" in the Group Header (also in the details
section).

My formula is: =IIf(IsNull([Group-Clinic Name]),"(No Group-Clinic
Specified)",[Group-Clinic Name])

I noticed that there are now two "groups" of records with empty Clinic Name
fields. One group displays "(No Group-Clinic Name Specified)" , while the
next group has an empty header. I have gone through and deleted the contents
of all of the supposedly empty Clinic Name fields, first with the Del key on
the keyboard (navigating from field cell to field cell with the up/down
arrows, which should select/highlight all data in the field), then the same
process with the Backspace key, then finally one more trip through selecting
the cell with the "white cross" mouse pointer, which selects/highlights the
entire cell, and using the "Delete" command in the Edit menu. None of these
approaches seems to have completely cleared the field, because there are
still two groups.

I notice that if I use [Clinic Name]<>"" instead of IsNull([Clinic Name])
I get the "2nd" group of blank clinic names. This makes me think that there
is still some shred or artifact in the field that Access is picking up that
makes it "not null".

Any ideas why some of the supposedly empty fields are being treated as if
they contain something?

That's what happens when the field in the table has its
AllowZeroLength property set to Yes.

If you don't have a good reason for that setting, run an
Update query to change the ZLS to Null:

UPDATE table
SET [Clinic Name]=Null
WHERE [Clinic Name] = ""

Then set the fields property to No so you don't accumulate
any more records with this problem.

If you really need AllowZeroLength set to Yes, then change
your grouping expression to:

=IIf(INz([Group-Clinic Name], "") = "", "(No Group-Clinic
Specified)",[Group-Clinic Name])