how to replace the Zero in the file to "-"

[Erictsang]

How can i replace all the "0" in the field into "- "



Thanks

[andy49]

Replace function.

[June7]

Should we presume this is a text type field?

[alansidman]

update query

[Erictsang]

the field is a numeric field, i tried Replace but not allow

[June7]

Of course, it's a number field. Exactly what are you trying to accomplish? Why do you want a "-" in place of 0?

[Erictsang]

Because, i have to use the data to print a voucher and it is nice to show - "-"rather than "0"

[andy49]

Perhaps the voucher could use something similar to



Replace (cstr(fieldname),”0”,”-“)

As it’s source?

[June7]

But that will replace 0 in 103 with hyphen character.

In query or textbox:

IIf([fieldname]=0, "-", [fieldname])

However, won't be able to use number format settings in textbox.

[andy49]

Thanks June. Just realised I misunderstood the problem.

[CJ_London]

you can use the format property for a control. Numbers can have different formats for positive, negative, zero and null. So in your case you could use. The format property does not affect the underlying value. The format function does.

#;#;"-";[Red]"You must enter a number"

[June7]

Nice, I had forgotten this feature of number Format property. However, had to make a slight change:

#;-#;-;[Red]

If you prefer parens for negative:

#;(#);-;[Red]

To show + sign for positive:

+#;-#;-;[Red]


And [Red] just displays numbers entered into Null field as red as you type but once entered go black.