Prev: DLookup fails in Form's Text Box Control Source when using Windows
Next: Chart Information
From: JJ on 23 Mar 2010 18:18
Does anyone know how to correct the SQL below...

I would like to say if the text in the form control is not equal to "*All"
then give me the results from the form control. However, if the text in the
form control is equal to "*All", then give me everything.

SELECT Step5qry.[Customer Name], Step5qry.[Customer Group], Step5qry.[Sales
Empl ID]
FROM Step5qry
WHERE (((Step5qry.[Customer Group])=IIf([Forms]![MiscVistaNP_frm]![Customer
Group]<>"*All",[Forms]![MiscVistaNP_frm]![Customer Group],Like "*" or Is
Null)));

Thanks!
From: John W. Vinson on 23 Mar 2010 19:16
On Tue, 23 Mar 2010 15:18:05 -0700, JJ <JJ(a)discussions.microsoft.com> wrote:

>Does anyone know how to correct the SQL below...
>
>I would like to say if the text in the form control is not equal to "*All"
>then give me the results from the form control. However, if the text in the
>form control is equal to "*All", then give me everything.
>
>SELECT Step5qry.[Customer Name], Step5qry.[Customer Group], Step5qry.[Sales
>Empl ID]
>FROM Step5qry
>WHERE (((Step5qry.[Customer Group])=IIf([Forms]![MiscVistaNP_frm]![Customer
>Group]<>"*All",[Forms]![MiscVistaNP_frm]![Customer Group],Like "*" or Is
>Null)));
>
>Thanks!

SELECT Step5qry.[Customer Name], Step5qry.[Customer Group], Step5qry.[Sales
Empl ID]
FROM Step5qry
WHERE Step5qry.[Customer Group]=[Forms]![MiscVistaNP_frm]![Customer
Group] OR [Forms]![MiscVistaNP_frm]![Customer Group] = "*All"

--

John W. Vinson [MVP]
From: KARL DEWEY on 23 Mar 2010 19:25
You can not use 'Like' function inside of an IIF statement.

Try this --
SELECT Step5qry.[Customer Name], Step5qry.[Customer Group], Step5qry.[Sales
Empl ID]
FROM Step5qry
WHERE ([Step5qry].[Customer Group] Like
IIf([Forms]![MiscVistaNP_frm]![Customer Group]<>"*All",
[Forms]![MiscVistaNP_frm]![Customer Group], "*")) OR ([Step5qry].[Customer
Group] Is Null);

--
Build a little, test a little.


"JJ" wrote:

> Does anyone know how to correct the SQL below...
>
> I would like to say if the text in the form control is not equal to "*All"
> then give me the results from the form control. However, if the text in the
> form control is equal to "*All", then give me everything.
>
> SELECT Step5qry.[Customer Name], Step5qry.[Customer Group], Step5qry.[Sales
> Empl ID]
> FROM Step5qry
> WHERE (((Step5qry.[Customer Group])=IIf([Forms]![MiscVistaNP_frm]![Customer
> Group]<>"*All",[Forms]![MiscVistaNP_frm]![Customer Group],Like "*" or Is
> Null)));
>
> Thanks!
From: JJ on 23 Mar 2010 21:06
Thanks guys! Works Perfect!

"John W. Vinson" wrote:

> On Tue, 23 Mar 2010 15:18:05 -0700, JJ <JJ(a)discussions.microsoft.com> wrote:
>
> >Does anyone know how to correct the SQL below...
> >
> >I would like to say if the text in the form control is not equal to "*All"
> >then give me the results from the form control. However, if the text in the
> >form control is equal to "*All", then give me everything.
> >
> >SELECT Step5qry.[Customer Name], Step5qry.[Customer Group], Step5qry.[Sales
> >Empl ID]
> >FROM Step5qry
> >WHERE (((Step5qry.[Customer Group])=IIf([Forms]![MiscVistaNP_frm]![Customer
> >Group]<>"*All",[Forms]![MiscVistaNP_frm]![Customer Group],Like "*" or Is
> >Null)));
> >
> >Thanks!
>
> SELECT Step5qry.[Customer Name], Step5qry.[Customer Group], Step5qry.[Sales
> Empl ID]
> FROM Step5qry
> WHERE Step5qry.[Customer Group]=[Forms]![MiscVistaNP_frm]![Customer
> Group] OR [Forms]![MiscVistaNP_frm]![Customer Group] = "*All"
>
> --
>
> John W. Vinson [MVP]
> .
>
From: James A. Fortune on 24 Mar 2010 18:53
On Mar 23, 7:25 pm, KARL DEWEY <KARLDE...(a)discussions.microsoft.com>
wrote:
> You can not use 'Like' function inside of an IIF statement.

What example led you to that conclusion?

I tried:

tblLike
LID AutoNumber
A Integer
B Text

LID A B
1 0 ABC
2 0 DEF
3 1 BCD
4 1 ABC
5 0 GHI

qryLike:
SELECT A, B
FROM tblLike WHERE IIf(A = 1, (B LIKE '*A*'), True);

!qryLike:
A B
0 ABC
0 DEF
1 ABC
0 GHI

Because of the precedence rules (e.g., LIKE is lower in the precedence
hierarchy than OR), I usually enclose LIKE statements in parentheses,
especially when used in conjunction with OR's and AND's, to guarantee
the prevention of undesirable side effects.

James A. Fortune
MPAPoster(a)FortuneJames.com
 |  Next  |  Last
Pages: 1 2
Prev: DLookup fails in Form's Text Box Control Source when using Windows
Next: Chart Information