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

Hi Carolyn

OK, that was not what I could read from what you wrote. But you may need a function that reads the output from the crosstab ignoring the column names and returns the desired output.

Or perhaps this will do:

  =IIf(Len(Format(rstData.Fields(2).Value, "ddd") & " " & rstData.Fields(3).Value)=1,"0",Format(rstData.Fields(2).Value, "ddd") & " " & rstData.Fields(3).Value)

where 2 and 3 are the column index of the fields from the crosstab.
Property Value will return the value of the fields while property Name would return the name of the fields.

/gustav


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