Conditional + Wildcard in Critera?

[Guest]

So, if I try to put those three things together (Conditional + Wildcard +
Criteria), I just get empty results sets.

What I'm trying to do, is to get it to to return-all if a variable is left
blank (either via an imput form or a popup variable dialogue).

So, I have something like this:

iif([Variable] is null,Like "*",[Variable])

I've tried many variations of this same concept:
iif([Variable]="",Like "*",[Variable])
iif(isNull [Variable],Like "*",[Variable])
iif([Variable]="","*",[Variable])

and so forth. I can't get it to work as criteria if it is part of a
conditional function.

 

[Duane Hookom]

Try:

Like Nz("*" + [Variable] + "*","*", )

 

[Douglas J. Steele]

Actually, it sounds as though they only want equality if the variable is not
null, but everything if it is.

That means you'd only need

Like Nz([Variable],"*")

You've got an extra comma in there.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Try:

Like Nz("*" + [Variable] + "*","*", )

--
Duane Hookom
MS Access MVP
--

So, if I try to put those three things together (Conditional + Wildcard +
Criteria), I just get empty results sets.

What I'm trying to do, is to get it to to return-all if a variable is
left
blank (either via an imput form or a popup variable dialogue).

So, I have something like this:

iif([Variable] is null,Like "*",[Variable])

I've tried many variations of this same concept:
iif([Variable]="",Like "*",[Variable])
iif(isNull [Variable],Like "*",[Variable])
iif([Variable]="","*",[Variable])

and so forth. I can't get it to work as criteria if it is part of a
conditional function.

 

[Guest]

Thanks. Exactly. Awesome. Always used that in field-to-field calculations,
didn't think of using it here. Works perfectly with my iif statements, and
even cuts down on my typing for those other instances where I don't. Again,
thanks.

-Undrline.

Actually, it sounds as though they only want equality if the variable is not
null, but everything if it is.

That means you'd only need

Like Nz([Variable],"*")

You've got an extra comma in there.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Try:

Like Nz("*" + [Variable] + "*","*", )

--
Duane Hookom
MS Access MVP
--

So, if I try to put those three things together (Conditional + Wildcard +
Criteria), I just get empty results sets.

What I'm trying to do, is to get it to to return-all if a variable is
left
blank (either via an imput form or a popup variable dialogue).

So, I have something like this:

iif([Variable] is null,Like "*",[Variable])

I've tried many variations of this same concept:
iif([Variable]="",Like "*",[Variable])
iif(isNull [Variable],Like "*",[Variable])
iif([Variable]="","*",[Variable])

and so forth. I can't get it to work as criteria if it is part of a
conditional function.

 

[Guest]

Thank you for your response. Though Douglas J. Steele was right as to what I
was looking for, there are instances when I'll need to use your version for
free-text fields, allowing the user to input a piece of a word or nothing at
all and still get matches. Also, you helped me to understand the syntax
better. His version alone would've confused me, because I wouldn't know if I
could use my iif statement as the first parameter. I'm glad you were able to
point me in the right direction.

Thanks.

-Undrline

Try:

Like Nz("*" + [Variable] + "*","*", )

--
Duane Hookom
MS Access MVP
--

So, if I try to put those three things together (Conditional + Wildcard +
Criteria), I just get empty results sets.

What I'm trying to do, is to get it to to return-all if a variable is left
blank (either via an imput form or a popup variable dialogue).

So, I have something like this:

iif([Variable] is null,Like "*",[Variable])

I've tried many variations of this same concept:
iif([Variable]="",Like "*",[Variable])
iif(isNull [Variable],Like "*",[Variable])
iif([Variable]="","*",[Variable])

and so forth. I can't get it to work as criteria if it is part of a
conditional function.