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

Returning True, False, and True Or False records

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
PS:

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

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #2

P: n/a
"Bill Reed via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:50***********@AccessMonster.com...
PS:

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

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
Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

P: n/a
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
Nov 13 '05 #5

P: n/a
Bill Reed via AccessMonster.com wrote:
(although I'm not sure I'll ever find a solution to "all of
the above" in the option group).


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
Nov 13 '05 #6

P: n/a
"Bill Reed via AccessMonster.com" <fo***@AccessMonster.com>
wrote in news:50***********@AccessMonster.com:
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]! [frmNewMat 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


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.
Nov 13 '05 #7

P: n/a
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:
PS:

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

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.