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

The control source for the text box is

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

For example, it might be [Mon Tape] or [Tue Tape].

If the value is null, I want the text box to show 0.   If I had a fixed crosstab query, I could just put in the control source property of the text box
    =Nz([Mon Tape],0)


With the dynamic query, I have concatenated field names as above.   I can't find a way to add the Nz function around that concatenated field name.

I have tried putting brackets around the field name, saving the field name as a string and putting brackets around it, and putting quotes around it.


It seems that the control source property set in code will only accept a field name, as opposed to an expression with a field name.


I have now gotten around this by making the text box with the concantenated field name invisible, and adding a second text box whole control source is 
    = Nz([concatenated field],0)



Thanks,
Carolyn Johnson





  ----- Original Message ----- 
  From: Heenan, Lambert 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, November 18, 2009 2:19 PM
  Subject: Re: [AccessD] set control source for text box in VBA using Nz


  Just break the problem up? You have two data elements, so you need two Nz calls...

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

  Note that the Nz is applied to the two fields before doing anything else, and that the Nz wrapping the date field is set to return an empty string and not zero. The reason is that if you format 0 as a date you will get 12/31/1899, which was a Saturday so  Format(0, "dddd") will result in "Saturday", probably not what you want.

  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 1:56 PM
  To: Access Developers discussion and problem solving
  Subject: Re: [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



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