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

Thanks.  I didn't know that the format funtion would return a string, but I should have at least caught the space I put there.

Unfortunately, I still don't see how to replace the null values with 0 when the control source is set in code.


Carolyn Johnson



  ----- Original Message ----- 
  From: Heenan, Lambert 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, November 18, 2009 12:18 PM
  Subject: Re: [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



  -- 
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com