[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