Expression Builder Problem

[benthamq]

Hello,

I am working on a form in which I am attempting to use the expression builder to automatically fill-in a text box based on user entries in other controls on the form.


Those controls are one text box, four combo boxes, and one check box; I would like the expression builder to concatenate the values in those controls into one string and display it in the text box. If and only if all the controls are left blank, I would like an alternate string, "Enter a new fabric" to appear. I have set all six controls to contain nothing (="" for the combo boxes and text box, and =False for the check box) as their default values to enable this to work.

I am attempting to use the Nz function; the concatenation part of this is working great - the text box populates nicely as the controls are updated in form view.

However, the default string that I would like to appear when all the controls are empty/null is never appearing. I assume this means that even when blank, all the controls are not evaluating to null.

If anyone can offer any advice on how I might correct this, please let me know. As always, I greatly appreciate any help. Below is my expression:

=Nz([txtFabricWidth] & [cmbFabricWidthUnits].[Column](1) & " " & [cmbFabricContent].[Column](1) & " " & IIf([chkFabricPrinted]=True,"Printed","") & " " & [cmbFabricType].[Column](1) & " " & [cmbFabricColor].[Column](1),"Enter a new fabric below")

Thanks

[TheShabz]

The syntax in a query for a form control is Forms!FormName!ControlName. However, the way you have it there, What you have as the criteria for Nz will never be null as you have & " " &. If everything else is null your result will be four spaces.

[benthamq]

Thanks very much for the fast reply.

I ditched trying to use the Nz function since per your advice the four instances of & " " & cannot evaluate to null. I am now using an IIf statement like this and it works:

=IIf(IsNull([Forms]![frmFabrics]![txtFabricWidth]) And IsNull([Forms]![frmFabrics]![cmbFabricWidthUnits].[Column](1)) And IsNull([Forms]![frmFabrics]![cmbFabricContent].[Column](1)) And [Forms]![frmFabrics]![chkFabricPrinted]=False And IsNull([Forms]![frmFabrics]![cmbFabricType].[Column](1)) And IsNull([Forms]![frmFabrics]![cmbFabricColor].[Column](1)),"Enter a new fabric",[Forms]![frmFabrics]![txtFabricWidth] & [Forms]![frmFabrics]![cmbFabricWidthUnits].[Column](1) & " " & [Forms]![frmFabrics]![cmbFabricContent].[Column](1) & " " & IIf([Forms]![frmFabrics]![chkFabricPrinted]=True,"Printed","") & " " & [Forms]![frmFabrics]![cmbFabricType].[Column](1) & " " & [cmbFabricColor].[Column](1))

I also corrected the syntax, though I noticed that when I used the expression builder to help me build this, if I just selected the field name from the list it only displayed the control name without [Forms]![Formname] and it still worked; I assume the expression builder when used to build an expression for a specific control on a form has a built-in way of specifying the equivalent of what would be, "Me.ControlName" in VBA.

Again, much thanks for your help. I will mark this solved.

[TheShabz]

How bout evaluating the length of the string should everything be null.
So, IF (Len(ConcatenatedString) = 4, "enter new fabric", WhateverYourElseIs)