Replacing Nulls with zeros

[Guest]

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.

 

[John Spencer]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

I believe it is because it is a crosstab query:

If I run a select query there will be no projects that have a null ( so I
can't really test it that way) because there is a value for every every
Project in the underlying table. It's only when I create a crosstab that the
nulls are created.

If I go to the underlying table and delete some values so there are some
existing nulls for Project REWORK your SQL will handle them correctly and put
in a 0.

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.

 

[Gary Walter]

try

Sum(IIF(InStr([Project],'REWORK')>0,NZ([Hours],0),[Hours]))

I believe it is because it is a crosstab query:

If I run a select query there will be no projects that have a null ( so I
can't really test it that way) because there is a value for every every
Project in the underlying table. It's only when I create a crosstab that
the
nulls are created.

If I go to the underlying table and delete some values so there are some
existing nulls for Project REWORK your SQL will handle them correctly and
put
in a 0.

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.

 

[Gary Walter]

sorry, make that

Sum(IIF(InStr([Project],'REWORK')=0, [Hours], NZ([Hours],0)))

try

Sum(IIF(InStr([Project],'REWORK')>0,NZ([Hours],0),[Hours]))

I believe it is because it is a crosstab query:

If I run a select query there will be no projects that have a null ( so I
can't really test it that way) because there is a value for every every
Project in the underlying table. It's only when I create a crosstab that
the
nulls are created.

If I go to the underlying table and delete some values so there are some
existing nulls for Project REWORK your SQL will handle them correctly and
put
in a 0.

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.

 

[Guest]

Sorry. Same result.

try

Sum(IIF(InStr([Project],'REWORK')>0,NZ([Hours],0),[Hours]))

I believe it is because it is a crosstab query:

If I run a select query there will be no projects that have a null ( so I
can't really test it that way) because there is a value for every every
Project in the underlying table. It's only when I create a crosstab that
the
nulls are created.

If I go to the underlying table and delete some values so there are some
existing nulls for Project REWORK your SQL will handle them correctly and
put
in a 0.

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.