Help on Nulls

[dgmorr]

Hey guys,

I'm doing a pretty basic query that

Selects about 5 fields
From Table1
Where

(value=0 AND name <> HOT)
OR
(value=Null AND name <>HOT)

I also have some records (4 specifically) that have a name = nullvalue. When I run this query it picks out all the records with a name, and skips HOT(which is correct), but skips the nulls which I also want to include. Can someone help me in finding the reason for this error?

 

[sportsguy]

you might want to try

you might want to try

(value=0 AND name <> HOT)
OR
(value= Is Null AND name <>HOT)


Value

0 or Is Null


sportsguy

 

[neileg]

I'd use Nz() to turn the nulls to zero than you don't need the OR statement.

 

[dgmorr]

I'd use Nz() to turn the nulls to zero than you don't need the OR statement.

Sorry, I'm somewhat new to this, what do you mean by Nz()?

 

[dgmorr]

you might want to try

(value=0 AND name <> HOT)
OR
(value= Is Null AND name <>HOT)


Value

0 or Is Null


sportsguy

when I meant "null" i knew about requiring 'is null'.

 

[neileg]

Nz() will return whatever the value of the field is, unless it is null. By default it returns zero for a null field (hence the name of the function) although you can set it to return anything.

So if you add a calculated field to your query, eg NewField:Nz(value) then you will get zeros instead of nulls and zeros where it is zero.