Returning True, False, and True Or False records

I have a field called "Wiring" in a query which is boolean. If I place "True
Or False" in the criteria for the field, I get all records (there are no
nulls in the recordset). Likewise if I place "True" or "False" I get the
correct records.
I have an option group on a form which has "Yes" and "No" radio buttons, but
whose default value is Null. What I'm trying to do is get the underlying
query to deliver records based on the user selection in the option group. If
one of the radio buttons is selected, the form's records will reflect that
selection. But the form will open with that group's value set to Null and I
want to deliver all the records, both true and false. So I have the following
criteria for the "Wiring" field in the query, which of course doesn't work:

IIf(IsNull([Forms]![frmNewMatrix]![grpWiring]),True Or False,[Forms]!
[frmNewMatrix]![grpWiring])

--
Message posted via http://www.accessmonster.com

re: Returning True, False, and True Or False records

PS:

The value of the radio buttons (and consequently, "grpWiring") - "Yes" = -1,
and "No" = 0.

--
Message posted via http://www.accessmonster.com

re: Returning True, False, and True Or False records

"Bill Reed via AccessMonster.com" <forum@AccessMonster.com> wrote in message
news:509FBB4F74280@AccessMonster.com...[color=blue]
> PS:
>
> The value of the radio buttons (and consequently, "grpWiring") - "Yes"
> = -1,
> and "No" = 0.
>[/color]
I would be tempted to ditch trying to do this on the query grid and try some
code in the in the option group's 'on update' event (untested):

If Me.grpWiring = 0 Then
Me.Recordsource = "qryNo"
Else
Me.Recordsource = "qryYes"
End If

You'd need three queries (Yes, No and All) but consider this - what if your
user want to show all records *after* making a selection in your option
group? You can't "null" the OG without re-opening the form, so maybe the
"All" condition should be a third selectable option in the group.

Hope this makes sense!

Regards,
Keith.
www.keithwilby.com

re: Returning True, False, and True Or False records

Yes, I was just trying to avoid that. There are 4 other option groups on the
form! Although they are not boolean and might be more accommodating in terms
of query criteria (although I'm not sure I'll ever find a solution to "all of
the above" in the option group).

Thanks for your suggestion,

Bill

--
Message posted via http://www.accessmonster.com

re: Returning True, False, and True Or False records

I found another post dealing with this and have a solution that works... sort
of! I use the following in the criteria of the query in the boolean "Wiring"
field:

Like IIf([Forms]![frmNewMatrix]![grpWiring]=1,"*",[Forms]![frmNewMatrix]!
[grpWiring])

When I have the form open, if grpWiring is the default selection of 1 ("Both")
the query returns all the records (hooray). Likewise, "Yes" (-1) and "No" (0)
return the right records. The problem is, it's the underlying query of the
form, but the form doesn't display ANY records!

Bill

--
Message posted via http://www.accessmonster.com

re: Returning True, False, and True Or False records

Bill Reed via AccessMonster.com wrote:
[color=blue]
> (although I'm not sure I'll ever find a solution to "all of
> the above" in the option group).[/color]

If you don't, your users *will* ask you for an "all of the above"
option, I guarantee it. Keith's post is the way to go. You need to do
some coding to get around this. The other method you're suggesting is a
bandaid solution and more trouble than it's worth.

Actually, by far the best way, in my opinion, is to write the SQL you
want in code, based on your user's choices. In other words, let them
click and choose from option groups and whatever you have there to their
hearts' content. Then, when they press a button, a procedure rewrites
the SQL for the underlying query. This way, you have one query, not
several, but the where clause is rewritten, depending upon the choices
made.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Returning True, False, and True Or False records

"Bill Reed via AccessMonster.com" <forum@AccessMonster.com>
wrote in news:50A0F96117FA0@AccessMonster.com:
[color=blue]
> I found another post dealing with this and have a solution
> that works... sort of! I use the following in the criteria of
> the query in the boolean "Wiring" field:
>
> Like
> IIf([Forms]![frmNewMatrix]![grpWiring]=1,"*",[Forms]![/color]
[frmNewMat[color=blue]
> rix]! [grpWiring])
>
> When I have the form open, if grpWiring is the default
> selection of 1 ("Both") the query returns all the records
> (hooray). Likewise, "Yes" (-1) and "No" (0) return the right
> records. The problem is, it's the underlying query of the
> form, but the form doesn't display ANY records!
>
> Bill
>[/color]

so you have the form just operate as a selector to determine the
query? Or is it that the query works, but doesn't repopulate the
form?


--
Bob Quintal

PA is y I've altered my email address.

re: Returning True, False, and True Or False records

I didn't get around to thanking you all for your help.

THANK YOU!!


I ended up following Keith's advice in the end.

Bill Reed wrote:[color=blue]
>PS:
>
>The value of the radio buttons (and consequently, "grpWiring") - "Yes" = -1,
>and "No" = 0.[/color]


--
Message posted via http://www.accessmonster.com