using isnull

[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

 

[Wayne Morgan]

Where are you running the code from, within the form "Forms1" or elsewhere?

Forms!Forms1.txtbox
or
Me.txtbox

 

[Newbie]

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

 

[Wayne Morgan]

=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

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

 

[Allen Browne]

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).

 

[Newbie]

Thanks - just what I was looking for
Al

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).