using isnull

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi

I want to replace all 0 or null entries with the character "-"

what will the syntax be?

I have tried

=iif(isnull(forms1.txtbox),"-",iif(forms1.txtbox=0,"-",forms1.txtbox))

but I get incorrect syntax message
 
Where are you running the code from, within the form "Forms1" or elsewhere?

Forms!Forms1.txtbox
or
Me.txtbox
 
thanks but I got it wrong . . . .

Here is what I have in the controlsource of a textbox. This works ok.
=IIf([Mth0]=0,"-",[Mth0])

what I want is to have the check for IsNull as well
Thanks
Al
 
=IIf([Mth0]=0 Or IsNull([Mth0]), "-", [Mth0])

This will require the field name and the name of the textbox to be
different. If they are both called Mth0, then try changing the name of the
textbox to txtMth0. Use caution when you do this, auto correct may try
changing the Mth0 in the equation to txtMth0 also. If this happens, change
it back in the equation.

--
Wayne Morgan
MS Access MVP


Newbie said:
thanks but I got it wrong . . . .

Here is what I have in the controlsource of a textbox. This works ok.
=IIf([Mth0]=0,"-",[Mth0])

what I want is to have the check for IsNull as well
Thanks
Al
Click to expand...
 
If you want to display a "-" for null, no need for a formula.
Just set the Format property of the text box to:
#,##0;-#,##0;-;-

The 4 parts (separated by semicolons) indicate:
- how to show a positive number (optional digits except the last one);
- how to show a negative number (same with minus);
- how to show a zero (as a dash);
- how to show a null (as a dash).
 
Thanks - just what I was looking for
Al



Allen Browne said:
If you want to display a "-" for null, no need for a formula.
Just set the Format property of the text box to:
#,##0;-#,##0;-;-

The 4 parts (separated by semicolons) indicate:
- how to show a positive number (optional digits except the last one);
- how to show a negative number (same with minus);
- how to show a zero (as a dash);
- how to show a null (as a dash).
Click to expand...
 
Back
Top