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

If I use the index for the column, I still get the same result as using the concatenated column name.   

The Len approach will not work because the value of the field is never " ", it's either a number or null.   But the Nz function will not work either.


There is either a way to put an expression in for the control source that I haven't found, or you cannot put an expression in for the control source if you assign it in code. 


Fortunately, my workaround of using a second text box based on the value of the programmatically-assigned text box is working.


Thanks
Carolyn Johnson



  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Thursday, November 19, 2009 7:48 AM
  Subject: Re: [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


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