If Statement with "*" in Query

  • Thread starter Thread starter Wes
  • Start date Start date
W

Wes

Hello,

I am working on a query that runs when i open a form. The setup I am
using is:

I have a form with 3 checkboxes. When I check one of the boxes, a
query filters the results based on a field called "Completed", which
is also a checkbox field saved in my table. I am using the expression
below.

Iif([Forms]![BooksMain]![UnreadSort_Check] is not null, No,
iif([Forms]![BooksMain]![CompleteSort_Check] is not null,Yes,
iif([Forms]![BooksMain]![AllSort_Check] is not null,”*”,”*”))

The yes and no searches work, but when I try to search ALL using "*",
I get the error message "The expression is typed incorrectly or is too
complex to be evaluated. For example, a numeric......". Anyone have
any idea where I am going wrong?

Thank you very much for any advice.
 
Clarification: I am trying to make the last expression (whether the
"All" checkbox is checked or if none of the checkboxes are checked),
return all records.
 
Hello,

I am working on a query that runs when i open a form. The setup I am
using is:

I have a form with 3 checkboxes. When I check one of the boxes, a
query filters the results based on a field called "Completed", which
is also a checkbox field saved in my table. I am using the expression
below.

Iif([Forms]![BooksMain]![UnreadSort_Check] is not null, No,
iif([Forms]![BooksMain]![CompleteSort_Check] is not null,Yes,
iif([Forms]![BooksMain]![AllSort_Check] is not null,”*”,”*”))

The yes and no searches work, but when I try to search ALL using "*",
I get the error message "The expression is typed incorrectly or is too
complex to be evaluated. For example, a numeric......". Anyone have
any idea where I am going wrong?

Thank you very much for any advice.
Click to expand...

Wildcards won't work as you expect with numeric (yes/no) fields. And you don't
need any IIF's at all. Try using a criterion of

[Completed] =NOT [Forms]![BookMain]![UnreadSort_Check]
OR
[Completed]=[Forms]![BookMain]![CompleteSort_Check]
OR
[Forms]![BookMain]![AllSort_Check]

Set the Default Value property of each form checkbox to False so it's either
checked or unchecked, not NULL.

An alternative would be to not use checkboxes at all, but rather an Option
Group control with radio buttons for Completed, Not Completed, Don't Care with
values 1, 2, 3; and a criterion of

([Completed] AND [Forms]![BookMain][OptSort] = 1)
OR
(NOT [Completed] AND [Forms]![BookMain][OptSort] = 2)
OR
([Forms]![BookMain][OptSort] = 3)

This has the advantage that the user doesn't have the opportunity to check TWO
checkboxes, or leave none of them checked... which will give ambiguous or
erroneous results.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top