Query sometimes creates its own [useless] expression??!!

[KT Huggs]

I am attempting to design a query that will look up records from a single
category or a preset group of categories. I have created a form where the
user may select a category from a list box OR they may select another control
that is SUPPOSED to indicate a preset group of categories (e.g. the
categories with the primary keys of 4, 8 and 17).

If I create an expression for the criteria field in my query (Note:
[RadioButtonCtrl1] is the control that selects the list box choice on the
form)
= IIf (Forms![FormName]![RadioButtonCtrl1], Forms![FormName]![ListCtrl],8)
then it will either return the records for the selected category or the
query will return records for catgory 8 if the preset group control is
selected on the form - as it should.

If I test that my expression for the preset combination only is correct by
creating the expression = 4 Or 8 Or 17, then that also works as expected and
returns all records in those three categories.

BUT when I try and combine both expressions by typing
= IIf (Forms![FormName]![RadioButtonCtrl1], Forms![FormName]![ListCtrl],4 Or
8 Or 17)
then it does not work when I select the "preset group" control and returns
no records. (The list box part works fine.) When I go back to the query,
then I see it has modified the "falsepart" section of the IIf function.
Instead of reading 4 Or 8 Or 17,
it now reads (

.[Field])=4 Or (

.[Field])=8 Or
(

.[Field])=17
(Where

.[Field] is in fact the table and field that the query is
looking up in that column.)

It does the same thing if the 4 Or 8 Or 17 expression is the truepart of the
function as well.

I'm sorry this is wordy, but I really don't know what is going on. It to me
seems that the full expression should function as I wrote it without bugs.
Can someone please tell me what Access is doing and why, and how I can build
an expression to do what I want without Access changing it?

Thanks!!!

 

[Gary Walter]

I am attempting to design a query that will look up records from a single
category or a preset group of categories. I have created a form where the
user may select a category from a list box OR they may select another
control
that is SUPPOSED to indicate a preset group of categories (e.g. the
categories with the primary keys of 4, 8 and 17).

If I create an expression for the criteria field in my query (Note:
[RadioButtonCtrl1] is the control that selects the list box choice on the
form)
= IIf (Forms![FormName]![RadioButtonCtrl1], Forms![FormName]![ListCtrl],8)
then it will either return the records for the selected category or the
query will return records for catgory 8 if the preset group control is
selected on the form - as it should.

If I test that my expression for the preset combination only is correct by
creating the expression = 4 Or 8 Or 17, then that also works as expected
and
returns all records in those three categories.

BUT when I try and combine both expressions by typing
= IIf (Forms![FormName]![RadioButtonCtrl1], Forms![FormName]![ListCtrl],4
Or
8 Or 17)
then it does not work when I select the "preset group" control and returns
no records. (The list box part works fine.) When I go back to the query,
then I see it has modified the "falsepart" section of the IIf function.
Instead of reading 4 Or 8 Or 17,
it now reads (

.[Field])=4 Or (

.[Field])=8 Or
(

.[Field])=17
(Where

.[Field] is in fact the table and field that the query is
looking up in that column.)

It does the same thing if the 4 Or 8 Or 17 expression is the truepart of
the
function as well.

I'm sorry this is wordy, but I really don't know what is going on. It to
me
seems that the full expression should function as I wrote it without bugs.
Can someone please tell me what Access is doing and why, and how I can
build
an expression to do what I want without Access changing it?

Thanks!!!

Try editing your SQL (in SQL View, instead of messing with Criteria
line in Design View where Access thinks it knows best).

Depending on what option values are, try something like

WHERE
SWITCH(Forms![FormName]![RadioButtonCtrl1] = 1,

.[Field] = Forms![FormName]![ListCtrl],
Forms![FormName]![RadioButtonCtrl1] = 2,

.[Field] IN (4, 8, 17),
True,
True);

good luck,

gary

 

[KT Huggs]

Thank you for your response. However, I am a mostly self-taught Access user
and the only thing I know about SQL is that it stands for Structured Query
Language. I know how to get to SQL view, but can you please

1) explain all the parts of the SWITCH function that you used as well as how
the IN operator works
and/or
2) tell me where I could go for a good reference on SQL and how to use it?
(The reference can assume that I am vaguely familiar with computer languages
in general but utterly amateur when it comes to SQL.)

Thanks again!!

I am attempting to design a query that will look up records from a single
category or a preset group of categories. I have created a form where the
user may select a category from a list box OR they may select another
control
that is SUPPOSED to indicate a preset group of categories (e.g. the
categories with the primary keys of 4, 8 and 17).

If I create an expression for the criteria field in my query (Note:
[RadioButtonCtrl1] is the control that selects the list box choice on the
form)
= IIf (Forms![FormName]![RadioButtonCtrl1], Forms![FormName]![ListCtrl],8)
then it will either return the records for the selected category or the
query will return records for catgory 8 if the preset group control is
selected on the form - as it should.

If I test that my expression for the preset combination only is correct by
creating the expression = 4 Or 8 Or 17, then that also works as expected
and
returns all records in those three categories.

BUT when I try and combine both expressions by typing
= IIf (Forms![FormName]![RadioButtonCtrl1], Forms![FormName]![ListCtrl],4
Or
8 Or 17)
then it does not work when I select the "preset group" control and returns
no records. (The list box part works fine.) When I go back to the query,
then I see it has modified the "falsepart" section of the IIf function.
Instead of reading 4 Or 8 Or 17,
it now reads (

.[Field])=4 Or (

.[Field])=8 Or
(

.[Field])=17
(Where

.[Field] is in fact the table and field that the query is
looking up in that column.)

It does the same thing if the 4 Or 8 Or 17 expression is the truepart of
the
function as well.

I'm sorry this is wordy, but I really don't know what is going on. It to
me
seems that the full expression should function as I wrote it without bugs.
Can someone please tell me what Access is doing and why, and how I can
build
an expression to do what I want without Access changing it?

Thanks!!!

Try editing your SQL (in SQL View, instead of messing with Criteria
line in Design View where Access thinks it knows best).

Depending on what option values are, try something like

WHERE
SWITCH(Forms![FormName]![RadioButtonCtrl1] = 1,

.[Field] = Forms![FormName]![ListCtrl],
Forms![FormName]![RadioButtonCtrl1] = 2,

.[Field] IN (4, 8, 17),
True,
True);

good luck,

gary

 

[Gary Walter]

Thank you for your response. However, I am a mostly self-taught Access
user
and the only thing I know about SQL is that it stands for Structured Query
Language. I know how to get to SQL view, but can you please

1) explain all the parts of the SWITCH function that you used as well as
how
the IN operator works
and/or

What version of Access?

For ex,. in Access2002 Help Content tab, choose topic
Programming in Visual Basic /
Visual Basic Language Reference /
Functions /
Q-Z /
Switch Function

Another quick way to lookup some things in Help Content tab,

Microsoft Jet SQL Reference /
Overview /
SQL Reserved Words /
In
-- will get you to definition of In Operator

2) tell me where I could go for a good reference on SQL and how to use it?
(The reference can assume that I am vaguely familiar with computer
languages
in general but utterly amateur when it comes to SQL.)

I would think John Viescas's "SQL For Mere Mortals"
might be a good start for you (at least I got it for my
nephew in college and it helped him alot with Access class).
I have seen John referred to as the "granddaddy of
Access MVP's."

Or, in Help Content tab,

Microsoft Jet SQL Reference /
Data Manipulation Language /
SELECT Statement

{at bottom "See Also" you get links to
FROM clause
WHERE clause
(IN clause -- not same as In Operator)
ORDER BY clause}

Thanks again!!

I am attempting to design a query that will look up records from a
single
category or a preset group of categories. I have created a form where
the
user may select a category from a list box OR they may select another
control
that is SUPPOSED to indicate a preset group of categories (e.g. the
categories with the primary keys of 4, 8 and 17).

If I create an expression for the criteria field in my query (Note:
[RadioButtonCtrl1] is the control that selects the list box choice on
the
form)
= IIf (Forms![FormName]![RadioButtonCtrl1],
Forms![FormName]![ListCtrl],8)
then it will either return the records for the selected category or the
query will return records for catgory 8 if the preset group control is
selected on the form - as it should.

If I test that my expression for the preset combination only is correct
by
creating the expression = 4 Or 8 Or 17, then that also works as
expected
and
returns all records in those three categories.

BUT when I try and combine both expressions by typing
= IIf (Forms![FormName]![RadioButtonCtrl1],
Forms![FormName]![ListCtrl],4
Or
8 Or 17)
then it does not work when I select the "preset group" control and
returns
no records. (The list box part works fine.) When I go back to the
query,
then I see it has modified the "falsepart" section of the IIf function.
Instead of reading 4 Or 8 Or 17,
it now reads (

.[Field])=4 Or (

.[Field])=8 Or
(

.[Field])=17
(Where

.[Field] is in fact the table and field that the query is
looking up in that column.)

It does the same thing if the 4 Or 8 Or 17 expression is the truepart
of
the
function as well.

I'm sorry this is wordy, but I really don't know what is going on. It
to
me
seems that the full expression should function as I wrote it without
bugs.
Can someone please tell me what Access is doing and why, and how I can
build
an expression to do what I want without Access changing it?

Thanks!!!

Try editing your SQL (in SQL View, instead of messing with Criteria
line in Design View where Access thinks it knows best).

Depending on what option values are, try something like

WHERE
SWITCH(Forms![FormName]![RadioButtonCtrl1] = 1,

.[Field] = Forms![FormName]![ListCtrl],
Forms![FormName]![RadioButtonCtrl1] = 2,

.[Field] IN (4, 8, 17),
True,
True);

good luck,

gary

 

[Gary Walter]

Some have posted here that this site was a great help:

http://www.devguru.com/Technologies/jetsql/quickref/select.html