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 |
#11
|
|||
|
|||
IIF Problem
привет. кто нибудь пишет по русски????
On 12.07.2007 23:48, in article , "KARL DEWEY" wrote: Try doing your subquery calculations in separate queries then join in final query. |
#12
|
|||
|
|||
IIF Problem
I believe the problem may be that an alias cannot be used in a WHERE clause
in the same query. That has been my observation based on experiments, but I admit I don't all of the details. You need a second condition in the IIf. It is not "written OK". If you can get the query to work without the WHERE clause, you can use the named query (qryProducts) to make another query: SELECT ProductID, [Product name], Unit, (select sum(UnitsSold) FROM qryProducts WHERE ((qryProducts.Status] = "OrderNow")); Or you can alias the SQL expression within another SQL expression: SELECT Q1.ProductID, Q1.[Product name], Q1.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 AS Q1 WHERE (((Q1.Status] = "OrderNow")); "Maurice" wrote in message ... 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") |
#13
|
|||
|
|||
IIF Problem
no, there is no any change , i want to ask a question ,can i set a criteria
to a calculated field ? "Maurice" wrote: 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") |
#14
|
|||
|
|||
IIF Problem
thxxxxxxxxxxxxxxxxxxxxxxx BruceM you are realy helpful , your solution
working and i get a perfect result from perfect query you made. thx BruceM, good day. "BruceM" wrote: I believe the problem may be that an alias cannot be used in a WHERE clause in the same query. That has been my observation based on experiments, but I admit I don't all of the details. You need a second condition in the IIf. It is not "written OK". If you can get the query to work without the WHERE clause, you can use the named query (qryProducts) to make another query: SELECT ProductID, [Product name], Unit, (select sum(UnitsSold) FROM qryProducts WHERE ((qryProducts.Status] = "OrderNow")); Or you can alias the SQL expression within another SQL expression: SELECT Q1.ProductID, Q1.[Product name], Q1.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 AS Q1 WHERE (((Q1.Status] = "OrderNow")); "Maurice" wrote in message ... 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") |
#15
|
|||
|
|||
IIF Problem
and many thanks for everyone replies to my subject ,
thxxxxxxxxxxxxx all "rita" wrote: thxxxxxxxxxxxxxxxxxxxxxxx BruceM you are realy helpful , your solution working and i get a perfect result from perfect query you made. thx BruceM, good day. "BruceM" wrote: I believe the problem may be that an alias cannot be used in a WHERE clause in the same query. That has been my observation based on experiments, but I admit I don't all of the details. You need a second condition in the IIf. It is not "written OK". If you can get the query to work without the WHERE clause, you can use the named query (qryProducts) to make another query: SELECT ProductID, [Product name], Unit, (select sum(UnitsSold) FROM qryProducts WHERE ((qryProducts.Status] = "OrderNow")); Or you can alias the SQL expression within another SQL expression: SELECT Q1.ProductID, Q1.[Product name], Q1.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 AS Q1 WHERE (((Q1.Status] = "OrderNow")); "Maurice" wrote in message ... 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") |
#16
|
|||
|
|||
IIF Problem
Glad to know it worked. I learned something too.
"rita" wrote in message ... thxxxxxxxxxxxxxxxxxxxxxxx BruceM you are realy helpful , your solution working and i get a perfect result from perfect query you made. thx BruceM, good day. "BruceM" wrote: I believe the problem may be that an alias cannot be used in a WHERE clause in the same query. That has been my observation based on experiments, but I admit I don't all of the details. You need a second condition in the IIf. It is not "written OK". If you can get the query to work without the WHERE clause, you can use the named query (qryProducts) to make another query: SELECT ProductID, [Product name], Unit, (select sum(UnitsSold) FROM qryProducts WHERE ((qryProducts.Status] = "OrderNow")); Or you can alias the SQL expression within another SQL expression: SELECT Q1.ProductID, Q1.[Product name], Q1.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 AS Q1 WHERE (((Q1.Status] = "OrderNow")); "Maurice" wrote in message ... 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") |
#17
|
|||
|
|||
IIF Problem
Good call Bruce ;-)
-- Maurice Ausum "BruceM" wrote: Glad to know it worked. I learned something too. "rita" wrote in message ... thxxxxxxxxxxxxxxxxxxxxxxx BruceM you are realy helpful , your solution working and i get a perfect result from perfect query you made. thx BruceM, good day. "BruceM" wrote: I believe the problem may be that an alias cannot be used in a WHERE clause in the same query. That has been my observation based on experiments, but I admit I don't all of the details. You need a second condition in the IIf. It is not "written OK". If you can get the query to work without the WHERE clause, you can use the named query (qryProducts) to make another query: SELECT ProductID, [Product name], Unit, (select sum(UnitsSold) FROM qryProducts WHERE ((qryProducts.Status] = "OrderNow")); Or you can alias the SQL expression within another SQL expression: SELECT Q1.ProductID, Q1.[Product name], Q1.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 AS Q1 WHERE (((Q1.Status] = "OrderNow")); "Maurice" wrote in message ... 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 | |
|
|