Not at all insulted, Steeler. I just don't see why it is not working
correctly. The Nz function converts a null value to whatever you
identify in
the second argument. For example, if you wanted to replace a Null value
with
"No Value", you could use Nz(SomeFieldOrVariable,"No Value")
I am wondering if perhaps it is because it is a crosstab query. Try
creating a select query and use the Nz function on the Hours field and
see
what you get.
--
Dave Hargis, Microsoft Access MVP
:
I checked the underlying table and Hours is a number (double) and the
lowest
value in there is a 1.
The Nulls are created in my query because there are no values for
certain
Projects. For instance, Engine 730263 has 41 hours for Project LTM0010
but no
entry for Project REWORK so when I run this crosstab query it places a
null
value in REWORK (you're probably wondering why I want a zero instead
of a
null but that's up to the boss).
Please don't be insulted by the long explanation ( I see you are a an
MVP),
it is really to help me understand.
:
Sorry, but that doesn't make sense. Have you checked to be sure it
is
actually a Null value and not an empty string "" ?
--
Dave Hargis, Microsoft Access MVP
:
I'm afraid this still returns null for the Rework columns -not
zeros
:
The Nz is in the wrong place. It should be applied to the
record, not the
sum. Any sum that contains a Null value will sum to Null.
IIf(InStr(1,[Project],"Rework")>0,Sum(Nz([hours],0)),Sum([hours]))
--
Dave Hargis, Microsoft Access MVP
:
I would try
IIf(InStr(1,[Project],"Rework")>0,Nz(Sum([hours]),0),Sum([hours]))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
I want to change the nulls to zero in my Crosstab query only
when the
column
name (Project) has the word "rework" in it.
The following SHOULD work:
Expr1:
IIf(InStr([Project],"Rework"),Nz(Sum([hours]),0),Sum([hours]))
Several people agree with me that this should work but I
must be missing
something because the "rework" columns still have nulls
instead of zeros.
Any help would be GREATLY appreciated.