Solved

Use Query to add Checkbox field

Posted on 2013-10-25
10
395 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help finding download OneNote 2016 or lates version 7 50
help with an excel problem 10 29
Access Changing Number to Date with Seperator 5 21
Access check if a table is open 4 41
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
My experience with Windows 10 over a one year period and suggestions for smooth operation
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 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