Problem With Null

[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

 

[Brendan Reynolds]

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

(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.

 

[zyus]

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

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

(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.

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

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

(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))))

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

(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.

 

[Brendan Reynolds]

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

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])

 

[John Spencer]

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

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

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])

 

[Brendan Reynolds]

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)))

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