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
|
|||
|
|||
IIF Problem
i have a query that calculate total sold, total purchased and stock status ,
i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help |
#2
|
|||
|
|||
IIF Problem
aren't you forgetting the second argument for the iif-statement. Now you do
have an argument when it is true but what if it is false? IIf([InStock][ReorderLevel],"OrderNow", "or else?" )) hth -- Maurice Ausum "rita" wrote: i have a query that calculate total sold, total purchased and stock status , i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help |
#3
|
|||
|
|||
IIF Problem
Try changing it to this ---
WHERE [InStock][ReorderLevel]; -- KARL DEWEY Build a little - Test a little "rita" wrote: i have a query that calculate total sold, total purchased and stock status , i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help |
#4
|
|||
|
|||
IIF Problem
"rita" wrote in message
... i have a query that calculate total sold, total purchased and stock status , i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help The IIf is lacking "value if false". Try something like: IIf([InStock][ReorderLevel],"OrderNow","OK") |
#5
|
|||
|
|||
IIF Problem
no, there is no a second argument , its null but the problem with the
criteria Ordernow when its written , returns a enter parameter box and not show the result. is there something wrong thanks, "Maurice" wrote: aren't you forgetting the second argument for the iif-statement. Now you do have an argument when it is true but what if it is false? IIf([InStock][ReorderLevel],"OrderNow", "or else?" )) hth -- Maurice Ausum "rita" wrote: i have a query that calculate total sold, total purchased and stock status , i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help |
#6
|
|||
|
|||
IIF Problem
Karl's suggestion sounds good, and simpler too. The point Maurice was
making is that IIf *requires* the second argument. If you want it to be blank, use an empty string: ("") instead of "or else?". "rita" wrote in message ... no, there is no a second argument , its null but the problem with the criteria Ordernow when its written , returns a enter parameter box and not show the result. is there something wrong thanks, "Maurice" wrote: aren't you forgetting the second argument for the iif-statement. Now you do have an argument when it is true but what if it is false? IIf([InStock][ReorderLevel],"OrderNow", "or else?" )) hth -- Maurice Ausum "rita" wrote: i have a query that calculate total sold, total purchased and stock status , i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help |
#7
|
|||
|
|||
IIF Problem
yes , i made this step but there is no changes its written ok beside the
remaining items but i want the query to only dir OrderNow items . thanks "BruceM" wrote: "rita" wrote in message ... i have a query that calculate total sold, total purchased and stock status , i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help The IIf is lacking "value if false". Try something like: IIf([InStock][ReorderLevel],"OrderNow","OK") |
#8
|
|||
|
|||
IIF Problem
u understood me , i written ur suggestion but the same issue, enter
parameter box , and the same problem their is no result "KARL DEWEY" wrote: Try changing it to this --- WHERE [InStock][ReorderLevel]; -- KARL DEWEY Build a little - Test a little "rita" wrote: i have a query that calculate total sold, total purchased and stock status , i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help |
#9
|
|||
|
|||
IIF Problem
Try doing your subquery calculations in separate queries then join in final
query. -- KARL DEWEY Build a little - Test a little "rita" wrote: u understood me , i written ur suggestion but the same issue, enter parameter box , and the same problem their is no result "KARL DEWEY" wrote: Try changing it to this --- WHERE [InStock][ReorderLevel]; -- KARL DEWEY Build a little - Test a little "rita" wrote: i have a query that calculate total sold, total purchased and stock status , i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help |
#10
|
|||
|
|||
IIF Problem
Ok, lets take another look at it. Looking at the where clause you've added
the second argument. But how about the following: ----- IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); ---- In the first part of the iif you are also refering to one argument. Besides that you are defining a column [Status] and in the whereclause of your statement you are not refering to a criteria field. My guess is that it should be status="Ordernow" hth -- Maurice Ausum "rita" wrote: yes , i made this step but there is no changes its written ok beside the remaining items but i want the query to only dir OrderNow items . thanks "BruceM" wrote: "rita" wrote in message ... i have a query that calculate total sold, total purchased and stock status , i want to make this query show only items that less than the reorder level i written this statment SELECT Products.ProductID, Products.[Product name], Products.Unit, (select sum(UnitsSold) from OrderDetails where ProductID = Products.ProductID) AS TotalSoldStock, (select sum(UnitsPurchased) from SuppliesDetials where ProductID = Products.ProductID) AS TotalPurchasedStock, ([TotalPurchasedSTock]-[TotalSoldStock]) AS InStock, Products.[last Purchasing price], Products.ReorderLevel, IIf([InStock][ReorderLevel],"OrderNow") AS status FROM Products WHERE (((IIf([InStock][ReorderLevel],"OrderNow"))="OrderNow")); but its returns enter prameter box and not showing any result plzzzzzz help The IIf is lacking "value if false". Try something like: IIf([InStock][ReorderLevel],"OrderNow","OK") |
|
Thread Tools | |
Display Modes | |
|
|