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")