Null Values

[Colin Basterfield]

Hi,

Is there equivalent NVL (Null values) function in Access so that if I get a
null value back I can substitue it with a zero so that the summing and total
sum bits work in my query.

I am left joining some tables and even without data in some of the tables I
still get rows back, but the emptry tables return a null, so that stops my
sum query working.

Many thanks in advance
Colin B

 

[George Nicholson]

The NZ (NullToZero) function:

NZ(Value1, Value2)
Value1 will be returned unless it is Null. In that case Value2 is returned.

Standard Usage:
NZ([MyTextField],"")
NZ([MyNumberField],0)
but you can substitute whatever you feel appropriate for "" or 0.

HTH,
George Nicholson

Remove Junk from reply-to before using.

 

[John Vinson]

Hi,

Is there equivalent NVL (Null values) function in Access so that if I get a
null value back I can substitue it with a zero so that the summing and total
sum bits work in my query.

Yes: NZ().

e.g. NZ([fieldname]) returns 0 if fieldname is NULL
NZ([fieldname], "Empty Field") returns the text string Empty Field if
it's null
In either case the function just returns the field value if it is not
null.

 

[Colin Basterfield]

Hi John,

Yes pretty much as soon as I had posted the message, I noticed it in one of
the other questions, but the subject was more subtle so I missed it first
scan, anyways that works in access, but when I put it into my app that talks
to the access database it failed, I have some Access components that I use
in Delphi and these seem not to like it, so I ended up using switch instead.

Thanks for your time
Colin

Hi,

Is there equivalent NVL (Null values) function in Access so that if I get a
null value back I can substitue it with a zero so that the summing and total
sum bits work in my query.

Yes: NZ().

e.g. NZ([fieldname]) returns 0 if fieldname is NULL
NZ([fieldname], "Empty Field") returns the text string Empty Field if
it's null
In either case the function just returns the field value if it is not
null.

 

[Michel Walsh]

Hi,


Nz( ) is a VBA function, not native in Jet, so, you need to use the iif
statement (built in Jet), when you are outside Access:

iif( x is null, 0, x )


Note that if you use SUM(expression) a null in the expression would not
influence the summation, but if you use +, sure, a NULL would be propagated.



Hoping it may help,
Vanderghast, Access MVP

Hi John,

Yes pretty much as soon as I had posted the message, I noticed it in one of
the other questions, but the subject was more subtle so I missed it first
scan, anyways that works in access, but when I put it into my app that talks
to the access database it failed, I have some Access components that I use
in Delphi and these seem not to like it, so I ended up using switch instead.

Thanks for your time
Colin

get

a

null value back I can substitue it with a zero so that the summing and total
sum bits work in my query.

Yes: NZ().

e.g. NZ([fieldname]) returns 0 if fieldname is NULL
NZ([fieldname], "Empty Field") returns the text string Empty Field if
it's null
In either case the function just returns the field value if it is not
null.