Null Values

  • Thread starter Thread starter Colin Basterfield
  • Start date Start date
C

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
 
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.
 
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.
Click to expand...
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.
 
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

John Vinson said:
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.
Click to expand...
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.
Click to expand...
 
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


Colin Basterfield said:
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.
Click to expand...
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.
Click to expand...
Click to expand...
 
Back
Top