Solved

Use Query to add Checkbox field

Posted on 2013-10-25
10
394 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HasData 9 37
Sum Multiple Columns in Access Query 5 50
SQL profiler equivalent in MS-Access 3 43
MS Access Tables Linking 6 40
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now