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

Using Len() gives you the length of the combined date and product name themselves, no the length of the value of the field with that name.  For example, a column produced by the crosstab query might be [Mon Tape], so the Len function is returning 8.   If the column was [Mon Paper], the Len function returns 9.  The only way Len(Format(rstData.Fields("Date"),"ddd") & " " & rstData.Fields("ProductName")) = 1 is when the column produced by the crosstab query  is  [ ].

I need to know if the value in the [Mon Tape] field is null.  If so, I want to print 0.

There still seems to be an issue with trying to use an expression when you assign a control source for a text box by code.   


Thanks
Carolyn


  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Thursday, November 19, 2009 3:30 AM
  Subject: Re: [AccessD] set control source for text box in VBA using Nz


  Hi Carolyn

  A simple method as the length of the space is 1:

  =IIf(Len(Format(rstData.Fields("Date"), "ddd") & " " & rstData.Fields("ProductName"))=1,"0",Format(rstData.Fields("Date"), "ddd") & " " & rstData.Fields("ProductName"))

  /gustav


  >>> cjlabs at worldnet.att.net 18-11-2009 23:34 >>>
  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



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