IIf expression not working

[blainegray]

Greetings
I am using this as a criteria in a query.

IIf([Forms]![frmBooks_Select_Types]![cboSelectBooks_Type1Selection]=23,
([tblBooks].[B_BookMovieType1ID]) Like "*",
[Forms]![frmBooks_Select_Types]![cboSelectBooks_Type1Selection])

If the first part is false, the false condition works fine. The criteria becomes the value of the combo box.

If the first part is true, the true condition does not yeild all the records.
Any idea why true part does not come out correctly?

If I put Like "*" as criteria I do get all the records.
Thanks,
Blaine

[FishVal]

Hi, Blaine.

Try the following criteria.

[code=sql]
..... WHERE [YourFieldName]=[Forms]![frmBooks_Select _Types]![cboSelectBooks_ Type1Selection] OR [Forms]![frmBooks_Select _Types]![cboSelectBooks_ Type1Selection]=23 ...
[/code]

Regards,
Fish

[blainegray]

Hi, Blaine.

Try the following criteria.

[code=sql]
..... WHERE [YourFieldName]=[Forms]![frmBooks_Select _Types]![cboSelectBooks_ Type1Selection] OR [Forms]![frmBooks_Select _Types]![cboSelectBooks_ Type1Selection]=23 ...
[/code]

Regards,
Fish

Fish,
I do not understand your reply.

If the cbo value is 23, all records should be selected.
If the cbo value is not 23, then use the value to select the records matching that value. That part is working fine. The correct records are being selected.

Maybe the Like "*" is not being interpreted correctly.
All that comes back is one blank row.

Thanks,
Blaine

[FishVal]

......
If the cbo value is 23, all records should be selected.
If the cbo value is not 23, then use the value to select the records matching that value.
[/code]

Example of WHERE clause of query SQL expression states exactly the same.

[code]
.....
Maybe the Like "*" is not being interpreted correctly.
.....

Just not the way you are expecting.
IIF() is a function that returns 2nd argument value if the 1st evaluates to True, otherwise it returns 3rd argument value.
In your case 2nd argument is a boolean expression which evaluates to boolean value and thus could hardly match all records in the table.

Regards,
Fish

[blainegray]

Just not the way you are expecting.
IIF() is a function that returns 2nd argument value if the 1st evaluates to True, otherwise it returns 3rd argument value.
In your case 2nd argument is a boolean expression which evaluates to boolean value and thus could hardly match all records in the table.

Regards,
Fish

Fish,
Oh. That makes sense.
Is there a way to have the second arguement as an expression that would match all records? That was the intent of the Like "*".
Thanks,
Blaine

[FishVal]

Fish,
Oh. That makes sense.
Is there a way to have the second arguement as an expression that would match all records? That was the intent of the Like "*".
Thanks,
Blaine

You may try something like:

Like IIf([Forms]![frmBooks_Select_Types]![cboSelectBooks_Type1Selection]=23, "*", [Forms]![frmBooks_Select_Types]![cboSelectBooks_Type1Selection])

Though I would use the previous suggestion.

Regards,
Fish

[NeoPa]

Fish is absolutely right Blaine. You are misreading the logic of his earlier suggestion (post #2).

Perhaps if we look at it with extra parentheses it will become clearer.
[code=sql]...
WHERE (([YourFieldName]=[Forms]![frmBooks_Select _Types]![cboSelectBooks_ Type1Selection])
OR ([Forms]![frmBooks_Select _Types]![cboSelectBooks_ Type1Selection]=23))
...[/code]

PS. If you can't see the logic - don't worry. Try it out anyway.

[blainegray]

Fish is absolutely right Blaine. You are misreading the logic of his earlier suggestion (post #2).

Perhaps if we look at it with extra parentheses it will become clearer.
[code=sql]...
WHERE (([YourFieldName]=[Forms]![frmBooks_Select _Types]![cboSelectBooks_ Type1Selection])
OR ([Forms]![frmBooks_Select _Types]![cboSelectBooks_ Type1Selection]=23))
...[/code]

PS. If you can't see the logic - don't worry. Try it out anyway.

Fish and NeoPa
Greetings
I tried Fish's suggestion of putting the Like first and letting the IIf select the values. What a clever idea. I probably would not have ever come up with that.
I see why you all are the experts. I did try the solution with Like then IIf and it works great. If a user makes no selection, all the values come back. If a user makes a type 1 selection and no type 2, all the type 2 for that type 1 come back. If a user makes a type 1 and a type 2 selection, then only those specific ones come back. Success! Thanks again for your help and insight.
Blaine

[FishVal]

You are welcome.
Good luck.

[NeoPa]

Aah.

But this is where coding efficiency comes in.

The "clever" code (not using "Like") should actually run more efficiently too I would expect. Therefore faster ;)

Glad you got a answer you're happy with anyway :)