IIf statement in Criteria

  • Thread starter Thread starter Brent_Fanguy
  • Start date Start date
B

Brent_Fanguy

I am trying to create a query that allows a user to
select from 1 to 3 search criteria from a form that run's
a query that uses only the criteria from the form for the
search.

Example Form: Part # _________
Partial Desc _________
Partial Customer PO _________

for the criteria in the query I need to use from 1 to 3
of these search criteria. The problem i have is that two
of these are Like *[Partial]* searches. If they do not
enter anything it yields all records. So i have been
trying to formulat an IIF stmt that only enters the
criteria if there is something to search for.

I have tried:
IIf([Forms]![Test].[Partial_Desc] is not null, "Like *
[Partial_Desc]*")
IIf([Forms]![Test].[Partial_Desc] is not null, 'Like "*
[Partial_Desc]*"')
IIf([Forms]![Test].[Partial_Desc] is not null, (Like "*
[Partial_Desc]*"))

All to no avail.

Any help would be greatly appreciated.
 
Hi,


When a function is called, the sequence is first to evaluate the arguments,
THEN to send it to the function/statement.

in:

iif( arg1, arg2, arg3)

you supplied, for arg2, an expression :

Like "*" & [Partial_Desc] & "*"


and that, cannot be evaluated, neither

LIKE "*" & Partial_Desc


could be evaluated. The iif is not in "function", when the error occurs,
since Access can't evaluate an argument to be send to the function
(statement).

Edit the SQL statement, change it to:

..... WHERE iif( ([Forms]![Test].[Partial_Desc] is not null , WHAT_FIELD
LIKE "*" & Partial_Desc & "*", true )


or just

.... WHERE WHAT_FIELD LIKE "*" & [Forms]![Test].[Partial_Desc] & "*"

since if the control is null, the concatenation would leaves "**", which
is just fine to match anything (except NULL).



(change WHAT_FIELD by the real field name implied by the comparison with
the operator LIKE ).





Hoping it may help,
Vanderghast, Access MVP
 
Mike thanks for the reply.

But i am not following you. what i want is to create a
criteria with a Like *[input]* that only searches when
[input] is not null. I never want it to yield all
records (Like **).

If have tried this statement and can not get any results,
can you try?

iif( ([Forms]![Test].[Partial_Desc] is not null ,
WHAT_FIELD LIKE "*" & Partial_Desc & "*", true )

this is what i am using:

IIf([Forms]![Test].[Partial_Desc] Is Not Null,[ARMA].
[NAMEX] Like "*" & [Forms]![Test].[Partial_Desc]
& "*",True)

If i put this in as a field, all i get is -1.

TIA,

Brent




-----Original Message-----
Hi,


When a function is called, the sequence is first to evaluate the arguments,
THEN to send it to the function/statement.

in:

iif( arg1, arg2, arg3)

you supplied, for arg2, an expression :

Like "*" & [Partial_Desc] & "*"


and that, cannot be evaluated, neither

LIKE "*" & Partial_Desc


could be evaluated. The iif is not in "function", when the error occurs,
since Access can't evaluate an argument to be send to the function
(statement).

Edit the SQL statement, change it to:

..... WHERE iif( ([Forms]![Test].[Partial_Desc] is not null , WHAT_FIELD
LIKE "*" & Partial_Desc & "*", true )


or just

.... WHERE WHAT_FIELD LIKE "*" & [Forms]![Test]. [Partial_Desc] & "*"

since if the control is null, the concatenation would leaves "**", which
is just fine to match anything (except NULL).



(change WHAT_FIELD by the real field name implied by the comparison with
the operator LIKE ).





Hoping it may help,
Vanderghast, Access MVP



I am trying to create a query that allows a user to
select from 1 to 3 search criteria from a form that run's
a query that uses only the criteria from the form for the
search.

Example Form: Part # _________
Partial Desc _________
Partial Customer PO _________

for the criteria in the query I need to use from 1 to 3
of these search criteria. The problem i have is that two
of these are Like *[Partial]* searches. If they do not
enter anything it yields all records. So i have been
trying to formulat an IIF stmt that only enters the
criteria if there is something to search for.

I have tried:
IIf([Forms]![Test].[Partial_Desc] is not null, "Like *
[Partial_Desc]*")
IIf([Forms]![Test].[Partial_Desc] is not null, 'Like "*
[Partial_Desc]*"')
IIf([Forms]![Test].[Partial_Desc] is not null, (Like "*
[Partial_Desc]*"))

All to no avail.

Any help would be greatly appreciated.
Click to expand...


.
Click to expand...
 
Mike,

i got it now, you put this as a field with a criteria of
<> False!!!

Thanks a bunch, you da man!!!

Brent




-----Original Message-----
Mike thanks for the reply.

But i am not following you. what i want is to create a
criteria with a Like *[input]* that only searches when
[input] is not null. I never want it to yield all
records (Like **).

If have tried this statement and can not get any results,
can you try?

iif( ([Forms]![Test].[Partial_Desc] is not null ,
WHAT_FIELD LIKE "*" & Partial_Desc & "*", true )

this is what i am using:

IIf([Forms]![Test].[Partial_Desc] Is Not Null,[ARMA].
[NAMEX] Like "*" & [Forms]![Test].[Partial_Desc]
& "*",True)

If i put this in as a field, all i get is -1.

TIA,

Brent




-----Original Message-----
Hi,


When a function is called, the sequence is first to evaluate the arguments,
THEN to send it to the function/statement.

in:

iif( arg1, arg2, arg3)

you supplied, for arg2, an expression :

Like "*" & [Partial_Desc] & "*"


and that, cannot be evaluated, neither

LIKE "*" & Partial_Desc


could be evaluated. The iif is not in "function", when the error occurs,
since Access can't evaluate an argument to be send to the function
(statement).

Edit the SQL statement, change it to:

..... WHERE iif( ([Forms]![Test].[Partial_Desc] is not null , WHAT_FIELD
LIKE "*" & Partial_Desc & "*", true )


or just

.... WHERE WHAT_FIELD LIKE "*" & [Forms]![Test]. [Partial_Desc] & "*"

since if the control is null, the concatenation would leaves "**", which
is just fine to match anything (except NULL).



(change WHAT_FIELD by the real field name implied by the comparison with
the operator LIKE ).





Hoping it may help,
Vanderghast, Access MVP



I am trying to create a query that allows a user to
select from 1 to 3 search criteria from a form that run's
a query that uses only the criteria from the form for the
search.

Example Form: Part # _________
Partial Desc _________
Partial Customer PO _________

for the criteria in the query I need to use from 1 to 3
of these search criteria. The problem i have is that two
of these are Like *[Partial]* searches. If they do not
enter anything it yields all records. So i have been
trying to formulat an IIF stmt that only enters the
criteria if there is something to search for.

I have tried:
IIf([Forms]![Test].[Partial_Desc] is not null, "Like *
[Partial_Desc]*")
IIf([Forms]![Test].[Partial_Desc] is not null, 'Like "*
[Partial_Desc]*"')
IIf([Forms]![Test].[Partial_Desc] is not null, (Like "*
[Partial_Desc]*"))

All to no avail.

Any help would be greatly appreciated.
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top