NZ function

[Dan]

I not sure how to use the NZ functions for my calulations. I have a query
that counts the number of records for a certain period of time. For some
periods there may not be any records inwhich I get a blank sceen with just
the column headings [Count of Apples]. However, when I do a simlar query on
an item that does have records for that period [Count of Pears] I get the
number item (Pears) sold. Therefore, when I go to add the results of theses
two queries [Count of Apples] + [Count of Pears] I also get a blank screen
with just the column headings. How do I get these queries to reconize if
there are no records for that period for apples for it to return me a result
of zero that can be counted with other queries (pears = 2)instead of a blank
screen? I have tried to use the NZ() fuction in the criteria of my column
for apples that is doing the counting, but can not get it to work.

 

[BruceM]

It would have been helpful to see the expression in which you tried to use
Nz. Did you supply a "value if null"? You may need something like:
Nz([Count of Apples],0) + Nz([Count of Pears],0)
If there are no apples a 0 is used instead. If there are 2 pears, 0 + 2 =
2; otherwise the calculation is Null + 2, which will return Null.
This assumes the calculation works properly if there are both apples and
pears, but fails if one or the other returns no records.

 

[Dan]

I don't have any expression. What I did was create an individual query for
each item to be counted. I'm not sure how to ue the NZ fuction in order to
get a zero when I run the query for apples sold and there were no apples
sold. Is it used in the criteria section? Or do I need to write an
expression for each item to be counted. Basically I have a query that counts
the number of item sold (apples) for a particular day. In the first column
of my query I have as my field [type of fruit] from my [Day Query] and for
the total I have Group By with a criteria of "apple". My next column I then
count the number of records return in my field I have [type of fruit], Table
[Day Query] and total as count. Im just trying to get this query to return a
zero if there are no records for apple. Do I put a NZ function in the
Criteria of my column that is doing the counting and if so how do I do this.

It would have been helpful to see the expression in which you tried to use
Nz. Did you supply a "value if null"? You may need something like:
Nz([Count of Apples],0) + Nz([Count of Pears],0)
If there are no apples a 0 is used instead. If there are 2 pears, 0 + 2 =
2; otherwise the calculation is Null + 2, which will return Null.
This assumes the calculation works properly if there are both apples and
pears, but fails if one or the other returns no records.

I not sure how to use the NZ functions for my calulations. I have a query
that counts the number of records for a certain period of time. For some
periods there may not be any records inwhich I get a blank sceen with just
the column headings [Count of Apples]. However, when I do a simlar query
on
an item that does have records for that period [Count of Pears] I get the
number item (Pears) sold. Therefore, when I go to add the results of
theses
two queries [Count of Apples] + [Count of Pears] I also get a blank screen
with just the column headings. How do I get these queries to reconize if
there are no records for that period for apples for it to return me a
result
of zero that can be counted with other queries (pears = 2)instead of a
blank
screen? I have tried to use the NZ() fuction in the criteria of my column
for apples that is doing the counting, but can not get it to work.

 

[BruceM]

I don't understand how you could have used Nz without putting it into an
expression, but maybe we are not using the terminology in the same way. In
any case, a separate query for each item is not a good idea. I can't tell
how your data are organized, but I assume that somewhere is a table with a
FruitType column. Make a query from that table. In the criteria row put
something like [Type of fruit]. When you run the query you will be prompted
for the type of fruit. This may help you see how parameters (criteria)
work.
Please describe the table in which these data appear. You mention Day
Query, but I have no way of knowing what that is. On what table or tables
is it based? What is the purpose of the database (other than counting)?

I don't have any expression. What I did was create an individual query for
each item to be counted. I'm not sure how to ue the NZ fuction in order
to
get a zero when I run the query for apples sold and there were no apples
sold. Is it used in the criteria section? Or do I need to write an
expression for each item to be counted. Basically I have a query that
counts
the number of item sold (apples) for a particular day. In the first
column
of my query I have as my field [type of fruit] from my [Day Query] and for
the total I have Group By with a criteria of "apple". My next column I
then
count the number of records return in my field I have [type of fruit],
Table
[Day Query] and total as count. Im just trying to get this query to
return a
zero if there are no records for apple. Do I put a NZ function in the
Criteria of my column that is doing the counting and if so how do I do
this.

It would have been helpful to see the expression in which you tried to
use
Nz. Did you supply a "value if null"? You may need something like:
Nz([Count of Apples],0) + Nz([Count of Pears],0)
If there are no apples a 0 is used instead. If there are 2 pears, 0 + 2
=
2; otherwise the calculation is Null + 2, which will return Null.
This assumes the calculation works properly if there are both apples and
pears, but fails if one or the other returns no records.

I not sure how to use the NZ functions for my calulations. I have a
query
that counts the number of records for a certain period of time. For
some
periods there may not be any records inwhich I get a blank sceen with
just
the column headings [Count of Apples]. However, when I do a simlar
query
on
an item that does have records for that period [Count of Pears] I get
the
number item (Pears) sold. Therefore, when I go to add the results of
theses
two queries [Count of Apples] + [Count of Pears] I also get a blank
screen
with just the column headings. How do I get these queries to reconize
if
there are no records for that period for apples for it to return me a
result
of zero that can be counted with other queries (pears = 2)instead of a
blank
screen? I have tried to use the NZ() fuction in the criteria of my
column
for apples that is doing the counting, but can not get it to work.

 

[Dan]

I have a table Sales with Type of Fruit as a column. I then made a query
that just pull the sales from last week from my Sales table. Becasue I did
not know of any other way of doing it I then created a separate query for
each item that counted the number of each item sold. I have a separate query
to count apples, pears, oranges, etc. I then made another query that pulls
all the information on one sheet. This query qives me a column for Count of
Apples, Count of Pears, Count of Oranges, etc.

I don't understand how you could have used Nz without putting it into an
expression, but maybe we are not using the terminology in the same way. In
any case, a separate query for each item is not a good idea. I can't tell
how your data are organized, but I assume that somewhere is a table with a
FruitType column. Make a query from that table. In the criteria row put
something like [Type of fruit]. When you run the query you will be prompted
for the type of fruit. This may help you see how parameters (criteria)
work.
Please describe the table in which these data appear. You mention Day
Query, but I have no way of knowing what that is. On what table or tables
is it based? What is the purpose of the database (other than counting)?

I don't have any expression. What I did was create an individual query for
each item to be counted. I'm not sure how to ue the NZ fuction in order
to
get a zero when I run the query for apples sold and there were no apples
sold. Is it used in the criteria section? Or do I need to write an
expression for each item to be counted. Basically I have a query that
counts
the number of item sold (apples) for a particular day. In the first
column
of my query I have as my field [type of fruit] from my [Day Query] and for
the total I have Group By with a criteria of "apple". My next column I
then
count the number of records return in my field I have [type of fruit],
Table
[Day Query] and total as count. Im just trying to get this query to
return a
zero if there are no records for apple. Do I put a NZ function in the
Criteria of my column that is doing the counting and if so how do I do
this.

It would have been helpful to see the expression in which you tried to
use
Nz. Did you supply a "value if null"? You may need something like:
Nz([Count of Apples],0) + Nz([Count of Pears],0)
If there are no apples a 0 is used instead. If there are 2 pears, 0 + 2
=
2; otherwise the calculation is Null + 2, which will return Null.
This assumes the calculation works properly if there are both apples and
pears, but fails if one or the other returns no records.

I not sure how to use the NZ functions for my calulations. I have a
query
that counts the number of records for a certain period of time. For
some
periods there may not be any records inwhich I get a blank sceen with
just
the column headings [Count of Apples]. However, when I do a simlar
query
on
an item that does have records for that period [Count of Pears] I get
the
number item (Pears) sold. Therefore, when I go to add the results of
theses
two queries [Count of Apples] + [Count of Pears] I also get a blank
screen
with just the column headings. How do I get these queries to reconize
if
there are no records for that period for apples for it to return me a
result
of zero that can be counted with other queries (pears = 2)instead of a
blank
screen? I have tried to use the NZ() fuction in the criteria of my
column
for apples that is doing the counting, but can not get it to work.

 

[George Nicholson]

1) You don't want to use NZ, but if you did the proper way would be as
follows:

In your 2nd column (the one with Total set to Count), replace [type of
fruit] in the 'Field' row with:
AppleCount: nz([Type of Fruit],0)

However, since DayQuery has no 'apple' records, you still won't get any
records returned no matter what you do (using just Day query).
NZ won't create a record where none exists.
(And adding a dummy record to DayQuery would give you an AppleCount of 1,
which is no good).
Off the top of my head, I can't think of any time where using NZ within
Count would give you different results than just Count by itself.
NZ is usually used with Sum or other functions where Null values *within* a
record (not the non-existance of records) can cause undesired results. Count
is not one of those functions.


2) So, you need to ditch NZ.
Try this in your 2nd column (replacing [type of Fruit] in the 'Field' row):
AppleCount: DCount("[Type of Fruit]","[Day query]","[type of Fruit] =
'apple'")
Set Total back to the default GroupBy.
(Your first column now serves no purpose, you can delete it)


3) Another approach, if you could work with a result set like this:

FruitType FruitCount
Apple 0
Pear 24

(Assuming tblFruitTypes is a table (or query) containing a single entry for
all possible fruits (*including Apple*), with a field named: FruitType)
Add tblFruitTypes to a query, along with Day Query.

Paste this into View>SQL:

SELECT tblFruitTypes.FruitType, Count([Day Query].[type of Fruit]) AS
FruitCount
FROM tblFruitTypes LEFT JOIN [Day Query] ON tblFruitTypes.FruitType =([Day
Query].[type of Fruit]
GROUP BY tblFruitTypes.FruitType;

This query does a Left Outer Join. That means it returns All records from
tblFruitTypes plus any matching records from Day Query. This guarantees that
the result set
has at least ONE record for each entry in tblFruitType, along with the Count
of that type from DayQuery. If there are no matching records for that
FruitType in Day Query, FruitCount will equal zero.


HTH,

I don't have any expression. What I did was create an individual query for
each item to be counted. I'm not sure how to ue the NZ fuction in order
to
get a zero when I run the query for apples sold and there were no apples
sold. Is it used in the criteria section? Or do I need to write an
expression for each item to be counted. Basically I have a query that
counts
the number of item sold (apples) for a particular day. In the first
column
of my query I have as my field [type of fruit] from my [Day Query] and for
the total I have Group By with a criteria of "apple". My next column I
then
count the number of records return in my field I have [type of fruit],
Table
[Day Query] and total as count. Im just trying to get this query to
return a
zero if there are no records for apple. Do I put a NZ function in the
Criteria of my column that is doing the counting and if so how do I do
this.

It would have been helpful to see the expression in which you tried to
use
Nz. Did you supply a "value if null"? You may need something like:
Nz([Count of Apples],0) + Nz([Count of Pears],0)
If there are no apples a 0 is used instead. If there are 2 pears, 0 + 2
=
2; otherwise the calculation is Null + 2, which will return Null.
This assumes the calculation works properly if there are both apples and
pears, but fails if one or the other returns no records.

I not sure how to use the NZ functions for my calulations. I have a
query
that counts the number of records for a certain period of time. For
some
periods there may not be any records inwhich I get a blank sceen with
just
the column headings [Count of Apples]. However, when I do a simlar
query
on
an item that does have records for that period [Count of Pears] I get
the
number item (Pears) sold. Therefore, when I go to add the results of
theses
two queries [Count of Apples] + [Count of Pears] I also get a blank
screen
with just the column headings. How do I get these queries to reconize
if
there are no records for that period for apples for it to return me a
result
of zero that can be counted with other queries (pears = 2)instead of a
blank
screen? I have tried to use the NZ() fuction in the criteria of my
column
for apples that is doing the counting, but can not get it to work.

 

[BruceM]

You can use the same query for both. Just enter the parameter as I
suggested. You may be able to automate the part that looks at last week's
date, depending on what exactly you need to do.
If all you need to do is display or print the numbers you can make a report,
and use its Sorting and Grouping feature. Group on the FruitType field.
There are various ways of summing the data.

I have a table Sales with Type of Fruit as a column. I then made a query
that just pull the sales from last week from my Sales table. Becasue I
did
not know of any other way of doing it I then created a separate query for
each item that counted the number of each item sold. I have a separate
query
to count apples, pears, oranges, etc. I then made another query that
pulls
all the information on one sheet. This query qives me a column for Count
of
Apples, Count of Pears, Count of Oranges, etc.

I don't understand how you could have used Nz without putting it into an
expression, but maybe we are not using the terminology in the same way.
In
any case, a separate query for each item is not a good idea. I can't
tell
how your data are organized, but I assume that somewhere is a table with
a
FruitType column. Make a query from that table. In the criteria row put
something like [Type of fruit]. When you run the query you will be
prompted
for the type of fruit. This may help you see how parameters (criteria)
work.
Please describe the table in which these data appear. You mention Day
Query, but I have no way of knowing what that is. On what table or
tables
is it based? What is the purpose of the database (other than counting)?

I don't have any expression. What I did was create an individual query
for
each item to be counted. I'm not sure how to ue the NZ fuction in
order
to
get a zero when I run the query for apples sold and there were no
apples
sold. Is it used in the criteria section? Or do I need to write an
expression for each item to be counted. Basically I have a query that
counts
the number of item sold (apples) for a particular day. In the first
column
of my query I have as my field [type of fruit] from my [Day Query] and
for
the total I have Group By with a criteria of "apple". My next column I
then
count the number of records return in my field I have [type of fruit],
Table
[Day Query] and total as count. Im just trying to get this query to
return a
zero if there are no records for apple. Do I put a NZ function in the
Criteria of my column that is doing the counting and if so how do I do
this.

:

It would have been helpful to see the expression in which you tried to
use
Nz. Did you supply a "value if null"? You may need something like:
Nz([Count of Apples],0) + Nz([Count of Pears],0)
If there are no apples a 0 is used instead. If there are 2 pears, 0 +
2
=
2; otherwise the calculation is Null + 2, which will return Null.
This assumes the calculation works properly if there are both apples
and
pears, but fails if one or the other returns no records.

I not sure how to use the NZ functions for my calulations. I have a
query
that counts the number of records for a certain period of time. For
some
periods there may not be any records inwhich I get a blank sceen
with
just
the column headings [Count of Apples]. However, when I do a simlar
query
on
an item that does have records for that period [Count of Pears] I
get
the
number item (Pears) sold. Therefore, when I go to add the results
of
theses
two queries [Count of Apples] + [Count of Pears] I also get a blank
screen
with just the column headings. How do I get these queries to
reconize
if
there are no records for that period for apples for it to return me
a
result
of zero that can be counted with other queries (pears = 2)instead of
a
blank
screen? I have tried to use the NZ() fuction in the criteria of my
column
for apples that is doing the counting, but can not get it to work.