Kristibaer,
You put that in the criteria of the column? I guess if it ain't broke,
don't fix it....but that is not where I would have put it.
Maybe it will work there just fine, but what I was thinking was creating
a
"calculated field" (of sorts) with that as the expression/calculation.
So, in the grid of the Query Design view, in one of the "cells" in the
"Field:" row, I would have put this...
SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)
....and left the "SumOfphtcnt" field out of the query completely.
But I guess it works either way. I don't know if one way is more
efficient
than the other (making the query run faster), it's hard to say.
By-the-by, in case I wasn't too clear in my first response, Nz() is one
function an Iif() is different fucntion. That is just in case you did
not
know that.
HTH,
Conan
Kristibaer said:
Hi Conan:
Nz([PHYMAINT SUM1].[SumOfphycnt],0) in the criteria of column
SumOfphycnt
worked! I got the Iif from Access page online (and from you a few days
ago
when I needed to change a quantity to a negative if another field was
"O"
for
out). I guess my head is still stuck there.
Thanks again!
Kristi
:
Kristibaer,
The function is Nz(). Get rid of the quotes and the Iif and move the
left
paren. between Nz and [PHYMA...
Like so...
Nz([PHYMAINT SUM1].[SumOfphtcnt],0)
You might also have to give it an alias as well. I think that you
might
be
able to use the name of the field/column inside the function, but
don't
quote me on that.
If doing this in Query Design view, give an alias like this:
SumOfphtcnt: Nz([PHYMAINT SUM1].[SumOfphtcnt],0)
If doing it in SQL view, asign an alias like this:
Nz([PHYMAINT SUM1].[SumOfphtcnt],0) as SumOfphtcnt
If it doesn't like you using an existing name, change "SumOfphtcnt"
slightly...to something you can live with.
HTH,
Conan
Hi Dave:
Here is the expression I used in the criteria for the field I want
null
values to be zero:
IIf("Nz[PHYMAINT SUM1].[SumOfphtcnt]",0)
This query has 2 tables. This table is called PHYMAINT SUM1 and the
field
is (from a previous query) SumOfphycnt. When I use this expression,
all
values return as zero where before some had actual numbers. Where
did I
take
the wrong turn? I am trying to keep this in the original column of
SumOfphycnt instead of having to create an additional column.
Thanks,
Kristi
:
Use a calculated field and the Nz function. The Nz function will
return
the
value of a control, variable, or field if it is not null, and the
value
of
the second argument if it is.
Xqty: Nz([qty], 0)
--
Dave Hargis, Microsoft Access MVP
:
I have a query with approximately 20,000 records. 3 of the
fields
(qty) only
have about 3,700 records and the rest are blank. Is there a
criteria
expression that will populate the blank records of these fields
with
0
(zero)
within the same column that states if the field is null replace
it
with
a
zero?
Thank you,
Kristibaer