#ERROR Message

building reports desktopintelligence
1

Hello,

I have a Character object and i am trying to convert it into numeric object. Some of the rows in that column are Undefined values which are blank values.

When i create a variable and use the function =ToNumber() then at the undefined values i am getting #ERROR Message. How to remove that error message. At the undefined rows i need to display 00000 because i need to concatenate this object with other objects.

Thanks
Raju

Rajua99 :india: (BOB member since 2004-12-16)

2

First of all, if you’re converting this to a number, you won’t be able to easily concatenate it to a character string. It works, but it’s not ugly. However, if you want it to be a number, try something like this:

=If IsNumber() Then ToNumber() Else 0

Then, if you want to concatenate it, you will want to convert it to a string with something like this:

=FormatNumber((If IsNumber([Flow #]) Then ToNumber([Flow #]) Else 0),“00000”)

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

3

Thanks for the Reply.

Is there a way to apply directly on Segment Four to display ZERO values when they are undefined values before converting into number.

I am using this formula but not successful

= If Not IsNull() Then Else “00000”

Thanks
Raju

Rajua99 :india: (BOB member since 2004-12-16)

4

What happens with that formula? Have you tried reversing it?

= If IsNull() Then “00000” Else

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

5

This is not working but your previous formula worked for me…

[b]
=If IsNumber() Then ToNumber() Else 0

Then, if you want to concatenate it, you will want to convert it to a string with something like this:

=FormatNumber((If IsNumber([Flow #]) Then ToNumber([Flow #]) Else 0),“00000”)
[/b]

Rajua99 :india: (BOB member since 2004-12-16)