A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Hiding zero values in report



 
 
Thread Tools Display Modes
  #1  
Old November 17th, 2009, 12:01 PM posted to microsoft.public.access.tablesdbdesign
Coco111 via AccessMonster.com
external usenet poster
 
Posts: 32
Default Hiding zero values in report

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/For...esign/200911/1

  #2  
Old November 17th, 2009, 02:01 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Hiding zero values in report

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 wrote:
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/For...esign/200911/1

  #3  
Old November 18th, 2009, 02:38 AM posted to microsoft.public.access.tablesdbdesign
Coco111 via AccessMonster.com
external usenet poster
 
Posts: 32
Default Hiding zero values in report

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

  #4  
Old November 18th, 2009, 01:08 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Hiding zero values in report

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/For...esign/200911/1

  #5  
Old November 18th, 2009, 01:59 PM posted to microsoft.public.access.tablesdbdesign
Coco111 via AccessMonster.com
external usenet poster
 
Posts: 32
Default Hiding zero values in report

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]
Message show Qty Purchase? and also Qty Sold? How? Please help?
Thks in advance...


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

  #6  
Old November 18th, 2009, 05:21 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Hiding zero values in report

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]
Message show Qty Purchase? and also Qty Sold? How? Please help?
Thks in advance...


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

  #7  
Old November 19th, 2009, 04:13 PM posted to microsoft.public.access.tablesdbdesign
Coco111 via AccessMonster.com
external usenet poster
 
Posts: 32
Default Hiding zero values in report

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]
Message show Qty Purchase? and also Qty Sold? How? Please help?
Thks in advance...


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200911/1

  #8  
Old November 19th, 2009, 06:53 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Hiding zero values in report

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.


Coco111 wrote:
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));

????

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

[quoted text clipped - 11 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/For...esign/200911/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.