Hiding zero values in report

[Coco111 via AccessMonster.com]

Hi,

I got some problem, I check a lot of past thread and can find similar
question but not exactly the problem that I face. Anybody can tell me how to
hide zero values in report. My report comes from Queries. Qty On Hand: [Qty
Purchased]-[Q'ty Sold], I put criteria in queries >0 but, it did not work.
Message show Qty Purchase? and also Qty Sold? How? Please help?
Thks in advance...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1

[BruceM via AccessMonster.com]

Please post the SQL. Open the query in design view, then click View >> SQL.

It could be that you are specifying >0 for Null values. Null is not the same
as 0. You could start by using >0 in one criteria line of the query, and in
the "Or" line directly below it, Is Not Null.

[Coco111 via AccessMonster.com]

I try it as you told me but still not work.

In criteria I put >0 and below line Or, I put, Is Not Null
F.Y.I, i use northwind template and i did not change anything.

How can I solve this problem?

BruceM wrote:
>Please post the SQL. Open the query in design view, then click View >> SQL.
>
>It could be that you are specifying >0 for Null values. Null is not the same
>as 0. You could start by using >0 in one criteria line of the query, and in
>the "Or" line directly below it, Is Not Null.
>

>>Hi,
>>
>[quoted text clipped - 4 lines]

>>Message show Qty Purchase? and also Qty Sold? How? Please help?
>>Thks in advance...

--
Message posted via http://www.accessmonster.com

[BruceM via AccessMonster.com]

Please post the SQL as described in my previous posting.

Coco111 wrote:
>I try it as you told me but still not work.
>
>In criteria I put >0 and below line Or, I put, Is Not Null
>F.Y.I, i use northwind template and i did not change anything.
>
>How can I solve this problem?
>

>>Please post the SQL. Open the query in design view, then click View >> SQL.
>>

>[quoted text clipped - 7 lines]

>>>Message show Qty Purchase? and also Qty Sold? How? Please help?
>>>Thks in advance...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200911/1

[Coco111 via AccessMonster.com]

Sorry I dont get it....in SQL see as below:-

SELECT Products.ID AS [Product ID], Products.[Product Name], Products.
[Product Code], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz([Quantity
Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold], [Qty
Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty On
Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz(
[Quantity On Back Order],0) AS [Qty On Back Order], Products.[Reorder Level],
Products.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target
Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level],
IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder
Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To
Reorder]
FROM ((((Products LEFT JOIN [Inventory Sold] ON Products.ID = [Inventory Sold]
.[Product ID]) LEFT JOIN [Inventory Purchased] ON Products.ID = [Inventory
Purchased].[Product ID]) LEFT JOIN [Inventory On Hold] ON Products.ID =
[Inventory On Hold].[Product ID]) LEFT JOIN [Inventory On Order] ON Products.
ID = [Inventory On Order].[Product ID]) LEFT JOIN [Products On Back Order] ON
Products.ID = [Products On Back Order].[Product ID];

How? Sorry, if im just beginner...

BruceM wrote:
>Please post the SQL as described in my previous posting.
>

>>I try it as you told me but still not work.
>>

>[quoted text clipped - 8 lines]

[BruceM via AccessMonster.com]

It doesn't look like anything in my copy of Northwind (Access 2003).

I don't see where you specified a criteria. You need something like this at
the end:

WHERE ([Quantity Purchased]-[Quantity Sold]) > 0

Was this query showing the correct data (other than showing 0 where you
didn't want it)?

Coco111 wrote:
>Sorry I dont get it....in SQL see as below:-
>
>SELECT Products.ID AS [Product ID], Products.[Product Name], Products.
>[Product Code], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz([Quantity
>Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold], [Qty
>Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty On
>Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz(
>[Quantity On Back Order],0) AS [Qty On Back Order], Products.[Reorder Level],
>Products.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target
>Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level],
>IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder
>Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To
>Reorder]
>FROM ((((Products LEFT JOIN [Inventory Sold] ON Products.ID = [Inventory Sold]
>.[Product ID]) LEFT JOIN [Inventory Purchased] ON Products.ID = [Inventory
>Purchased].[Product ID]) LEFT JOIN [Inventory On Hold] ON Products.ID =
>[Inventory On Hold].[Product ID]) LEFT JOIN [Inventory On Order] ON Products.
>ID = [Inventory On Order].[Product ID]) LEFT JOIN [Products On Back Order] ON
>Products.ID = [Products On Back Order].[Product ID];
>
>How? Sorry, if im just beginner...
>

>>Please post the SQL as described in my previous posting.
>>

>[quoted text clipped - 3 lines]

[Coco111 via AccessMonster.com]

It northwind 2007 not 2003

even I specified a criteria as you told...it shows as below but still come
the same problem...

WHERE ((([Qty Purchased]-[Qty Sold])>0));

I try it in other field and it work as below
Qty Purchased: Nz([Quantity Purchased],0) , criteria >0
SQL show below:-

SELECT Products.ID AS [Product ID], Products.[Product Name], Products.
[Product Code], Nz([Quantity Purchased],0) AS [Qty Purchased], Nz([Quantity
Sold],0) AS [Qty Sold], Nz([Quantity On Hold],0) AS [Qty On Hold], [Qty
Purchased]-[Qty Sold] AS [Qty On Hand], [Qty Purchased]-[Qty Sold]-[Qty On
Hold] AS [Qty Available], Nz([Quantity On Order],0) AS [Qty On Order], Nz(
[Quantity On Back Order],0) AS [Qty On Back Order], Products.[Reorder Level],
Products.[Target Level], [Target Level]-[Current Level] AS [Qty Below Target
Level], [Qty Available]+[Qty On Order]-[Qty On Back Order] AS [Current Level],
IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder
Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0) AS [Qty To
Reorder]
FROM ((((Products LEFT JOIN [Inventory Sold] ON Products.ID = [Inventory Sold]
.[Product ID]) LEFT JOIN [Inventory Purchased] ON Products.ID = [Inventory
Purchased].[Product ID]) LEFT JOIN [Inventory On Hold] ON Products.ID =
[Inventory On Hold].[Product ID]) LEFT JOIN [Inventory On Order] ON Products.
ID = [Inventory On Order].[Product ID]) LEFT JOIN [Products On Back Order] ON
Products.ID = [Products On Back Order].[Product ID]

WHERE (((Nz([Quantity Purchased],0))>0));

????

BruceM wrote:
>It doesn't look like anything in my copy of Northwind (Access 2003).
>
>I don't see where you specified a criteria. You need something like this at
>the end:
>
>WHERE ([Quantity Purchased]-[Quantity Sold]) > 0
>
>Was this query showing the correct data (other than showing 0 where you
>didn't want it)?
>

>>Sorry I dont get it....in SQL see as below:-
>>

>[quoted text clipped - 23 lines]

[BruceM via AccessMonster.com]

When you see something with "AS" it means there is an alias for the field:

Nz([Quantity Purchased],0) AS [Qty Purchased]

[Qty Purchased] is the field name. [Qty Purchased] is the alias. For the
WHERE condition, try using the field name, as I suggested. I suggested you
try this:

WHERE ([Quantity Purchased]-[Quantity Sold]) > 0

but you did this:

WHERE ((([Qty Purchased]-[Qty Sold])>0))

Don't worry about the parentheses. Access throws those in there for reasons
that are not always clear. My point is that you are using the field name
aliases, but I think you need to use the actual field names.

>>It doesn't look like anything in my copy of Northwind (Access 2003).
>>

>[quoted text clipped - 11 lines]