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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|