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

Carolyn,

    The result sought by you can be achieved by assigning the source field via a calculated expression (instead of direct binding).

    Sample code snippet in report's module is given below:

' Sample code part in report's open event
' (ControlName & FieldName are string variables
' WDay and WDate used instead of Day & Date
' so as to avoid potential conflict with reserved words)
'====================================
    ControlName = "Txt" & _
                    rstData.Fields("WDay") & "Product"
    FieldName = Format(rstData.Fields("WDate"), "ddd") & _
                    " " & rstData.Fields("ProductName")
                
    Me(ControlName).ControlSource = _
                    "=Nz([" & FieldName & "], 0)"
'====================================

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Carolyn Johnson 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, November 19, 2009 20:39
  Subject: Re: [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