Returning null values

[Joseph Greenberg]

I have a report where I think that certain fields are not shrinking because
while I think that the associated query fields look blank, they are no in
fact null. How can I ensure that a query field (that is the result of an
expression) is in fact null, and not just blank?

 

[Marshall Barton]

I have a report where I think that certain fields are not shrinking because
while I think that the associated query fields look blank, they are no in
fact null. How can I ensure that a query field (that is the result of an
expression) is in fact null, and not just blank?

What is the query field's expression?

If worse comes to worst, you can use a text box expression
in the report:
=Trim(Nz(thefield,""))
That will result in a zero length string (ZLS) if the field
contains only spaces, is Null or a ZLS. A report text box
will shrink if its value is Null, a ZLS or if the text box
is invisible.

 

[Joseph Greenberg]

I put the Trim(NZ code around my text box on my report and it is still not
shrinking. The query that builds the report is calling another query, which
is populating the field as "PrefEmailMem1:
LCase([PreferredEmail])".PreferredEmail is a field in my memberdetail table
of 50 characters, where I have to allow zero length (not everyone has email
).

Bottom line is that it is still not shrinking.

 

[John Spencer]

Try using the following expression in the query. It should force a null value
to be returned when PreferredEmail consists of a null, a zero-length string,
or multiple space characters.

PrefEmailMem1: IIF(Len(Trim(PreferredEmail & ""))>0
,LCase([PreferredEmail])
,NULL)

If that does not work, then I would suspect that the problem lies not in the
data but in something you are doing in the report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I put the Trim(NZ code around my text box on my report and it is still not
shrinking. The query that builds the report is calling another query, which
is populating the field as "PrefEmailMem1:
LCase([PreferredEmail])".PreferredEmail is a field in my memberdetail table
of 50 characters, where I have to allow zero length (not everyone has email
).

Bottom line is that it is still not shrinking.

 

[Joseph Greenberg]

I believe it was the report in the end - thanks for the help - it is working
now.

Try using the following expression in the query. It should force a null
value to be returned when PreferredEmail consists of a null, a zero-length
string, or multiple space characters.

PrefEmailMem1: IIF(Len(Trim(PreferredEmail & ""))>0
,LCase([PreferredEmail])
,NULL)

If that does not work, then I would suspect that the problem lies not in
the data but in something you are doing in the report.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I put the Trim(NZ code around my text box on my report and it is still
not shrinking. The query that builds the report is calling another query,
which is populating the field as "PrefEmailMem1:
LCase([PreferredEmail])".PreferredEmail is a field in my memberdetail
table of 50 characters, where I have to allow zero length (not everyone
has email ).

Bottom line is that it is still not shrinking.

Joseph Greenberg wrote:

I have a report where I think that certain fields are not shrinking
because
while I think that the associated query fields look blank, they are no
in
fact null. How can I ensure that a query field (that is the result of
an
expression) is in fact null, and not just blank?

What is the query field's expression?

If worse comes to worst, you can use a text box expression
in the report:
=Trim(Nz(thefield,""))
That will result in a zero length string (ZLS) if the field
contains only spaces, is Null or a ZLS. A report text box
will shrink if its value is Null, a ZLS or if the text box
is invisible.