Getting #Error# on numeric NULL value

[teqtaq]

I have a text boxes with source as a Numeric datatype.
I want to have dash displayed when there is no value and I use
=IIf([CountOfPhone]="","--",[CountOfPhone])
or
=IIf(ISNULL([CountOfPhone]),"--",[CountOfPhone]))

but all I get is #error#

Please, help.

 

[Remou]

Where is CountOfPhone coming from? Have you tried Nz?

 

[teqtaq]

=IIf(Nz([CountOfPhone]),[CountOfPhone],"--") doesn't work as well.

It is coming from the table that is having all these fields Appended into it. Some Long Integers, some Double.

There is default value 0 set to every Numeric field - however blanks instead of 0s are in the table.

Could that do with anything?

Thanks

 

[Remou]

I meant Nz without If:

Nz(CountOfPhone,"--")

However, you may have zero-length strings, in which case:

IIf(Trim(CountOfPhone & "")="","--",CountOfPhone)

 

[teqtaq]

Imagine nothing works?
I can't believe this nonsense.
What could that be in those fields?

Thanks

 

[Remou]

What happens if you simply set the control source of the textbox to CountofPhone?

 

[teqtaq]

It will display numbers. No manipuilations. All counts and averages were done in Access and appended to a table that is the Source for this Report.
Now few text boxes set to display values from the table and client wants to make sure there is no missing value by seeing dash if nothing to display.
It is so easy - it makes me sick! LOL

Please, let's try something else.

 

[Remou]

Ok. Change the name of the control from CountOfPhone to say, txtCountOfPhone and then try an If or Nz again.

 

[teqtaq]

OMG!!!!!!!!!!!!
It worked - how did you know? THANKS SO MUCH!!!!!
(insert happy dance)

 

[JoeAtWork]

Access will sometimes throw a hissy-fit if you name the control the same as a field name. Not often, just enough to keep you guessing.

 

[Remou]

Access 2000 will always have problems when a report has a control with the name of a field that does not contain that field.

 

[teqtaq]

Mine is 2003 converted and to be totally honest I was too lazy to change names because I had to change about 50!
But when I was TOLD - it is a diff story!

You guys are best!

 

[ProgramError]

Hence the reason for naming conventions.
ie textboxes should prefixed with txt
labels with lbl
tables with tbl
These also help in identifying the type of the control. You can use your own conventions but there are established ones out there.


Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.