A ZLS being reported as null

[Peter Gonzales]

Hi all,

I have a need to import information into my database from a variety of files
and formats. I want to set up a group of tables that will get populated from
the import file(s). Then from one form per table I will allow mapping the
information to what is already on file. Also I need to produce a report
showing the changes that will be made to the existing database. Then
actually import the information.

As a design decision I allow nulls on all the fields. Then I check if a
field is null or not. If null that means it was not available from the
import file and hence is unknown. Otherwise it is known and a zero-length
string or a 0 is actually the value coming from the import file.

As an example, on a Person to import I can POSSIBLY have first name, last
name, suffix, address line 1, address line 2, city, state, zipcode,
telephone 1, telephone 2 and email. Import file #1 has both address lines
available so if the address line 2 is blank I would force a zero-length
string into that record. But import file #2 only has one address line so I
would not load anything into address line 2 and expect it to be null.

My problem is that a field is showing up "Not null" in a query and "Null" on
a report. If I create a new query based on the table I populated from the
import files and for address line 2 have a column with Expr1:
Nz([AddressLine2],"Null") the fields with a zero-length show blank and the
nulls show "Null". Great... just what I expect. But if I create a new report
based on the table and place the same expression as the source for a textbox
=Nz([AddressLine2],"Null" I am getting "Null" on the zero-length strings.

Has anyone experienced this?

TIA

 

[Peter Gonzales]

Hi all,

I created a new empty database. Then created a new table with Field1 (Text,
Required=No,Allow Zero Length=Yes) and Field2
(Numeric,Required=No,Default=No value here). Switched to datasheet, tabbed
to Field2 and entered a 2. Next record entered a blank in Field1 and nothing
in Field2. Backedup to Field1 and clear it.

Created a query with 2 columns where I use Nz to see if the field is null or
not. Query shows:
Record 1 Null, 2
Record 2 Null, Null

Created a report with both fields having as part of control source Nz to see
if the field is null or not. Report shows:
Record 1 Null, 2
Record 2 Null, Null

Created a form on table. On the afterupdate for Field1 I used If
IsNull(Field1) Then Field1 = "" to force a ZLS. Opened form and on record
one entered a blank in Field1 then cleared and moved to record 2. Exit form.

Reran query and it shows (where * is nothing, blank, ZLS whatever you want
to call it):

Record 1 *, 2
Record 2 Null, Null
Great - just what I expect and WANT.

Reran report and it shows:
Record 1 Null, 2
Record 2 Null, Null

Why does the query show record1/field1 to be blank (see * above) but the
report shows null?

Peter

Hi all,

I have a need to import information into my database from a variety of
files
and formats. I want to set up a group of tables that will get populated
from
the import file(s). Then from one form per table I will allow mapping the
information to what is already on file. Also I need to produce a report
showing the changes that will be made to the existing database. Then
actually import the information.

As a design decision I allow nulls on all the fields. Then I check if a
field is null or not. If null that means it was not available from the
import file and hence is unknown. Otherwise it is known and a zero-length
string or a 0 is actually the value coming from the import file.

As an example, on a Person to import I can POSSIBLY have first name, last
name, suffix, address line 1, address line 2, city, state, zipcode,
telephone 1, telephone 2 and email. Import file #1 has both address lines
available so if the address line 2 is blank I would force a zero-length
string into that record. But import file #2 only has one address line so I
would not load anything into address line 2 and expect it to be null.

My problem is that a field is showing up "Not null" in a query and "Null"
on
a report. If I create a new query based on the table I populated from the
import files and for address line 2 have a column with Expr1:
Nz([AddressLine2],"Null") the fields with a zero-length show blank and the
nulls show "Null". Great... just what I expect. But if I create a new
report
based on the table and place the same expression as the source for a
textbox
=Nz([AddressLine2],"Null" I am getting "Null" on the zero-length strings.

Has anyone experienced this?

TIA

 

[Allen Browne]

Peter, that should work.

We don't know what's in the RecordSource of the report.
DLookup() fails to distinguish between ZLS and Null.
Could that be a factor?

If not, double-check the report's RecordSource is the table you expect. Then
ask Access to tell you what's in the field, by adding a couple more text
boxes to the report such as:
=([Field1] = "")
and:
=([Field1] Is Null)
and
=Len([Field1])

Also, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
and then compact the database:
Tools | Database Utilities | Compact/Repair

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

I created a new empty database. Then created a new table with Field1
(Text,
Required=No,Allow Zero Length=Yes) and Field2
(Numeric,Required=No,Default=No value here). Switched to datasheet,
tabbed
to Field2 and entered a 2. Next record entered a blank in Field1 and
nothing
in Field2. Backedup to Field1 and clear it.

Created a query with 2 columns where I use Nz to see if the field is null
or
not. Query shows:
Record 1 Null, 2
Record 2 Null, Null

Created a report with both fields having as part of control source Nz to
see
if the field is null or not. Report shows:
Record 1 Null, 2
Record 2 Null, Null

Created a form on table. On the afterupdate for Field1 I used If
IsNull(Field1) Then Field1 = "" to force a ZLS. Opened form and on record
one entered a blank in Field1 then cleared and moved to record 2. Exit
form.

Reran query and it shows (where * is nothing, blank, ZLS whatever you want
to call it):

Record 1 *, 2
Record 2 Null, Null
Great - just what I expect and WANT.

Reran report and it shows:
Record 1 Null, 2
Record 2 Null, Null

Why does the query show record1/field1 to be blank (see * above) but the
report shows null?

Peter

Hi all,

I have a need to import information into my database from a variety of
files
and formats. I want to set up a group of tables that will get populated
from
the import file(s). Then from one form per table I will allow mapping the
information to what is already on file. Also I need to produce a report
showing the changes that will be made to the existing database. Then
actually import the information.

As a design decision I allow nulls on all the fields. Then I check if a
field is null or not. If null that means it was not available from the
import file and hence is unknown. Otherwise it is known and a zero-length
string or a 0 is actually the value coming from the import file.

As an example, on a Person to import I can POSSIBLY have first name, last
name, suffix, address line 1, address line 2, city, state, zipcode,
telephone 1, telephone 2 and email. Import file #1 has both address lines
available so if the address line 2 is blank I would force a zero-length
string into that record. But import file #2 only has one address line so
I
would not load anything into address line 2 and expect it to be null.

My problem is that a field is showing up "Not null" in a query and "Null"
on
a report. If I create a new query based on the table I populated from the
import files and for address line 2 have a column with Expr1:
Nz([AddressLine2],"Null") the fields with a zero-length show blank and
the
nulls show "Null". Great... just what I expect. But if I create a new
report
based on the table and place the same expression as the source for a
textbox
=Nz([AddressLine2],"Null" I am getting "Null" on the zero-length strings.

Has anyone experienced this?

TIA

 

[Peter Gonzales]

Hi Allen,

Thanks for replying. I want to express my thanks for your help and web site.
Its the first place I go for suggestions and problem resolution.

Since I am having the problem in the new database I created just for the
purpose of recreating the problem all my responses are with relation to that
database.

As for the Name AutoCorrect I had forgotten to turn that off. So I did and
compacted before running the tests you suggested. Results are what I have
reported. (On my "real" database I had done that and went as far as creating
a new database, turning off Name Autocorrect and importing everything over -
but still got the problems reported here).

The RecordSource for the report is Table1 - no SQL. So no DLookup involved.

=([Field1] = "") with Format of True/False gives nothing
=([Field1] Is Null) with Format of True/False gives True
=Len([Field1]) with Format of General Number gives nothing

I went back to the query and changed it to include columns that would give
the same info - ie the following SQL
SELECT Nz([Field1],"Null") AS Expr1, Nz([Field2],"Null") AS Expr2,
([Field1]="") AS Expr3, ([Field1] Is Null) AS Expr4, Len([Field1]) AS Expr5
FROM Table1;
The results were Expr3 = -1, Expr4 = 0 and Expr5 = 0.

FWI - Access 2003 SP2 with database file format 2000 running on XP Pro SP2.

Do you have any other suggestions?

Peter

Peter, that should work.

We don't know what's in the RecordSource of the report.
DLookup() fails to distinguish between ZLS and Null.
Could that be a factor?

If not, double-check the report's RecordSource is the table you expect.
Then ask Access to tell you what's in the field, by adding a couple more
text boxes to the report such as:
=([Field1] = "")
and:
=([Field1] Is Null)
and
=Len([Field1])

Also, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
and then compact the database:
Tools | Database Utilities | Compact/Repair

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

I created a new empty database. Then created a new table with Field1
(Text,
Required=No,Allow Zero Length=Yes) and Field2
(Numeric,Required=No,Default=No value here). Switched to datasheet,
tabbed
to Field2 and entered a 2. Next record entered a blank in Field1 and
nothing
in Field2. Backedup to Field1 and clear it.

Created a query with 2 columns where I use Nz to see if the field is null
or
not. Query shows:
Record 1 Null, 2
Record 2 Null, Null

Created a report with both fields having as part of control source Nz to
see
if the field is null or not. Report shows:
Record 1 Null, 2
Record 2 Null, Null

Created a form on table. On the afterupdate for Field1 I used If
IsNull(Field1) Then Field1 = "" to force a ZLS. Opened form and on record
one entered a blank in Field1 then cleared and moved to record 2. Exit
form.

Reran query and it shows (where * is nothing, blank, ZLS whatever you
want
to call it):

Record 1 *, 2
Record 2 Null, Null
Great - just what I expect and WANT.

Reran report and it shows:
Record 1 Null, 2
Record 2 Null, Null

Why does the query show record1/field1 to be blank (see * above) but the
report shows null?

Peter

Hi all,

I have a need to import information into my database from a variety of
files
and formats. I want to set up a group of tables that will get populated
from
the import file(s). Then from one form per table I will allow mapping
the
information to what is already on file. Also I need to produce a report
showing the changes that will be made to the existing database. Then
actually import the information.

As a design decision I allow nulls on all the fields. Then I check if a
field is null or not. If null that means it was not available from the
import file and hence is unknown. Otherwise it is known and a
zero-length
string or a 0 is actually the value coming from the import file.

As an example, on a Person to import I can POSSIBLY have first name,
last
name, suffix, address line 1, address line 2, city, state, zipcode,
telephone 1, telephone 2 and email. Import file #1 has both address
lines
available so if the address line 2 is blank I would force a zero-length
string into that record. But import file #2 only has one address line so
I
would not load anything into address line 2 and expect it to be null.

My problem is that a field is showing up "Not null" in a query and
"Null" on
a report. If I create a new query based on the table I populated from
the
import files and for address line 2 have a column with Expr1:
Nz([AddressLine2],"Null") the fields with a zero-length show blank and
the
nulls show "Null". Great... just what I expect. But if I create a new
report
based on the table and place the same expression as the source for a
textbox
=Nz([AddressLine2],"Null" I am getting "Null" on the zero-length
strings.

Has anyone experienced this?

TIA

 

[Allen Browne]

Peter, zip the test database you created to demo the problem, and email it
to me using the address spelled out in the sig below.

Include a copy of this thread.

I'm curious because your text box recognised the Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for replying. I want to express my thanks for your help and web
site. Its the first place I go for suggestions and problem resolution.

Since I am having the problem in the new database I created just for the
purpose of recreating the problem all my responses are with relation to
that database.

As for the Name AutoCorrect I had forgotten to turn that off. So I did and
compacted before running the tests you suggested. Results are what I have
reported. (On my "real" database I had done that and went as far as
creating a new database, turning off Name Autocorrect and importing
everything over - but still got the problems reported here).

The RecordSource for the report is Table1 - no SQL. So no DLookup
involved.

=([Field1] = "") with Format of True/False gives nothing
=([Field1] Is Null) with Format of True/False gives True
=Len([Field1]) with Format of General Number gives nothing

I went back to the query and changed it to include columns that would give
the same info - ie the following SQL
SELECT Nz([Field1],"Null") AS Expr1, Nz([Field2],"Null") AS Expr2,
([Field1]="") AS Expr3, ([Field1] Is Null) AS Expr4, Len([Field1]) AS
Expr5 FROM Table1;
The results were Expr3 = -1, Expr4 = 0 and Expr5 = 0.

FWI - Access 2003 SP2 with database file format 2000 running on XP Pro
SP2.

Do you have any other suggestions?

Peter

Peter, that should work.

We don't know what's in the RecordSource of the report.
DLookup() fails to distinguish between ZLS and Null.
Could that be a factor?

If not, double-check the report's RecordSource is the table you expect.
Then ask Access to tell you what's in the field, by adding a couple more
text boxes to the report such as:
=([Field1] = "")
and:
=([Field1] Is Null)
and
=Len([Field1])

Also, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
and then compact the database:
Tools | Database Utilities | Compact/Repair

Hi all,

I created a new empty database. Then created a new table with Field1
(Text,
Required=No,Allow Zero Length=Yes) and Field2
(Numeric,Required=No,Default=No value here). Switched to datasheet,
tabbed
to Field2 and entered a 2. Next record entered a blank in Field1 and
nothing
in Field2. Backedup to Field1 and clear it.

Created a query with 2 columns where I use Nz to see if the field is
null or
not. Query shows:
Record 1 Null, 2
Record 2 Null, Null

Created a report with both fields having as part of control source Nz to
see
if the field is null or not. Report shows:
Record 1 Null, 2
Record 2 Null, Null

Created a form on table. On the afterupdate for Field1 I used If
IsNull(Field1) Then Field1 = "" to force a ZLS. Opened form and on
record
one entered a blank in Field1 then cleared and moved to record 2. Exit
form.

Reran query and it shows (where * is nothing, blank, ZLS whatever you
want
to call it):

Record 1 *, 2
Record 2 Null, Null
Great - just what I expect and WANT.

Reran report and it shows:
Record 1 Null, 2
Record 2 Null, Null

Why does the query show record1/field1 to be blank (see * above) but the
report shows null?

Peter


Hi all,

I have a need to import information into my database from a variety of
files
and formats. I want to set up a group of tables that will get populated
from
the import file(s). Then from one form per table I will allow mapping
the
information to what is already on file. Also I need to produce a report
showing the changes that will be made to the existing database. Then
actually import the information.

As a design decision I allow nulls on all the fields. Then I check if a
field is null or not. If null that means it was not available from the
import file and hence is unknown. Otherwise it is known and a
zero-length
string or a 0 is actually the value coming from the import file.

As an example, on a Person to import I can POSSIBLY have first name,
last
name, suffix, address line 1, address line 2, city, state, zipcode,
telephone 1, telephone 2 and email. Import file #1 has both address
lines
available so if the address line 2 is blank I would force a zero-length
string into that record. But import file #2 only has one address line
so I
would not load anything into address line 2 and expect it to be null.

My problem is that a field is showing up "Not null" in a query and
"Null" on
a report. If I create a new query based on the table I populated from
the
import files and for address line 2 have a column with Expr1:
Nz([AddressLine2],"Null") the fields with a zero-length show blank and
the
nulls show "Null". Great... just what I expect. But if I create a new
report
based on the table and place the same expression as the source for a
textbox
=Nz([AddressLine2],"Null" I am getting "Null" on the zero-length
strings.

 

[Peter Gonzales]

Hi Allen,

Just wanted to be sure you received my email. I sent it about an hour after
your response.

BTW, on the text boxes Text2 & Text4 I had set Visible to No while I was
making sure that no value was printing. Sorry I hadn't changed that back to
Visible = Yes before sending it to you.

Peter

 

[Allen Browne]

Rec'd. As soon as I added Field1 to the report, the other text boxes started
reporting successfully, i.e.:
=IsNull([Field1])
=([Field1] = "")
=Len([Field1])

Sometimes Access optimizes reports by looking at the ControlSource of the
controls and the Sorting'n'Grouping dialog, and if it doesn't find an exact
match for a field name, it doesn't bother fetching it. I suspect that's what
was going on. Adding a text box bound to the field solves the issue. Even if
you set its Visible property to No, Access will fetch the field.

 

[Peter Gonzales]

Allen,

Thanks for the info. I started playing around with the fields some more and
found the following - If the textbox Name and ControlSource are both Field1
then things "work". But if I change the Name to Test and leave the
ControlSource as Field1 then it reverts back to the undesired results.

In your email to me you mentioned an "old" optimization issue. Is there
somewhere that goes more into depth about it? Other gotchas regarding ZLS
and null values that I should be aware of?

Once again thanks for your help.

Peter

Rec'd. As soon as I added Field1 to the report, the other text boxes
started reporting successfully, i.e.:
=IsNull([Field1])
=([Field1] = "")
=Len([Field1])

Sometimes Access optimizes reports by looking at the ControlSource of the
controls and the Sorting'n'Grouping dialog, and if it doesn't find an
exact match for a field name, it doesn't bother fetching it. I suspect
that's what was going on. Adding a text box bound to the field solves the
issue. Even if you set its Visible property to No, Access will fetch the
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Just wanted to be sure you received my email. I sent it about an hour
after your response.

BTW, on the text boxes Text2 & Text4 I had set Visible to No while I was
making sure that no value was printing. Sorry I hadn't changed that back
to Visible = Yes before sending it to you.

Peter

 

[Allen Browne]

Hi Peter

Okay, this isn't really a ZLS/Null issue.

It's do to with field/control naming and report optimization.

I'm not aware of a good resource for this, but sometimes you can't get a
Control Source like this to work:
=[FirstName] & " " & [Surname]
if you don't have text boxes for FirstName and Surname on the report.

Additionally, Access gets confused if you have a text box that has the same
name as a field, but is bound to something else (expression or other field.)
This is not the issue you saw, but it's related.

My personal practice is to use ZLS very rarely. I can only think of one kind
of scenario where I've allowed zero-length strings in a field in the last 2
years. (That has to do with creating a unqiue index.) For every other case,
I actually set Allow Zero Length to No for all fields in the database. Then
you don't have the inefficiency of having to query for both, and the
increased potential for bugs and maintenance issues. If you want it, the
code is here:
http://allenbrowne.com/bug-09.html

Regarding nulls, there's just a handful of cases where users get tripped up.
They're listed here:
http://allenbrowne.com/casu-12.html

Regarding Access making mistakes:
1. DLookup() can't tell the difference between Null and ZLS.
Here's a replacement that can:
http://allenbrowne.com/ser-42.html

2. DISTINCT handles nulls wrongly:
http://allenbrowne.com/bug-12.html

3. Explicit parameters of type TEXT are handled wrongly:
http://allenbrowne.com/bug-13.html

4. Trailing spaces give wrong results:
http://allenbrowne.com/bug-15.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the info. I started playing around with the fields some more
and found the following - If the textbox Name and ControlSource are both
Field1 then things "work". But if I change the Name to Test and leave the
ControlSource as Field1 then it reverts back to the undesired results.

In your email to me you mentioned an "old" optimization issue. Is there
somewhere that goes more into depth about it? Other gotchas regarding ZLS
and null values that I should be aware of?

Once again thanks for your help.

Peter

Rec'd. As soon as I added Field1 to the report, the other text boxes
started reporting successfully, i.e.:
=IsNull([Field1])
=([Field1] = "")
=Len([Field1])

Sometimes Access optimizes reports by looking at the ControlSource of the
controls and the Sorting'n'Grouping dialog, and if it doesn't find an
exact match for a field name, it doesn't bother fetching it. I suspect
that's what was going on. Adding a text box bound to the field solves the
issue. Even if you set its Visible property to No, Access will fetch the
field.

Hi Allen,

Just wanted to be sure you received my email. I sent it about an hour
after your response.

BTW, on the text boxes Text2 & Text4 I had set Visible to No while I was
making sure that no value was printing. Sorry I hadn't changed that back
to Visible = Yes before sending it to you.

Peter

Peter, zip the test database you created to demo the problem, and email
it to me using the address spelled out in the sig below.

Include a copy of this thread.

I'm curious because your text box recognised the Null

 

[Peter Gonzales]

Thanks for your help Allen.

Hi Peter

Okay, this isn't really a ZLS/Null issue.

It's do to with field/control naming and report optimization.

I'm not aware of a good resource for this, but sometimes you can't get a
Control Source like this to work:
=[FirstName] & " " & [Surname]
if you don't have text boxes for FirstName and Surname on the report.

Additionally, Access gets confused if you have a text box that has the
same name as a field, but is bound to something else (expression or other
field.) This is not the issue you saw, but it's related.

My personal practice is to use ZLS very rarely. I can only think of one
kind of scenario where I've allowed zero-length strings in a field in the
last 2 years. (That has to do with creating a unqiue index.) For every
other case, I actually set Allow Zero Length to No for all fields in the
database. Then you don't have the inefficiency of having to query for
both, and the increased potential for bugs and maintenance issues. If you
want it, the code is here:
http://allenbrowne.com/bug-09.html

Regarding nulls, there's just a handful of cases where users get tripped
up. They're listed here:
http://allenbrowne.com/casu-12.html

Regarding Access making mistakes:
1. DLookup() can't tell the difference between Null and ZLS.
Here's a replacement that can:
http://allenbrowne.com/ser-42.html

2. DISTINCT handles nulls wrongly:
http://allenbrowne.com/bug-12.html

3. Explicit parameters of type TEXT are handled wrongly:
http://allenbrowne.com/bug-13.html

4. Trailing spaces give wrong results:
http://allenbrowne.com/bug-15.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the info. I started playing around with the fields some more
and found the following - If the textbox Name and ControlSource are both
Field1 then things "work". But if I change the Name to Test and leave the
ControlSource as Field1 then it reverts back to the undesired results.

In your email to me you mentioned an "old" optimization issue. Is there
somewhere that goes more into depth about it? Other gotchas regarding ZLS
and null values that I should be aware of?

Once again thanks for your help.

Peter

Rec'd. As soon as I added Field1 to the report, the other text boxes
started reporting successfully, i.e.:
=IsNull([Field1])
=([Field1] = "")
=Len([Field1])

Sometimes Access optimizes reports by looking at the ControlSource of
the controls and the Sorting'n'Grouping dialog, and if it doesn't find
an exact match for a field name, it doesn't bother fetching it. I
suspect that's what was going on. Adding a text box bound to the field
solves the issue. Even if you set its Visible property to No, Access
will fetch the field.

Hi Allen,

Just wanted to be sure you received my email. I sent it about an hour
after your response.

BTW, on the text boxes Text2 & Text4 I had set Visible to No while I
was making sure that no value was printing. Sorry I hadn't changed that
back to Visible = Yes before sending it to you.

Peter

Peter, zip the test database you created to demo the problem, and
email it to me using the address spelled out in the sig below.

Include a copy of this thread.

I'm curious because your text box recognised the Null