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

Thanks.  Using an expression works.

I had tried putting the expression inside quotations, but it didn't working.  The control source seems to become the literal value.


Carolyn Johnson


  ----- Original Message ----- 
  From: A.D. Tejpal 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, November 19, 2009 2:12 PM
  Subject: Re: [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
  -- 
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com