how to ger rid of #error?

[Jeff06]

I have column value which is text and have missing value. I want to chang it to numerci.

table1
value
2
3
5

8

7
the code iff(clng(value)>5, 1, 0) as newv
will give result
value newv
2 0
3 0
5 0
#error
8 1
#error
7 1

I do not want error to be three, I want it to be 0 or missing. how can i do that? Thank you very much.

Jeff

 

[neileg]

What is in the blanks? Is it a null or a zero length string or a space? Or can it be a combination of these?
If it's always a null, you can use Nz() to convert this to a zero. If it's a zero length string, use an Iff() statement to test for "", and if it's a space use Iff() to test for " ".

 

[KeithG]

the code iif(isnull(value)=true,0,iif(clng(value)>5, 1, 0)) as newv

 

[neileg]

the code iif(isnull(value)=true,0,iif(clng(value)>5, 1, 0)) as newv

Keith this won't deal with a space or a zls. Also you don't need the '=true' in your statement.

 

[KeithG]

True I guess I made the assumption that missing meant a Null Valuse. Technically a zero length string is not a missing value. Also I like to put in the '=True' because I think it helps a novice understand the code better.

 

[Simon_MT]

I prefer to default the numeric fields to zero.

I do test for IsNumeric patricularly when subforms are used, if the record is present:
if IsNumeric etc
do something
else
do something else

Simon

 

[rainman89]

cant u use the NZ function?

oops just saw this from neil

If it's always a null, you can use Nz() to convert this to a zero. If it's a zero length string, use an Iff() statement to test for "", and if it's a space use Iff() to test for " ".

 

[Moniker]

The Nz() function is the one to use. Here's your code with it properly implemented:

OLD CODE:
iff(clng(value)>5, 1, 0) as newv

NEW CODE:
iff(clng(Nz(value),0))>5, 1, 0) as newv

Keep in mind that Nz allows you to assign a different value if there is a null. The format is like this:

Nz(Value_To_Check,Value_If_Null)

In English:

If "Value_To_Check" is a NULL value, then change it to "Value_If_Null". Therefore, in your code, the (Nz(value),0) says, "If value is NULL, change it to a zero."

 

[neileg]

Moniker, Nz() only works if the data is null. As I have outlined above, it won't work on spaces or zls.

 

[Dragonchaser]

I have the same issue. What If it's a ZLS?

Found this thread in the search, saves me starting a new topic.
So, how would you convert a zls to 0?

Scenario: I have 2 IIf expressions. They both state that when true return "" when false return the value required. I have another expression to sum them up, but I get #Error when trying to add "".

Is there simply a way turn return Null instead of ""(zls) in an IIf expression so I can use Nz() to avoid the #Error?

 

[gemma-the-husky]

nz works in a query

but if its REALLY a number, then set them to zero manually and THEN change the table data type to a numeric one

 

[Dragonchaser]

Thanks for the response Gemma, but I'm still confused.

They're just textboxes in a form. Here's the expressions:

Here's the actual code:
ControlName: FullPipe
=IIf(IsNull([USPipe]),"", [USPipe]-[USMid]-[USGas])

ControlName: HalfPipe
=IIf(IsNull([DSPipe]),"", [DSPipe]-[DSMid])

ControlName: OverallLength
=[FullPipe] - [HalfPipe]

OverallLength returns #Error if either DSPipe or USPipe is Null

Basically I want the ""(zls) to work like a 0 (zero) without actually showing the 0 (zero) in the control. Is that possible?

 

[gemma-the-husky]

i think the iif (immediate iif) actually evaluates all the expressions, so the second half will fail.

you ought to just do something like

nz(uspipe)-nz(usmid)-nz(usgas)

this might still fail tho, as you are trying to add text values

- they may automatically cast to values, but its more likely that they will fail. if the numbers should be numeric, i think it would be better to get them that way in the table