• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

Use Query to add Checkbox field

I remember that there is a way to add a checkbox field in Access's query builder. But now I cannot find anywhere on the web that shows me how to add that field. I am working on a table that I cannot modify and I am currently building a new table by combining one table wih the data and another with a checkbox field. Now I do not want to build the table, i just want to have a form bound to the query. Can anyone help?
0
pmacafee
Asked:
pmacafee
  • 4
  • 3
  • 3
2 Solutions
 
Jeffrey CoachmanMIS LiasonCommented:
<I remember that there is a way to add a checkbox field in Access's query builder.>
Can you post a specific example stating the datatypes as defined in the table?

I am not sure this is possible, If I am understanding what you are asking for.

This can be done in a form\report though, if your data is "Boolean":
Meaning you only have two choices: (0=False=No, ...AnyOtherValue=True=Yes)

Just drop a checkbox on the form/report.
Then set the controlsouce to your Boolean field.


If this is not what you wanted, then please post an example of what you have now, then post a clear graphical example of the exact output you need, based on this example.

Jeff Coachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
I am working on a table that I cannot modify and I am currently building a new table by combining one table wih the data and another with a checkbox field. Now I do not want to build the table, i just want to have a form bound to the query. Can anyone help?

Upon reading this over a few times, I have another idea....
Create a new table with the additional Field(s) you want.
Add in the same PK field as in the original table (*but you do not have to add any records to this table directly*)
Then link these tables on the PK fields, ...but also set the Join type as: "Include All records from the Original table..."

Now create a query with these two tables and bring in all the fields you need from both tables.
Now you will have created a "View", than you can add records to as if all fields were from the same table.
The only drawback is that new record will have a default of "Null" in the checkboxes until you edit them (however you can create code on the form to fix this after the record has been entered.

Sample attached
Access--SampleAddLinkedFieldsToT.mdb
0
 
pmacafeeAuthor Commented:
Jeff, I think your proposed solution is the way that we have solved this up to date. For rather complicated reasons, we want to change the way we deal with this. I want to create a recordset for the form with a query. and use that recordset in another function. I remember for many years ago, that there was a way to use the query builder to add a checkbox field.
I am talking about where you add the Title of the field and then a colon ":" and then parameter's for the field. There was a way of defining the field as Boolean maybe something like Format("Y/N") or something like that.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Helen FeddemaCommented:
Do you mean adding a calculated field to a query, as opposed to adding a checkbox control to a form?  If so, you can do this by defining the field in a make-table query as (say) Available: True, and formatting the column as Yes/No.  This field would not be editable, but the table created when you run the query will have a Number field with a value of Yes (-1).  You could then change the data type to Boolean.
0
 
Jeffrey CoachmanMIS LiasonCommented:
I am talking about where you add the Title of the field and then a colon ":" and then parameter's for the field. There was a way of defining the field as Boolean maybe something like Format("Y/N") or something like that.

This is what is used to create the rowsource for a "Combobox", not a checkbox.
...and you must select from the Lookup tab-->Display Control=Combobox.

So please clarify what you really need here, a combobox(Dropdown) or a Checkbox.

Also please provide more info on this "new field"
Because regardless of how you do it, you still have to add this new field to a new table
...*Unless* this "new" field is really a calculated field based on the same fields that already exist in the table...
If this is a calculated field then tell ous if it will need to be editable or not...


As Helen states you can list the two values as text "Yes" and "No" in the combobox.
The disadvantage is that it is harder to refer to text (Yes/No) than it is to reference a Number
0=No=False
-1=Yes=true

As I stated, you can use the a numeric field in the table then use a true checkbox control in the form.

In other words, if you need only two choices, (Yes/No, ...True/False)
Then use a Boolean (numeric) field and display this as a checkbox in the form/report

The bottom line is that you need to give us some more background info on this "New Field"
What is really is, Why is it needed, what is in used for, ...etc

JeffCoachman
0
 
Helen FeddemaCommented:
You can also add a Boolean field to a table using code like the following:

   CurrentDb.Execute "ALTER TABLE tblTest " _
        & "ADD COLUMN Available YESNO;"

Open in new window

0
 
pmacafeeAuthor Commented:
Helen & Jeff, thank you for all you responses.

I think I have muddled two memories into one. The first creates a new column in a select query and after a little searching around, I found this "PaidFor:Cbool(0)" which adds the Boolean value field to the recordset.
The other memory is to use a make table query.
I think I can accomplish my goal with the first one.
-P
0
 
Jeffrey CoachmanMIS LiasonCommented:
Yes,
Depending on what you need exactly, both pf our approaches are valid, so you can slpit the points if you like.

;-)

Jeff
0
 
Helen FeddemaCommented:
There are generally about three ways to do anything in Access -- if one doesn't work, try another!
0
 
pmacafeeAuthor Commented:
Had to pick one as best when both were equally good.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now