If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Returning null values
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? |
#2
|
|||
|
|||
Returning null values
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. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Returning null values
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. "Marshall Barton" wrote in message ... 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. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Returning null values
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 Joseph Greenberg wrote: 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. "Marshall Barton" wrote in message ... 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. -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
Returning null values
I believe it was the report in the end - thanks for the help - it is working
now. "John Spencer" wrote in message ... 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 Joseph Greenberg wrote: 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. "Marshall Barton" wrote in message ... 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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|