Yes/No field in a report parameter form?

[Owl]

I have a report whose parameters are collected in a form. I want to add a
Yes/No field to it.

Question 1
Is there any way of doing this? I know that Yes/No fields are frowned upon.

Question 2
If the above is not possible, I would like to keep my Yes/No field as such
elsewhere, but would like to know if it is possible to change it to a text
field in the query of the above report, for the purpose of this report.

Thank you for any help.

 

[Marshall Barton]

I have a report whose parameters are collected in a form. I want to add a
Yes/No field to it.

Question 1
Is there any way of doing this? I know that Yes/No fields are frowned upon.

That is nonsense, there is nothing wrong with using Yes/No
fields when they meet your database's business rules.

Add a Yes/No type control (check nox, radio button or toggle
button) to your form and add associated code to the form or
parameter to your query just like you did for your existing
numeric parameters,

Question 2
If the above is not possible, I would like to keep my Yes/No field as such
elsewhere, but would like to know if it is possible to change it to a text
field in the query of the above report, for the purpose of this report.

This makes no sense to me. Why would you want to convert a
Yes/No value to Text. If you want to display a Yes/No value
as Yes or No in a text box in a form or report, just set the
text box's Format property to Yes/No.

 

[Owl]

Thank you very much for your reply, Marshall.

Please will you help me further. I don't know what code to add to the query
for the Yes/No field.

My parameter form is called Parameter Collector1 and my Yes/No field is
called Done.

 

[Marshall Barton]

Thank you very much for your reply, Marshall.

Please will you help me further. I don't know what code to add to the query
for the Yes/No field.

My parameter form is called Parameter Collector1 and my Yes/No field is
called Done.

And you are using the check box on the form as what in the
query?

If you want to select records that are not done, then set
the Done field's criteria to:

Not Forms![Parameter Collector1].[check box on form]

 

[Duane Hookom]

Perhaps Owl has read a previous posting by me that suggests Yes/No fields are
very often mis-used. I try to avoid them like the plague.

What had worked for storing [Active]: Yes/No often should be a [Status]
field based on a small lookup table since Active and Not Active no longer
describe all possible values.

I also have seen the Yes/No field used in an un-normalized structure where
several Yes/No fields should actually be related records in a child table.
For instance, a person might be on one or more mailing lists. I quite often
see Yes/No fields with names like:
[ChristmasCard], [Family], [Business],... etc.
Each of these fields should actually create a new record in a related table.

You really have to be careful when up-sizing Yes/No fields to SQL Server.
Make sure you set a default value.

With the size of most hard-drives, I would rather use an integer or byte
field.
--
Duane Hookom
Microsoft Access MVP

Thank you very much for your reply, Marshall.

Please will you help me further. I don't know what code to add to the query
for the Yes/No field.

My parameter form is called Parameter Collector1 and my Yes/No field is
called Done.

And you are using the check box on the form as what in the
query?

If you want to select records that are not done, then set
the Done field's criteria to:

Not Forms![Parameter Collector1].[check box on form]

 

[Owl]

Thank you, Marshall. Could you help me a little further, please.

(My check box on the form is called Done.) I tried what you said (I copied
it into the Criteria of the Done field in the query), but I couldn't get it
to work (In the report, I selected with and then without checking the Done
check box and both resulted in a non-report). I then tried it without the
Not and it brought up the correct results when I checked the Done check box,
but when I tried it without checking the Done check box it resulted in an
error (a non-report). What am I doing wrong?

Thank you very much for your reply, Marshall.

Please will you help me further. I don't know what code to add to the query
for the Yes/No field.

My parameter form is called Parameter Collector1 and my Yes/No field is
called Done.

And you are using the check box on the form as what in the
query?

If you want to select records that are not done, then set
the Done field's criteria to:

Not Forms![Parameter Collector1].[check box on form]

 

[Owl]

Thank you for your response, Duane. I am going to try to fathom it all out
properly and will probably ask you some questions, but first I want to sort
out my above problem.

Perhaps Owl has read a previous posting by me that suggests Yes/No fields are
very often mis-used. I try to avoid them like the plague.

What had worked for storing [Active]: Yes/No often should be a [Status]
field based on a small lookup table since Active and Not Active no longer
describe all possible values.

I also have seen the Yes/No field used in an un-normalized structure where
several Yes/No fields should actually be related records in a child table.
For instance, a person might be on one or more mailing lists. I quite often
see Yes/No fields with names like:
[ChristmasCard], [Family], [Business],... etc.
Each of these fields should actually create a new record in a related table.

You really have to be careful when up-sizing Yes/No fields to SQL Server.
Make sure you set a default value.

With the size of most hard-drives, I would rather use an integer or byte
field.
--
Duane Hookom
Microsoft Access MVP

Thank you very much for your reply, Marshall.

Please will you help me further. I don't know what code to add to the query
for the Yes/No field.

My parameter form is called Parameter Collector1 and my Yes/No field is
called Done.

And you are using the check box on the form as what in the
query?

If you want to select records that are not done, then set
the Done field's criteria to:

Not Forms![Parameter Collector1].[check box on form]

 

[Owl]

Thank you, Marshall. It is working perfectly now. I had unbound fields in
the form instead of bound ones. I tried bound ones and hey presto, that
wonderful exciting Microsoft Access moment when IT WORKS!

Thank you very much for your reply, Marshall.

Please will you help me further. I don't know what code to add to the query
for the Yes/No field.

My parameter form is called Parameter Collector1 and my Yes/No field is
called Done.

And you are using the check box on the form as what in the
query?

If you want to select records that are not done, then set
the Done field's criteria to:

Not Forms![Parameter Collector1].[check box on form]

 

[Owl]

HA! Sorted out the problem, and so now I can attend to your reply. (Smiles)

I don't remember that particular posting, although I read every Discussions
Group posting that I can possibly find the time for, for the sheer pleasure
of learning something new on Access. I have read of several people who avoid
Yes/No fields. If you have the time, could you elaborate on all you said in
your response in this thread, please? Thank you for your time.

Perhaps Owl has read a previous posting by me that suggests Yes/No fields are
very often mis-used. I try to avoid them like the plague.

What had worked for storing [Active]: Yes/No often should be a [Status]
field based on a small lookup table since Active and Not Active no longer
describe all possible values.

I also have seen the Yes/No field used in an un-normalized structure where
several Yes/No fields should actually be related records in a child table.
For instance, a person might be on one or more mailing lists. I quite often
see Yes/No fields with names like:
[ChristmasCard], [Family], [Business],... etc.
Each of these fields should actually create a new record in a related table.

You really have to be careful when up-sizing Yes/No fields to SQL Server.
Make sure you set a default value.

With the size of most hard-drives, I would rather use an integer or byte
field.
--
Duane Hookom
Microsoft Access MVP

Thank you very much for your reply, Marshall.

Please will you help me further. I don't know what code to add to the query
for the Yes/No field.

My parameter form is called Parameter Collector1 and my Yes/No field is
called Done.

And you are using the check box on the form as what in the
query?

If you want to select records that are not done, then set
the Done field's criteria to:

Not Forms![Parameter Collector1].[check box on form]

 

[Duane Hookom]

I'm not sure I can "elaborate on all (I) said".

Your yes/no field name is [Done]. I assume this field is used to store
something like if a task is Done or Not Done. What happens when the
pointy-haired-boss comes along and wants to know the percent done? You might
have been better off using an integer field that stores the percent done. The
functionality of Done or Not Done is still available as an integer with 100
being done and every other value as not done.


--
Duane Hookom
Microsoft Access MVP

HA! Sorted out the problem, and so now I can attend to your reply. (Smiles)

I don't remember that particular posting, although I read every Discussions
Group posting that I can possibly find the time for, for the sheer pleasure
of learning something new on Access. I have read of several people who avoid
Yes/No fields. If you have the time, could you elaborate on all you said in
your response in this thread, please? Thank you for your time.

Perhaps Owl has read a previous posting by me that suggests Yes/No fields are
very often mis-used. I try to avoid them like the plague.

What had worked for storing [Active]: Yes/No often should be a [Status]
field based on a small lookup table since Active and Not Active no longer
describe all possible values.

I also have seen the Yes/No field used in an un-normalized structure where
several Yes/No fields should actually be related records in a child table.
For instance, a person might be on one or more mailing lists. I quite often
see Yes/No fields with names like:
[ChristmasCard], [Family], [Business],... etc.
Each of these fields should actually create a new record in a related table.

You really have to be careful when up-sizing Yes/No fields to SQL Server.
Make sure you set a default value.

With the size of most hard-drives, I would rather use an integer or byte
field.
--
Duane Hookom
Microsoft Access MVP

Owl wrote:

Thank you very much for your reply, Marshall.

Please will you help me further. I don't know what code to add to the query
for the Yes/No field.

My parameter form is called Parameter Collector1 and my Yes/No field is
called Done.


And you are using the check box on the form as what in the
query?

If you want to select records that are not done, then set
the Done field's criteria to:

Not Forms![Parameter Collector1].[check box on form]

 

[Marshall Barton]

Glad you got it sorted out.

When you said Yes/No fields should be avoided and I
responded with they're fine IF they meet your business
needs, I did not elaborate because you had not explained the
business need served by the Done field in the table.

Duane has postulated a scenario where a simple done or not
done may not be sufficient in the future. There are a *lot*
of times when a simple yes or no is a short sighted view of
the world an application is trying to model.

Duane provided an excellent example of how you should think
about **all** stated business needs when they are presented
to you. Regardless of what is said about the immediate
needs, you can save a tremendous amout of future work and
hair pulling, if you extrapolate each stated need out to
potential feature requests in the future. Then build your
application to deal with at least the ones that don't
require a lot of addition work in the version you are
currently developing.
--
Marsh
MVP [MS Access]

Thank you, Marshall. It is working perfectly now. I had unbound fields in
the form instead of bound ones. I tried bound ones and hey presto, that
wonderful exciting Microsoft Access moment when IT WORKS!

Thank you very much for your reply, Marshall.

Please will you help me further. I don't know what code to add to the query
for the Yes/No field.

My parameter form is called Parameter Collector1 and my Yes/No field is
called Done.

And you are using the check box on the form as what in the
query?

If you want to select records that are not done, then set
the Done field's criteria to:

Not Forms![Parameter Collector1].[check box on form]