yes/no in criteria

[karim]

Hello All,
I have a check box field. I want in a qry in the criteria to be
able to have the option to select fields that are checked or fields that are
not checked. How would I do that in the criteria?

Thanks for the help...

 

[Jeff Boyce]

Karim

I'm not completely clear on how your data is set up...

You mention having "a check box field" ... that sounds like a single field.

You also mention selecting "fields that are checked or fields ..." ... that
sounds like multiple fields.

Does your table have a single checkbox field or multiple checkbox fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[karim]

Hi and thank for the fast replay,

my table has more than one check box field, but i'm working on the option
for only one check box. you know when we write a qry, in the criteria field,
i want the user to be able when opening the report to type in the pop up box
yes/no for that check box. so that if the user typed yes in the pop up box
that appears when first opening the report, it shows all the record that has
that check box CHECKED. if he type NO, it shows all the UNCHECKED check
boxes...

 

[Allen Browne]

It is possible to put an unbound check box on a form, set its Triple State
property to Yes, and then design the query so that it matches the Yes record
if the check box is checked, the No records if it is unchecked, or all
records if it is Null.

In the end, though, this is going to be a considerable amount of work for a
quite poor outcome. The triple state check box is a confusing interface,
since there's no visible difference beween No and Null under recent
versions of Windows. Even worse, the query critieria is long, confusing to
enter, difficult to maintain, inefficient to execute, and may even be
unstable.

It depends on your data, but it may be a much better solution would be to
use a normalized design rather than many check boxes in the one table. For
an example, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

If you really want to do it anyway, you will end up with the WHERE clause of
your query (in SQL View) looking like this:
WHERE (IIf([Forms].[Form1].[Chk1]=True, [MyYesNo1]=True,
IIf([Forms].[Form1].[Chk1]=False, [MyYesNo1]=False, True)))
AND (IIf([Forms].[Form1].[Chk2]=True, [MyYesNo2]=True,
IIf([Forms].[Form1].[Chk2]=False, [MyYesNo2]=False, True)))
AND ...

 

[James A. Fortune]

...
It depends on your data, but it may be a much better solution would be
to use a normalized design rather than many check boxes in the one
table. For an example, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

If you really want to do it anyway, you will end up with the WHERE
clause of your query (in SQL View) looking like this:
WHERE (IIf([Forms].[Form1].[Chk1]=True, [MyYesNo1]=True,
IIf([Forms].[Form1].[Chk1]=False, [MyYesNo1]=False, True)))
AND (IIf([Forms].[Form1].[Chk2]=True, [MyYesNo2]=True,
IIf([Forms].[Form1].[Chk2]=False, [MyYesNo2]=False, True)))
AND ...

That's definitely great advice and a great article. However, the SQL
programmer also needs to be aware that doing things properly with a
many-to-many relationship can cause future SQL creation and maintenance
to become much more difficult if more than a few other tables start
getting joined in along with the many-to-many tables. It's not anything
that can't be overcome, but things can suddenly get a bit more complex
when you have, say, a few many-to-many joins in the mix.

For a small database, I think adding a single many-to-many relationship
is a very good way for an Access programmer to get used to the SQL that
she needs to create.

For a larger database with existing many-to-many relationships, I think
that the database planner should sit down with the schemata to convince
herself that she can create the queries that she knows she'll need in
the near future before adding another many-to-many relationship. That
way, the painful trade-offs that might come into play will become clearer.

That said, based on past experience, it's good to normalize as much as
one's SQL abilities and available time allow.

James A. Fortune
(e-mail address removed)

 

[lenny wong]

Hello,

I have created a Window Mails account, but when I try to send a mail,
windows prompt me POP3 server not found...
Is it because I have created wrongly.
Please help.

 

[Gina Whipp]

Lenny,

You have posted this the Microsoft Access newsgroup. I am not sure how you
got here but I would suggest you look for Windows Live newsgroup where they
would be of much more service.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[lenny wong]

Hello,
Below error message prompted, How to resolve it?


The host 'POP3' could not be found. Please verify that you have entered the
server name correctly.
Subject 'Booking apartment', Account: 'POP3', Server: 'POP3', Protocol:
SMTP, Port: 25, Secure(SSL): No, Socket Error: 11001, Error Number:
0x800CCC0D