Option Buttons to Pass Criteria to Query

R

rocketD

Hello,

I have a form that has some unbound controls that pass criteria to a
query that displays in the lower half of the form. One of the fields
I have criteria for is a yes/no field called invalidEmail. The values
are yes if the email is invalid; no if the email is valid. In the
query, I want the user to be able to display (1) only records with
invalid emails or (2) only records with valid emails or (3) all
records. I tried a form checkbox where in the query, the criterion is
forms.fm__.checkbox, but that will only give me displays 1 and 2.

So, I tried 3 option buttons, but because they are numeric, I can't
set a value at * to get select all. So I tried the following
statement:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,"*"))
Access doesn't like this if [emailOptions]=3, which is my intended
"show all" button, or if no options are selected. The error message
is "The expression is typed incorrectly or too complex to be
evaluated...."

It doesn't seem right, but I also tried:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,IIf([forms].[fmQryByClinic].
[emailOptions]=3,"*","*"))
Access doesn't like that either, same error.

Does anyone know what I'm missing here?

Thanks,
Dara
 
J

John Spencer

If you enter the following into a criteria cell under your field you should
get the desired results. It will get restructured in the query design view
when you save the query.

IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClinic]![emailOptions]=2,False,Null))
OR [forms]![fmQryByClinic]![emailOptions]=3



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
K

KARL DEWEY

Try this --
Like
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].[fmQryByClinic].[emailOptions]=2,False,"*"))
 
R

rocketD

Try this --
    Like
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].[fmQryByClini­c].[emailOptions]=2,False,"*"))

--
Build a little, test a little.



rocketD said:
Hello,
Click to expand...
I have a form that has some unbound controls that pass criteria to a
query that displays in the lower half of the form.  One of the fields
I have criteria for is a yes/no field called invalidEmail.  The values
are yes if the email is invalid; no if the email is valid.  In the
query, I want the user to be able to display (1) only records with
invalid emails or (2) only records with valid emails or (3) all
records.  I tried a form checkbox where in the query, the criterion is
forms.fm__.checkbox, but that will only give me displays 1 and 2.
Click to expand...
So, I tried 3 option buttons, but because they are numeric, I can't
set a value at * to get select all.  So I tried the following
statement:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,"*"))
Access doesn't like this if [emailOptions]=3, which is my intended
"show all" button, or if no options are selected.  The error message
is "The expression is typed incorrectly or too complex to be
evaluated...."
Click to expand...
It doesn't seem right, but I also tried:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,IIf([forms].[fmQryByClinic].
[emailOptions]=3,"*","*"))
Access doesn't like that either, same error.
Click to expand...
Does anyone know what I'm missing here?
Click to expand...
Thanks,
Dara
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks for trying, but that's the same as the first code I listed in
my post, it doesn't work.
 
R

rocketD

If you enter the following into a criteria cell under your field you should
get the desired results.  It will get restructured in the query design view
when you save the query.

IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClini­c]![emailOptions]=2,False,Null))
OR [forms]![fmQryByClinic]![emailOptions]=3

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County


Hello,
Click to expand...
I have a form that has some unbound controls that pass criteria to a
query that displays in the lower half of the form.  One of the fields
I have criteria for is a yes/no field called invalidEmail.  The values
are yes if the email is invalid; no if the email is valid.  In the
query, I want the user to be able to display (1) only records with
invalid emails or (2) only records with valid emails or (3) all
records.  I tried a form checkbox where in the query, the criterion is
forms.fm__.checkbox, but that will only give me displays 1 and 2.
Click to expand...
So, I tried 3 option buttons, but because they are numeric, I can't
set a value at * to get select all.  So I tried the following
statement:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,"*"))
Access doesn't like this if [emailOptions]=3, which is my intended
"show all" button, or if no options are selected.  The error message
is "The expression is typed incorrectly or too complex to be
evaluated...."
Click to expand...
It doesn't seem right, but I also tried:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,IIf([forms].[fmQryByClinic].
[emailOptions]=3,"*","*"))
Access doesn't like that either, same error.
Click to expand...
Does anyone know what I'm missing here?
Click to expand...
Thanks,
Dara- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Your suggestion worked perfectly, thanks a ton! Now I have to stare
at it and figure out why... :)
Dara
 
J

John Spencer

Explanation:
The first test is applied against your field.
IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClini­c]![emailOptions]=2,False,Null))

In a Where clause
WHERE [YourField] =
IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClini­c]![emailOptions]=2,False,Null))

After the IIF is evaluated that is going to be
WHERE [YourField]= True (or False or Null)

The second test is actually is going to return TRUE or FALSE and does not
involve your field at all.

WHERE [forms]![fmQryByClinic]![emailOptions]=3

Records are returned when the WHERE clause evaluates to TRUE

SO
WHERE ([YourField] =
IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClini­c]![emailOptions]=2,False,Null))
OR [forms]![fmQryByClinic]![emailOptions] = 3)
evaluates to return the desired records.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
If you enter the following into a criteria cell under your field you should
get the desired results. It will get restructured in the query design view
when you save the query.

IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClini­c]![emailOptions]=2,False,Null))
OR [forms]![fmQryByClinic]![emailOptions]=3

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County


Hello,
I have a form that has some unbound controls that pass criteria to a
query that displays in the lower half of the form. One of the fields
I have criteria for is a yes/no field called invalidEmail. The values
are yes if the email is invalid; no if the email is valid. In the
query, I want the user to be able to display (1) only records with
invalid emails or (2) only records with valid emails or (3) all
records. I tried a form checkbox where in the query, the criterion is
forms.fm__.checkbox, but that will only give me displays 1 and 2.
So, I tried 3 option buttons, but because they are numeric, I can't
set a value at * to get select all. So I tried the following
statement:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,"*"))
Access doesn't like this if [emailOptions]=3, which is my intended
"show all" button, or if no options are selected. The error message
is "The expression is typed incorrectly or too complex to be
evaluated...."
It doesn't seem right, but I also tried:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,IIf([forms].[fmQryByClinic].
[emailOptions]=3,"*","*"))
Access doesn't like that either, same error.
Does anyone know what I'm missing here?
Thanks,
Dara- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Your suggestion worked perfectly, thanks a ton! Now I have to stare
at it and figure out why... :)
Dara
Click to expand...
 
R

rocketD

Explanation:
The first test is applied against your field.
IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClini­­c]![emailOptions]=2,False,Null))

In a Where clause
WHERE [YourField] =
IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClini­­c]![emailOptions]=2,False,Null))

After the IIF is evaluated that is going to be
WHERE [YourField]= True (or False or Null)

The second test is actually is going to return TRUE or FALSE and does not
involve your field at all.

WHERE  [forms]![fmQryByClinic]![emailOptions]=3

Records are returned when the WHERE clause evaluates to TRUE

SO
WHERE ([YourField] =
IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClini­­c]![emailOptions]=2,False,Null))
OR [forms]![fmQryByClinic]![emailOptions] = 3)
evaluates to return the desired records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County


If you enter the following into a criteria cell under your field you should
get the desired results.  It will get restructured in the query design view
when you save the query.
IIF([forms]![fmQryByClinic]![emailOptions]=1,True,IIF([forms]![fmQryByClini­­c]![emailOptions]=2,False,Null))
OR [forms]![fmQryByClinic]![emailOptions]=3
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
rocketD wrote:
Hello,
I have a form that has some unbound controls that pass criteria to a
query that displays in the lower half of the form.  One of the fields
I have criteria for is a yes/no field called invalidEmail.  The values
are yes if the email is invalid; no if the email is valid.  In the
query, I want the user to be able to display (1) only records with
invalid emails or (2) only records with valid emails or (3) all
records.  I tried a form checkbox where in the query, the criterionis
forms.fm__.checkbox, but that will only give me displays 1 and 2.
So, I tried 3 option buttons, but because they are numeric, I can't
set a value at * to get select all.  So I tried the following
statement:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,"*"))
Access doesn't like this if [emailOptions]=3, which is my intended
"show all" button, or if no options are selected.  The error message
is "The expression is typed incorrectly or too complex to be
evaluated...."
It doesn't seem right, but I also tried:
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].
[fmQryByClinic].[emailOptions]=2,False,IIf([forms].[fmQryByClinic].
[emailOptions]=3,"*","*"))
Access doesn't like that either, same error.
Does anyone know what I'm missing here?
Thanks,
Dara- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Your suggestion worked perfectly, thanks a ton!  Now I have to stare
at it and figure out why... :)
Dara- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Excellent explanation. Thank you for taking the time.

Dara
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Options Group and Query Criteria, revisited 3
Query Parameter using multiple options 0
Criteria in Querry to include All and Empty Field 9
Radio Button Value In Query 3
Criteria from Form 3
Running Report from Query using Open Form or Entering Criteria 1
Query Criteria IIf statement 4
Is Null 2

Top