[AccessD] set control source for text box in VBA using Nz

Carolyn,

I have a problem with this use of Nz

 Nz(Format(rstData.Fields("Date"), "ddd") & " " & rstData.Fields("ProductName"), 0)

The expression inside Nz() is *never* null for several reasons.

Taking the first part of the expression: Format(rstData.Fields("Date"), "ddd")

The field named 'Date' (not a good name for a field as 'Date' is a reserved word in VBA) is first being formatted and then passed to Nz, but therein lies a problem, the Format function will change null values to something that is not null. 

Type the following in the immediate window and hit enter:

? "!" & format(null,"dddd") & "!", "@" & nz(format(null,"dddd"),0) & "@", "$" &  nz(null,0) & "$"

The output is:

!!            @@            $0$

Notice that nothing is printed between the two @ symbols. That is because format(null,"dddd") does not return null. Instead it returns an *empty string*. Format always returns some sort of string, and there is no such thing as a null string. As a result of this nz(format(null,"dddd"),0) does not return 0 (zero), it just gives you an empty string. So the first part of you expression is always a string, never null.

But you should also not that ?  "$" &  nz(null,0) & "$" does indeed result in $0$, because Nz sees the null value.

The next part of your expression also guaranteed not to be null: & " " & 

It adds a space to the first part, and so is not null. That expression inside Nz is always going to be a space character at a minimum, and so the Nz function will never see null.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Carolyn Johnson
Sent: Wednesday, November 18, 2009 12:20 PM
To: Access Developers
Subject: [AccessD] set control source for text box in VBA using Nz

Access2003, WinXP

I have a report using a dynamic crosstab query.  I have set the control source for the text boxes based on the output field names of the crosstab query in the Report open property of the report.  I would like to have 0 printed instead of nothing if the value from the crosstab is null.


When I set the control source as 

        Me.Controls("txt" & rstData.Fields("Day") & "Product").ControlSource = Nz(Format(rstData.Fields("Date"), "ddd") & " " & rstData.Fields("ProductName"), 0)

in the Report open code, the Nz function is ignored.  The data are dsiplayed, but the null values are blank instead of 0.


If I set the control source in the property of the text box, the Nz function would work.


Is there a way to use the Nz function if the control source is set by code?



Thanks
Carolyn Johnson
St Louis, MO