By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

Help with (il)logic in option group/combo box/query

P: n/a
Access 97.

I have a form where there is an option group with two buttons, and a
combo box. The combo box Row Source is a query. The option group has
two options a) include a subset of the query where all records with
the field OPEN set to Yes, or return all records in the query (OPEN =
Yes or No).

If I "manually" set the criteria to 1 (yes) or 1 Or 2, I get the
correct records returned. However, I want to use the option group
value to tell the query which records to return, If I try to set this
up in an IIF statement, it doesn't work.

IIf([Forms]![frmIASSelect]![frOpAcct]=1,-1,-1 Or 0)

It seems IIF doesn't want to evaluate the Or. If I leave the
-falsepart- out of the IIF, it's evaluated to Null. I also tried the
IF THEN in a function, but passing the "-1 or 0" doesn't work as a
single variable, and passing it as a string won't work in the query.

Am I missing something? Is there a simple way to do this?

Thanks.

-Steve-
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Where to start...

OK, the idea of indicating criteria such as [Field] = 0 or -1 is a convenience
of the query editor, and not something handled at a lower level. If you look
at the generated SQL for that, you See [Field] = 0 or [Field] = -1.

I think what really makes sense here is something like [Field] <> False Or
[Forms]![frmIASSelect]![frOpAcct]=2. In the query builder, that's something
like...

[Field] | [Forms]![frmIASSelect]![frOpAcct]
|
<> False |
| 2

This returns rows if either the field is True (<> False is a more reliable
check), -or- if frOpAcct equals 2. Thus, if frOpAcct is 2, then all recordse
will be returned regardless of the state of the field.

On 13 Sep 2004 12:29:23 -0700, hb***@uiuc.edu (Steve) wrote:
Access 97.

I have a form where there is an option group with two buttons, and a
combo box. The combo box Row Source is a query. The option group has
two options a) include a subset of the query where all records with
the field OPEN set to Yes, or return all records in the query (OPEN =
Yes or No).

If I "manually" set the criteria to 1 (yes) or 1 Or 2, I get the
correct records returned. However, I want to use the option group
value to tell the query which records to return, If I try to set this
up in an IIF statement, it doesn't work.

IIf([Forms]![frmIASSelect]![frOpAcct]=1,-1,-1 Or 0)

It seems IIF doesn't want to evaluate the Or. If I leave the
-falsepart- out of the IIF, it's evaluated to Null. I also tried the
IF THEN in a function, but passing the "-1 or 0" doesn't work as a
single variable, and passing it as a string won't work in the query.

Am I missing something? Is there a simple way to do this?

Thanks.

-Steve-


Nov 13 '05 #2

P: n/a
Bingo! After one hammers on something for so long the obvious answers
become obscured. Thanks for the help clearing up the fog.

-Steve-
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<sr********************************@4ax.com>. ..
Where to start...

OK, the idea of indicating criteria such as [Field] = 0 or -1 is a convenience
of the query editor, and not something handled at a lower level. If you look
at the generated SQL for that, you See [Field] = 0 or [Field] = -1.

I think what really makes sense here is something like [Field] <> False Or
[Forms]![frmIASSelect]![frOpAcct]=2. In the query builder, that's something
like...

[Field] | [Forms]![frmIASSelect]![frOpAcct]
|
<> False |
| 2

This returns rows if either the field is True (<> False is a more reliable
check), -or- if frOpAcct equals 2. Thus, if frOpAcct is 2, then all recordse
will be returned regardless of the state of the field.

On 13 Sep 2004 12:29:23 -0700, hb***@uiuc.edu (Steve) wrote:
Access 97.

I have a form where there is an option group with two buttons, and a
combo box. The combo box Row Source is a query. The option group has
two options a) include a subset of the query where all records with
the field OPEN set to Yes, or return all records in the query (OPEN =
Yes or No).

If I "manually" set the criteria to 1 (yes) or 1 Or 2, I get the
correct records returned. However, I want to use the option group
value to tell the query which records to return, If I try to set this
up in an IIF statement, it doesn't work.

IIf([Forms]![frmIASSelect]![frOpAcct]=1,-1,-1 Or 0)

It seems IIF doesn't want to evaluate the Or. If I leave the
-falsepart- out of the IIF, it's evaluated to Null. I also tried the
IF THEN in a function, but passing the "-1 or 0" doesn't work as a
single variable, and passing it as a string won't work in the query.

Am I missing something? Is there a simple way to do this?

Thanks.

-Steve-

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.