A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

IIF Problem



 
 
Thread Tools Display Modes
  #11  
Old July 13th, 2007, 01:43 AM posted to microsoft.public.access.tablesdbdesign
macintosh
external usenet poster
 
Posts: 1
Default 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  
Old July 13th, 2007, 01:01 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default 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  
Old July 13th, 2007, 01:06 PM posted to microsoft.public.access.tablesdbdesign
Rita
external usenet poster
 
Posts: 196
Default 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  
Old July 13th, 2007, 01:28 PM posted to microsoft.public.access.tablesdbdesign
Rita
external usenet poster
 
Posts: 196
Default 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  
Old July 13th, 2007, 01:32 PM posted to microsoft.public.access.tablesdbdesign
Rita
external usenet poster
 
Posts: 196
Default 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  
Old July 13th, 2007, 01:44 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default 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  
Old July 13th, 2007, 03:34 PM posted to microsoft.public.access.tablesdbdesign
Maurice
external usenet poster
 
Posts: 1,585
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.