count control

  • Thread starter Thread starter Dennis Hansen
  • Start date Start date
D

Dennis Hansen

I have a report that counts items for an inventory. I need
all the items to show on the report even if a zero balance
exists. My report does not show the item if zero exists
I use a text control with =count(*) in the detail section,
How can I force the zero balance to show, my fields are
text data types.

Thanks in advance
Dennis Hansen
 
Dennis

Are you basing your report on a table directly, or on a query? I'd probably
look to using a query to ensure that I had every category, including those
with zero balance. ?Perhaps using a cross-tabs query?
 
I tried to base my report off of a cross-tabs query, still
no luck.!
Hope this helps narrow this down, I am very frustrated at
this point
thank you for your response.
 
Does your cross-tab query generate the "0"s you are looking for?

Consider posting the SQL expression you use for your query...
 
No I'm not getting my '0'
TRANSFORM Count(qerytestinventoryrubberstorage.ItemID) AS
[The Value]
SELECT qerytestinventoryrubberstorage.DESC, Count
(qerytestinventoryrubberstorage.ItemID) AS [Total Of
ItemID]
FROM qerytestinventoryrubberstorage
GROUP BY qerytestinventoryrubberstorage.DESC
PIVOT qerytestinventoryrubberstorage.BINLOC;
Thank you for your time
Dennis
a learning exp.
 
So, if the [Total Of ItemID] is null, it doesn't show "0", right? Have you
tried using the Nz() function to convert a null to a zero?
 
That is correct. it doesn't show "0"
I'm not totaly clear on using the function NZ, or where to
put it.????
I tried a variation NZ, but Idon't think I did it
correctly.
Thanks again for your help.
Dennis
 
PMFJI try:
TRANSFORM Val(Nz(Count(qerytestinventoryrubberstorage.ItemID),0)) AS
[The Value]
SELECT qerytestinventoryrubberstorage.DESC,
Count(qerytestinventoryrubberstorage.ItemID)
AS [Total Of ItemID]
FROM qerytestinventoryrubberstorage
GROUP BY qerytestinventoryrubberstorage.DESC
PIVOT qerytestinventoryrubberstorage.BINLOC;

However, this will not show a [DESC] for items that are not in
qerytestinventoryrubberstorage. It should put a zero in BINLOC columns where
there are none of the particular item.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


Dennis said:
That is correct. it doesn't show "0"
I'm not totaly clear on using the function NZ, or where to
put it.????
I tried a variation NZ, but Idon't think I did it
correctly.
Thanks again for your help.
Dennis
-----Original Message-----
So, if the [Total Of ItemID] is null, it doesn't show "0", right? Have you
tried using the Nz() function to convert a null to a zero?

--
Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...
 
I tried your sql and it did as you said, I see the zeros,
but I need to see the [desc]
I'm just counting inventory and I need to see that the
inventory says (0)
How do I make my query show the missing [desc]
if it has zero items in it.
I think I'm close, but I just can't quite get it.
Thanks again for you time.
I really need this, so your help is appreciated.
-----Original Message-----
PMFJI try:
TRANSFORM Val(Nz(Count
Click to expand...
(qerytestinventoryrubberstorage.ItemID),0)) AS
[The Value]
SELECT qerytestinventoryrubberstorage.DESC,
Count(qerytestinventoryrubberstorage.ItemID)
AS [Total Of ItemID]
FROM qerytestinventoryrubberstorage
GROUP BY qerytestinventoryrubberstorage.DESC
PIVOT qerytestinventoryrubberstorage.BINLOC;

However, this will not show a [DESC] for items that are not in
qerytestinventoryrubberstorage. It should put a zero in BINLOC columns where
there are none of the particular item.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


That is correct. it doesn't show "0"
I'm not totaly clear on using the function NZ, or where to
put it.????
I tried a variation NZ, but Idon't think I did it
correctly.
Thanks again for your help.
Dennis
-----Original Message-----
So, if the [Total Of ItemID] is null, it doesn't show "0", right? Have you
tried using the Nz() function to convert a null to a zero?

--
Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...


.
Click to expand...
 
If a DESC value doesn't display in qerytestinventoryrubberstorage, then it
won't show in the crosstab unless you join it with a left or right join to a
query or table with all DESC values.

--
Duane Hookom
MS Access MVP


Dennis said:
I tried your sql and it did as you said, I see the zeros,
but I need to see the [desc]
I'm just counting inventory and I need to see that the
inventory says (0)
How do I make my query show the missing [desc]
if it has zero items in it.
I think I'm close, but I just can't quite get it.
Thanks again for you time.
I really need this, so your help is appreciated.
-----Original Message-----
PMFJI try:
TRANSFORM Val(Nz(Count
Click to expand...
(qerytestinventoryrubberstorage.ItemID),0)) AS
[The Value]
SELECT qerytestinventoryrubberstorage.DESC,
Count(qerytestinventoryrubberstorage.ItemID)
AS [Total Of ItemID]
FROM qerytestinventoryrubberstorage
GROUP BY qerytestinventoryrubberstorage.DESC
PIVOT qerytestinventoryrubberstorage.BINLOC;

However, this will not show a [DESC] for items that are not in
qerytestinventoryrubberstorage. It should put a zero in BINLOC columns where
there are none of the particular item.

--
Duane Hookom
Microsoft Access MVP
Please direct any questions to News Groups


That is correct. it doesn't show "0"
I'm not totaly clear on using the function NZ, or where to
put it.????
I tried a variation NZ, but Idon't think I did it
correctly.
Thanks again for your help.
Dennis
-----Original Message-----
So, if the [Total Of ItemID] is null, it doesn't
show "0", right? Have you
tried using the Nz() function to convert a null to a zero?

--
Good luck

Jeff Boyce
<Access MVP>

.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top