Using VAL function with nulls

[terrytek]

I have a field SS of the text data type that contains numbers and plus signs, ex. 208+ . I need to perform calculations on the number, so used the Access expression
SSNum: Val ([SS]) in a query.
This works fine and returns just the number without the +. However, there are some SS fields that are null, and the query results show #Error in the SSNum field in those cases. I have tried putting Nz before the whole expression, inside the Val expression, and any other way I can think of with no luck; either it throws an error when I try to run the query, or the query runs but the SSNum field next to null SS values still reads #Error.

How can I deal with null values so that the VAL function doesn't return an error?

 

[sneuberg]

Try

SSNum: IIf(IsNull([SS]), Null, Val([SS]))

 

[Cronk]

Alternatively

SSNum: Val([SS] & "")

 

[sneuberg]

Alternatively

SSNum: Val([SS] & "")

Please be aware that this will place 0 in the result if SS is Null.

 

[CJ_London]

another option is

val(nz([SS],"0"))

 

[Cronk]

Sneuberg,

Um,
? Null & ""
""

? val("")
0

 

[terrytek]

Please be aware that this will place 0 in the result if SS is Null.

Having a 0 is fine, since I will need to perform calculations on this field.

I appreciate the quick and useful help from all!!