Solved

Use Query to add Checkbox field

Posted on 2013-10-25
10
392 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

746 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

10 Experts available now in Live!

Get 1:1 Help Now