Solved

Use Query to add Checkbox field

Posted on 2013-10-25
10
399 Views
Last Modified: 2013-10-28
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
Comment
Question by:pmacafee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 39602049
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39602294
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
 

Author Comment

by:pmacafee
ID: 39602660
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39602731
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39602791
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
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 250 total points
ID: 39602861
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
 

Author Comment

by:pmacafee
ID: 39603911
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39604489
Yes,
Depending on what you need exactly, both pf our approaches are valid, so you can slpit the points if you like.

;-)

Jeff
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39607234
There are generally about three ways to do anything in Access -- if one doesn't work, try another!
0
 

Author Closing Comment

by:pmacafee
ID: 39607478
Had to pick one as best when both were equally good.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question