Return Null Values

[JJ]

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!

 

[John W. Vinson]

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"

 

[KARL DEWEY]

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

 

[JJ]

Thanks guys! Works Perfect!

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"

 

[James A. Fortune]

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
(e-mail address removed)

 

[JJ]

I used John's way:

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"

 

[James A. Fortune]

I used John's way:

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"

I'm glad you found a solution. That's the important thing. John's
query works because the = operator does not interpret any symbols as
wild cards. I was not trying to suggest a particular solution. I was
simply pointing out an apparent overgeneralization about the LIKE
operator.

James A. Fortune
(e-mail address removed)