Option Buttons to Pass Criteria to Query

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
0
rocketD
12/4/2009 8:14:09 PM
access 16762 articles. 3 followers. Follow

6 Replies
2488 Views

Similar Articles

[PageSpeed] 52

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

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 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
0
John
12/4/2009 8:55:17 PM
Try this --
    Like 
IIf([forms].[fmQryByClinic].[emailOptions]=1,True,IIf([forms].[fmQryByClinic].[emailOptions]=2,False,"*"))

-- 
Build a little, test a little.


"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 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
> .
> 
0
Utf
12/4/2009 8:57:01 PM
On Dec 4, 1:57=A0pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
> Try this --
> =A0 =A0 Like
> IIf([forms].[fmQryByClinic].[emailOptions]=3D1,True,IIf([forms].[fmQryByC=
lini=ADc].[emailOptions]=3D2,False,"*"))
>
> --
> Build a little, test a little.
>
>
>
> "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. =A0One of the fields
> > I have criteria for is a yes/no field called invalidEmail. =A0The value=
s
> > are yes if the email is invalid; no if the email is valid. =A0In 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. =A0I tried a form checkbox where in the query, the criterion i=
s
> > 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. =A0So I tried the following
> > statement:
> > IIf([forms].[fmQryByClinic].[emailOptions]=3D1,True,IIf([forms].
> > [fmQryByClinic].[emailOptions]=3D2,False,"*"))
> > Access doesn't like this if [emailOptions]=3D3, which is my intended
> > "show all" button, or if no options are selected. =A0The 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]=3D1,True,IIf([forms].
> > [fmQryByClinic].[emailOptions]=3D2,False,IIf([forms].[fmQryByClinic].
> > [emailOptions]=3D3,"*","*"))
> > Access doesn't like that either, same error.
>
> > Does anyone know what I'm missing here?
>
> > Thanks,
> > Dara
> > .- Hide quoted text -
>
> - Show quoted text -

Thanks for trying, but that's the same as the first code I listed in
my post, it doesn't work.
-1
rocketD
12/4/2009 10:00:11 PM
On Dec 4, 1:55=A0pm, John Spencer <spen...@chpdm.edu> wrote:
> If you enter the following into a criteria cell under your field you shou=
ld
> get the desired results. =A0It will get restructured in the query design =
view
> when you save the query.
>
> IIF([forms]![fmQryByClinic]![emailOptions]=3D1,True,IIF([forms]![fmQryByC=
lini=ADc]![emailOptions]=3D2,False,Null))
> OR [forms]![fmQryByClinic]![emailOptions]=3D3
>
> 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. =A0One of the fields
> > I have criteria for is a yes/no field called invalidEmail. =A0The value=
s
> > are yes if the email is invalid; no if the email is valid. =A0In 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. =A0I tried a form checkbox where in the query, the criterion i=
s
> > 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. =A0So I tried the following
> > statement:
> > IIf([forms].[fmQryByClinic].[emailOptions]=3D1,True,IIf([forms].
> > [fmQryByClinic].[emailOptions]=3D2,False,"*"))
> > Access doesn't like this if [emailOptions]=3D3, which is my intended
> > "show all" button, or if no options are selected. =A0The 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]=3D1,True,IIf([forms].
> > [fmQryByClinic].[emailOptions]=3D2,False,IIf([forms].[fmQryByClinic].
> > [emailOptions]=3D3,"*","*"))
> > Access doesn't like that either, same error.
>
> > Does anyone know what I'm missing here?
>
> > Thanks,
> > Dara- Hide quoted text -
>
> - Show quoted text -

Your suggestion worked perfectly, thanks a ton!  Now I have to stare
at it and figure out why... :)
Dara
-1
rocketD
12/4/2009 10:00:44 PM
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

rocketD wrote:
> On Dec 4, 1:55 pm, John Spencer <spen...@chpdm.edu> wrote:
>> 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 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 -
>> - Show quoted text -
> 
> Your suggestion worked perfectly, thanks a ton!  Now I have to stare
> at it and figure out why... :)
> Dara
0
John
12/5/2009 6:30:40 PM
On Dec 5, 11:30=A0am, John Spencer <spen...@chpdm.edu> wrote:
> Explanation:
> The first test is applied against your field.
> IIF([forms]![fmQryByClinic]![emailOptions]=3D1,True,IIF([forms]![fmQryByC=
lini=AD=ADc]![emailOptions]=3D2,False,Null))
>
> In a Where clause
> WHERE [YourField] =3D
> IIF([forms]![fmQryByClinic]![emailOptions]=3D1,True,IIF([forms]![fmQryByC=
lini=AD=ADc]![emailOptions]=3D2,False,Null))
>
> After the IIF is evaluated that is going to be
> WHERE [YourField]=3D 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 =A0[forms]![fmQryByClinic]![emailOptions]=3D3
>
> Records are returned when the WHERE clause evaluates to TRUE
>
> SO
> WHERE ([YourField] =3D
> IIF([forms]![fmQryByClinic]![emailOptions]=3D1,True,IIF([forms]![fmQryByC=
lini=AD=ADc]![emailOptions]=3D2,False,Null))
> OR [forms]![fmQryByClinic]![emailOptions] =3D 3)
> evaluates to return the desired records.
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
>
>
> rocketD wrote:
> > On Dec 4, 1:55 pm, John Spencer <spen...@chpdm.edu> wrote:
> >> If you enter the following into a criteria cell under your field you s=
hould
> >> get the desired results. =A0It will get restructured in the query desi=
gn view
> >> when you save the query.
>
> >> IIF([forms]![fmQryByClinic]![emailOptions]=3D1,True,IIF([forms]![fmQry=
ByClini=AD=ADc]![emailOptions]=3D2,False,Null))
> >> OR [forms]![fmQryByClinic]![emailOptions]=3D3
>
> >> 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. =A0One of the fiel=
ds
> >>> I have criteria for is a yes/no field called invalidEmail. =A0The val=
ues
> >>> are yes if the email is invalid; no if the email is valid. =A0In 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. =A0I 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. =A0So I tried the following
> >>> statement:
> >>> IIf([forms].[fmQryByClinic].[emailOptions]=3D1,True,IIf([forms].
> >>> [fmQryByClinic].[emailOptions]=3D2,False,"*"))
> >>> Access doesn't like this if [emailOptions]=3D3, which is my intended
> >>> "show all" button, or if no options are selected. =A0The error messag=
e
> >>> 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]=3D1,True,IIf([forms].
> >>> [fmQryByClinic].[emailOptions]=3D2,False,IIf([forms].[fmQryByClinic].
> >>> [emailOptions]=3D3,"*","*"))
> >>> Access doesn't like that either, same error.
> >>> Does anyone know what I'm missing here?
> >>> Thanks,
> >>> Dara- Hide quoted text -
> >> - Show quoted text -
>
> > Your suggestion worked perfectly, thanks a ton! =A0Now I have to stare
> > at it and figure out why... :)
> > Dara- Hide quoted text -
>
> - Show quoted text -

Excellent explanation. Thank you for taking the time.

Dara
-1
rocketD
12/9/2009 3:36:35 PM
Reply:

Similar Artilces: