Convert zero to empty string

[DJJ]

Is it possible to convert a numeric zero to an emtpy string? The str, cstr
and format functions all return zero as a string.

Thanks in advance...

DJ

 

[SusanV]

For what purpose?

 

[DJJ]

I want to shrink a field that keeps displaying a zero value so it does not
appear on a report or leave a blank space using the CanShrink property. The
CanShrink property may only work with nulls but I wanted to give it a shot
anyway.

 

[Guest]

You could use an IIf statement.

IIf([YourField] = 0, "", [YourField])

Still that might bomb out if the control is expecting a number. Have you
considered using a null instead of an empty string? I almost always perfer
nulls to empty strings.

IIf([YourField] = 0, Null, [YourField])

 

[Guest]

I find that if you set the default value of a number filed to blank that
really helps, why whould I want a default value of zero anyway? Then if you
want to delete all of the "0" values you can run a delete query...

 

[Graham Mandeno]

Hi DJ

Do you really want to convert zero to an empty string, or do you simply want
to *display* an empty string for zero values?

If it is the latter, then you can use the Format property of the textbox
where the value is displayed. For numeric values, the format property
string has three parts, separated by semicolons:

1. format if value is positive
2. format if value is negative
3. format is value is zero
4. format is value is Null

So, you could set the format string to something like this:

0.0##;-0.0##;;;

Because the third part is empty, nothing will be displayed is the value is
zero.

 

[SusanV]

DJJ,

In that case, rather than mess with your data, use the OnFormat event of the
report detail section to hide the control if it contains a zero:

If Me.YourFieldControl <>0 Then
Me.YourFieldControl.Visible = True
Else
Me.YourFieldControl.Visible = False
End If

(Change YourFieldControl to the actual name of the control)

 

[DJJ]

Jerry,



That does the trick (null value). The field now shrinks instead of
displaying zero.



FYI - The field displays a discounted amount on a custom invoice report
which is stored in a proprietary SQLServer table where the default value is
set to zero. The client only wanted the discount field to display when an
actual discount was given to a customer.



Thanks to all.



DJ

You could use an IIf statement.

IIf([YourField] = 0, "", [YourField])

Still that might bomb out if the control is expecting a number. Have you
considered using a null instead of an empty string? I almost always perfer
nulls to empty strings.

IIf([YourField] = 0, Null, [YourField])
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Is it possible to convert a numeric zero to an emtpy string? The str,
cstr
and format functions all return zero as a string.

Thanks in advance...

DJ