Problem With Null

  • Thread starter Thread starter zyus
  • Start date Start date
Z

zyus

I hv this expression in my report field

(Sum(Abs(([NPLFLG] Like "N" And [ARRMTH] Not Between 1 And 5))))

Problem arise when my NPLFLG field having Null Value...how can i handle it ?

TQ
 
zyus said:
I hv this expression in my report field

(Sum(Abs(([NPLFLG] Like "N" And [ARRMTH] Not Between 1 And 5))))

Problem arise when my NPLFLG field having Null Value...how can i handle it
?

TQ
Click to expand...


(Sum(Abs((NZ([NPLFLG],"") Like "N" And [ARRMTH] Not Between 1 And 5))))

.... or ...

(Sum(Abs((([NPLFLG] & "") Like "N" And [ARRMTH] Not Between 1 And 5))))

In other words, use either the NZ() function or concatenation to convert the
Null values to empty strings.

Note that those are double quotes with nothing between them, no spaces.
 
My NPLFLG will consist of these 3 value

1. N
2. Y
3. null

null is equal to N.
null is NOT equal to Y

Can the expression suggested by you will handle my issue as above

Brendan Reynolds said:
zyus said:
I hv this expression in my report field

(Sum(Abs(([NPLFLG] Like "N" And [ARRMTH] Not Between 1 And 5))))

Problem arise when my NPLFLG field having Null Value...how can i handle it
?

TQ
Click to expand...


(Sum(Abs((NZ([NPLFLG],"") Like "N" And [ARRMTH] Not Between 1 And 5))))

... or ...

(Sum(Abs((([NPLFLG] & "") Like "N" And [ARRMTH] Not Between 1 And 5))))

In other words, use either the NZ() function or concatenation to convert the
Null values to empty strings.

Note that those are double quotes with nothing between them, no spaces.
Click to expand...
 
--
Jeff C
Live Well .. Be Happy In All You Do


zyus said:
My NPLFLG will consist of these 3 value

1. N
2. Y
3. null

null is equal to N.
null is NOT equal to Y

Can the expression suggested by you will handle my issue as above
Click to expand...

(Sum(Abs((NZ([NPLFLG],"N") Like "N" And [ARRMTH] Not Between 1 And 5))))

... or ...

(Sum(Abs((([NPLFLG] & "N") Like "N" And [ARRMTH] Not Between 1 And 5))))








Brendan Reynolds said:
zyus said:
I hv this expression in my report field

(Sum(Abs(([NPLFLG] Like "N" And [ARRMTH] Not Between 1 And 5))))

Problem arise when my NPLFLG field having Null Value...how can i handle it
?

TQ
Click to expand...


(Sum(Abs((NZ([NPLFLG],"") Like "N" And [ARRMTH] Not Between 1 And 5))))

... or ...

(Sum(Abs((([NPLFLG] & "") Like "N" And [ARRMTH] Not Between 1 And 5))))

In other words, use either the NZ() function or concatenation to convert the
Null values to empty strings.

Note that those are double quotes with nothing between them, no spaces.
Click to expand...
Click to expand...
 
zyus said:
My NPLFLG will consist of these 3 value

1. N
2. Y
3. null

null is equal to N.
null is NOT equal to Y
Click to expand...


Null is neither equal to nor not equal to anything. Try the following in the
Immediate window ...

? Null = "N"
Null
? Null <> "Y"
Null

The NZ() expression can be used to convert a Null value to another value. It
takes an optional second argument which specifies the value you want to be
used to replace Null values. The following example will replace Null values
with the letter "N" ...

NZ([NPLFLG], "N")

NZ() is an Access function, it will work fine as long as your query is used
only within Access. If you think you may need to use your query other than
via Access, you can use the IIF() and IsNull() functions instead ...

IIF(IsNull([NPLFLG]), "N", [NPLFLG])
 
I would use the following expression in a query.n
Abs(Sum((NPLFLG is Null or NPLFLG = 'N') AND ArrMth Not Between 1 and 5)))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Brendan said:
zyus said:
My NPLFLG will consist of these 3 value

1. N
2. Y
3. null

null is equal to N.
null is NOT equal to Y
Click to expand...


Null is neither equal to nor not equal to anything. Try the following in
the Immediate window ...

? Null = "N"
Null
? Null <> "Y"
Null

The NZ() expression can be used to convert a Null value to another
value. It takes an optional second argument which specifies the value
you want to be used to replace Null values. The following example will
replace Null values with the letter "N" ...

NZ([NPLFLG], "N")

NZ() is an Access function, it will work fine as long as your query is
used only within Access. If you think you may need to use your query
other than via Access, you can use the IIF() and IsNull() functions
instead ...

IIF(IsNull([NPLFLG]), "N", [NPLFLG])
Click to expand...
 
John Spencer said:
I would use the following expression in a query.n
Abs(Sum((NPLFLG is Null or NPLFLG = 'N') AND ArrMth Not Between 1 and
5)))
Click to expand...


Good point, John, yes, no function call required.
 
Back
Top