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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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!!


"Gary Walter" wrote:


"KT Huggs" wrote:
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 ([Table].[Field])=4 Or ([Table].[Field])=8 Or
([Table].[Field])=17
(Where [Table].[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,
[Table].[Field] = Forms![FormName]![ListCtrl],
Forms![FormName]![RadioButtonCtrl1] = 2,
[Table].[Field] IN (4, 8, 17),
True,
True);

good luck,

gary




.

Quantcast