"Not Null" on Query but "Null" on Report

[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

 

[Wayne-I-M]

Hi Peter

The Nz function allows you to insert a value when when a field may or may
not contain variant or null. In your case the value you are inserting is the
word (text) Null into the field if there is nothing there. Hope that makes
sence.

Try this text fields
Nz([AddressLine2],""

or for number fields
Nz([AddressLine2],0

Hope this helps

 

[Peter Gonzales]

Hi Wayne,

My point is that a report is showing a zero-length string as a null value
instead of ZLS. But the query correctly shows ZLS for the same field/record.

Peter

Hi Peter

The Nz function allows you to insert a value when when a field may or may
not contain variant or null. In your case the value you are inserting is
the
word (text) Null into the field if there is nothing there. Hope that
makes
sence.

Try this text fields
Nz([AddressLine2],""

or for number fields
Nz([AddressLine2],0

Hope this helps

--
Wayne
Manchester, England.

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]

If anyone is interested in further information I posted this same question
in the microsoft.public.access.reports newsgroup on 2/5/2007 10:11 AM. Allen
Browne had some useful suggestions.

Peter