[AccessD] Where claise in IIf query

Oleg,

  OK a couple of things.  First, no you don't have to write it all out.
Second, is [Amount] a numeric field?  Your not treating it like one.  Also,
your iif's are looking for "2 "  (two and a space). Bad idea to have
embedded spaces.  Third, you omitted the false conditions on the last IIF()
in both field definitions (I put ???? in as the value).

  Last, if you find the syntax difficult, either use the query designer to
build the SQL statement (which is what I do when a SQL statement gets long)
or use a function to remove all the IIF()'s as already suggested.

  He is the correct SQL:

SELECT Table1.Color, Table1.Furniture, Table1.Amount, IIf([Amount]="4
","777",IIf([Amount]="2 ","060010",IIf([Amount]="3 ","055","????"))) AS HH,
IIf([HH]="060010 ","zzz",IIf([HH]="777 ","yyy","????")) AS JJ
FROM Table1;

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
Oleg_123 at xuppa.com
Sent: Monday, November 10, 2003 4:07 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Where claise in IIf query


if i just say 'WHERE JJ = "zzz" 'I get prompted to enter Parameter
if i write
WHERE (((IIf([HH]="060010 ","zzz"))="zzz"));
i get prompted for HH

Am I supposed to write it all out ?
the origina l is kinda long, and I keep getting lost in perentasis and
brackets

SELECT Table3.color, Table3.furniture, Table3.amount, IIf([Amount]="3
","055") AS Expr2, IIf([Amount]="4 ","777",IIf([Amount]="2
","060010",IIf([Amount]="3 ","055"))) AS HH, IIf([HH]="060010 ","zzz") AS
JJ
FROM Table3

> Sorry, read the question without looking at the SQL.  The SQL you have
> looks fine.  What's the problem your having?
>
> Jim Dettman
> President,
> Online Computer Services of WNY, Inc.
> (315) 699-3443
> jimdettman at earthlink.net
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
> Sent: Monday, November 10, 2003 2:22 PM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] Where claise in IIf query
>
>
>
>   Define the IIF() expression as a column (field), then place criteria
> on
> that.
>
> Jim Dettman
> President,
> Online Computer Services of WNY, Inc.
> (315) 699-3443
> jimdettman at earthlink.net
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
> Oleg_123 at xuppa.com
> Sent: Monday, November 10, 2003 1:43 PM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Where claise in IIf query
>
>
> Hey Group, I am not able to put conditions in queries whee I use IIF.
> What is the alternate solution ?
> This works ---
>
> SELECT Table3.amount,
> IIf([Amount]="4 ","777",IIf([Amount]="2 ","060010",IIf([Amount="3
> ","055"))) AS HH,
> IIf([HH]="060010 ","zzz",IIf([HH]="777 ","yyy")) AS JJ
> FROM Table3;
>
> however when  Iput in WHERE
>
> SELECT Table3.amount,
> IIf([Amount]="4 ","777",IIf([Amount]="2 ","060010",IIf([Amount="3
> ","055"))) AS HH,
> IIf([HH]="060010 ","zzz",IIf([HH]="777 ","yyy")) AS JJ
> FROM Table3
> WHERE JJ = "zzz"
>
> doesn't work. I tried inserting GROUP BY by all statements, and then
> HAVING, but doesn't work either...
>
>
> -----------------------------------------
> Get Breaking News from CNN, ABC, NBC, CBS Now.
> http://www.xuppa.com/news/?link=webmail
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



-----------------------------------------
Get Breaking News from CNN, ABC, NBC, CBS Now.
http://www.xuppa.com/news/?link=webmail


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com