Zero --> Null

[jakoBAlmqvist]

Zero --> Null OR "#Error" to Zero!

Is there somekind of reverse Nz() function?

I want Zeros to become null in my query!

..or I almost rather change "#Error" to 0(Zero) if that is possible?

Thanks

-jake

 

[raskew]

Hi -

You might experiment with the following function to see if you could adapt it to your needs.

Function ISB(V) As Integer
'*******************************************
'Name:      ISB (Function)
'Purpose:   Determines if passed argument is either null or blank.
'Source:    [url]http://support.microsoft.com/support/kb/articles/Q95/3/90.asp?PR=ACC[/url]
'*******************************************

    ISB = IIf(IsNull(V) Or V = "", True, False)

End Function

HTH - Bob

 

[#1AccessDummy]

I do something similar to this by using an IIF statement in the query field.

example:

expr1: IIF([tablename].[fieldname] = 0,[tablename].[fieldname] = null,[tablename].[fieldname])

 

[Mile-O]

=IIf(Field=0,Null,0)

 

[jakoBAlmqvist]

I'm trying to use the =IIf(Field=0,Null,0) but it doesn't seem to work.

I putting the statement as a new field in the query, maybe that's not the way to do it?

-jake

 

[Rich]

Shouldn't it be

NewFld:Iif([Field]=0,Null,[Field]) ?

 

[jakoBAlmqvist]

Still doesn't work. When I wirte it it says:

The expression you entered contains invalid syntax.
You may have entered an operand without an operator.

What is wrong?

-jake

 

[Mile-O]

Is it possible you should be using the European method which substitutes commas for semi-colons?

NewFld: IIf([Field]=0;Null;[Field])

 

[jakoBAlmqvist]

That was it!=)

Thanks for all help!