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

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

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

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

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

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

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

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

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

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