Query not returning null values

[janelgirl]

2007 version - my child query is not returning null values. I have 2 fields, both pulled from a parent query. One is "DaysSinceRecd", the other is "AmountPaying". The parent query returns multiple results, which is fine. For example, the parent query might have 10 different records for "0" "DaysSinceRecd", with different "AmountPaying" for each of those records. In the child query, I am trying to condense that down, so that I only have one "AmountPaying" for each "DaysSinceRecd". It's working fabulously, except that there are records in the parent query that have a value for "AmountPaying" but a null value for "DaysSinceRecd". The null value for "DaysSinceRecd" cannot be changed to another value, it needs to stay null. My child query is not returning the null records. I tried adding to the criteria: IsNull Or IsNotNull, >=0 or <=0. That's not working. I'm a novice, so speak slowly, please . TIA for any help that you can provide.

[NoellaG]

Hi,

or you can use the Is Null criteria in an"Or" statement, or you can use the Nz function.

syntax: Nz(variable,value if null), for instance Nz([MyField],0) will return 0 everywhere the field [MyField] is empty.

greetings
NG

[janelgirl]

I tried the "Or" statement, no luck there. Is there a way to use the Nz function that would return the null fields as null? I need it to stay null if possible. Thanks!

[NoellaG]

Hi,

the point of the Nz function is to eliminate empty values.
With the "OR" statement I meant, if you have other criteria you can combine them with an or criteria as:

select fieldA, fieldB, fieldC from tblMyTable where fieldC > 0 OR fieldC Is Null

greetings
NG

[janelgirl]

That isn't working either. The query still does not return the null values. Any other ideas?

TIA!

[NoellaG]

Hi,

not without seeing the query. Can you post the SQL of both queries, or better yet post an example access file with some data and both queries?

gr
NG