Crazy IIf

[Shep]

Is it possible to use an IIf statement in the criteria field of a query which, assuming proper condition is met, will return the string:

Like "*"

AND which will work properly, once done?

I have supplied this string via a function to the IIf statement in the query criteria field and it returns no records.

If I use the string by itself, it works fine.

Losing brain cells over this one.

Shep

 

[DALeffler]

No.

The reason is because a function (or an Iif, or what have you) is returning a string or a number, not an SQL operator.

Say you have:

SELECT AlarmCats.AlarmCatID, AlarmCats.AlarmCat
FROM AlarmCats
WHERE (((AlarmCats.AlarmCat)=fnc1tst()));

and fnct1tst is coded:

Function fnc1tst() As String
fnc1tst = Chr(34) & "Like " & Chr(34) & "*" & Chr(34)
End Function

The SQL that is executed will read something like:

SELECT AlarmCats.AlarmCatID, AlarmCats.AlarmCat
FROM AlarmCats
WHERE (((AlarmCats.AlarmCat)='Like "*"'));

and you need the SQL to be:

SELECT AlarmCats.AlarmCatID, AlarmCats.AlarmCat
FROM AlarmCats
WHERE (((AlarmCats.AlarmCat) Like "*"));

You need to design your function into creating the 2nd SQL statement and executing the SQL from your VBA code. Nothing we do in the query design grid (except entering Like "*" directly into the criteria field) will cause the 2nd SQL statemnet to be run.

HTH,

Doug

 

[Shep]

Thank you, Doug.

I will study a way to display the SQL that would be produced by this uncommon marriage of ideas that I had, so as to reinforce and confirm what you're telling me. And, of course, to help determine a way to accomplish my goal.

 

[Jon K]

I have a query containing an iif() function in its criteria. The query runs fine:-

SELECT *
FROM CAR
WHERE IIf(Day(Date())<15, ND IN ("1","3"),ND LIKE "*")


Maybe you can arrange your iif() function in the same way.

 

[Shep]

Hi Jon,

I tried my IIf statement in various ways. One particular edit of it, upon saving the query and reopening it, had 'rewritten' my statement in part...changing the section containing the 'Like' portion into '([Some field]) Like...' etc, which is quite similar to what you present here. It didn't return any records, but it's a query based on another query. It does bring hope, though! There might be some way for me to rearrange it.

I will work on that.

Many thanks.

Shep

 

[Shep]

Got it

This was much simpler than I thought.

I wanted the query to display all records if the condition was met.

Instead of using the Like operator to display "*" (all) records, I tried using the query field name instead, in brackets, without quotes. This works, and is probably far faster and more efficient than the Like operator.

In the query 'Field' field:
Status

In the query 'Table' field:
qryEstimateSort_Source

In the query 'Criteria' field:
IIf(Len(EstimateStatus())=0,[Status],EstimateStatus())

Works like a charm.

 

[DALeffler]

I also tried the

SELECT AlarmCats.AlarmCatID, AlarmCats.AlarmCat
FROM AlarmCats
WHERE (((AlarmCats.AlarmCat)=IIf(True=True,(AlarmCats.AlarmCat) Like "*",(AlarmCats.AlarmCat) Like "Low")));

and could not get the query to return any records no matter what I put in the table field. I think the problem is that equal sign in front of the Iif statement.

However, if you put the iif statement behind the Like operator in the criteria field, things work much better...

Critera:

Like (Iif(True = True, "*", "Low"))

Doug - wondering which mistake I'm gonna learn from next

 

[Jon K]

When you use the cells in the grids of a query in Design View, Access will always add the = operator in the Criteria (in the SQL statement that it runs) if it doesn't have an operator.

But, if you type (or edit) your SQL statement in the SQL View of the query and do not change anything in the Design View after typing (or editing), Access will leave your query statement intact.


So if you type in the SQL View:
SELECT AlarmCatID, AlarmCat
FROM AlarmCats
WHERE IIf(True=True, AlarmCat Like "*", AlarmCat Like "Low");

or:
SELECT AlarmCatID, AlarmCat
FROM AlarmCats
WHERE AlarmCat like IIf(True=True, "*", "Low");


the query will work. You can switch to Design View to see how Access places the statement in the grids.

However, since True always equals to True, the above criteria will always return AlarmCat like "*", so the purpose of using the iif() function is defected.


Shep's criteria works because when the condition Len(EstimateStatus())=0 is true, the Where clause becomes:
WHERE Status = [Status]

and when the condition is false, the Where clause becomes:
WHERE Status = EstimateStatus()

whereas Status = [Status] is true for every record in the table.

 

[Shep]

Yes, the "=" operator seems to be the culprit. Of course that cannot mix with the "Like" operator.

Using Like([Expression]) is great, too.
Good thinking, Doug.

And Jon, thanks for your added depth. Between we three, I learned something here which will save a bunch of time in the future.

Shep