How to create Access 2013 field with only 2 possible values

Normally, the way you would do this is to have a form for entering the data into the table.  Instead of having a textbox as the data entry control, you would use a combo box.  You could create a separate table (tbl_ResumeLanguage) with fields:

LanguageCD and Language
EN     English
DE     Deutsch

and then use that table as the source for your combo box, or you could use a ValueList for the combo box with RowSource: "EN", "English", "DE", "Deutsch"

Then, you would set the BoundColumn = 0, ColumnCount = 2, and ColumnWidths = 0, 1

Finally, you would add code to the forms BeforeUpdate event which ensures that the column is filled in.  Something like

Private Sub Form_BeforeUpdate(Cancel as Integer)

    if me.cbo_Language & "" = "" Then
        Cancel = True
        msgbox "Select a value from the Language dropdown"
    End If

End Sub

Select all Open in new window

I'll present an alternative strategy.

You could have the drop-down box embedded into your table.

Go into your table design mode, click on the relevant field, and click on the second tab.

Once you change Display Control to (say) list box, then you can enter your mini-table values there.

I would have Column Count as 2, and have the Bound Column as 2, so you could have

Deutsch.    DE
English.      EN

You could change the Column Widths to (say) 3 cm; 0 cm, so that the second column is not visible, but is the one bound.

See http://probiztechnology.com/blog/2012/a-students-access-question-quick-easy-dropdown-lists/ for more information.

I strongly discourage:

1.  Allowing users direct access to your tables.  Users should only access data through forms.

2.  Use of the feature that Phillip recommends above.  This can be quite confusing to users because the value they see when viewing the table would be "English" or "Deutsch", but what is actually stored is "EN" or "DE".  I cannot tell you how many times I have encountered this when trying to help someone who is trying to filter their table and has either forgotten, or does not know that the actual data stored in the table is "EN"/"DE" and they are trying to filter on "English" or "Deutsch"

This field represents a flag for a mail-merge effort to automate cover letter customization
If are you driving the mail merge with a query instead of a table, then why not put a parameter into the quer(ies)
One query has the field = "Deutsch", and another query had the field = "English"
When you do the merge, you pick the appropriate query for the results you want.

If you're going to filter on it, then it matters little how you accomplish it. Whether put in a lookup table with "Deutsch" = 1 and "English" = 2 and then filter by 1 or by 2, or you filter by the text value is little practical difference.

In theoretical terms the lookup table is the preferred method.  You could then change the names to "Englisch" and "German" with little hassle in a single locale.  And filtering on a number field is the preferred method over filtering on text in a larger context

I have a mini-app to manage simple lookup tables like this.  I import it into each new application so I don't have to reinvent the wheel.  It consists of two tables, four forms, and two reports.  I've attached a few pictures.  I tie it in with security because some tables can be updated by anyone or a supervisor or just IT.  It empowers the user and relieves me of having to manage the code lists.  The downside is that the codes stored in your tables will be meaningless numbers so unless you memorize hundreds of unique values, you won't be able to "see" them when you look directly at a table so you'll need to use a query so you can see the decoded values.  I include a long desc and a short desc because you usually want to see the long description in the combos but for reports, you may be squeezed for space and so you can use the short desc.

And I'll definitely side with @Dale Fye about what belongs in tables.
Real values.
Text and Numbers

The minute you start putting controls like checkboxes , yes/no, listboxes, comboboxes other assorted  things is the moment you voluntarily opened the gates of hell and went for a stroll.  You WANT the tables to show you the real values stored.  That way, you can design queries and criteria based on what you see. The minute the tables become a place for presentation it becomes hellacious to figure out what's truly there.  Forms and reports are the places for pretty presentation.

Not tables.

Checkboxes represent true/false values (-1 or 0).  What is hellacious about that?  In queries, if you want true you look for TRUE.  If you don't want true, you look for FALSE.  If you need a third value - null (unknown), you should not use a Yes/No data type.

One way:

Just modify the field properties in table.

Default Value: "EN"
Validation Rule: ="EN" Or ="DE"

To make a selection between English and German to a selection between True and False equals selecting a bad design.
It may work, but that's no indication of a good design.

Happy New Year.
Gustav

We were not discussing making this a y/n design.  We were discussing, in general, the use of Yes/No columns in a table.

@PatHartman
What is hellacious about that?
Build an app.
Use checkboxes instead of numerics in table.
Forget to disallow null for the column or set a default, since you've got that looked after in code/forms.
Hand that app to a user with enough knowledge to want to expand or innovate on it, but not a power user.
How do you put an unchecked checkbox in the Query Editor?
Or why, when you put 'Yes' in there that the query returns nothing?
These are all things that the user will then need to discover, that you already know.
And easily avoidable.

You can ensure that the table displays the values that actually exist in it.
And it's odd.
In my app, with the same SQL Server backend some of my bit columns, in Access, show 0 and 1, while others show 0 and -1
Hell, in the same table I've got bit fields that some of them show 0 and 1 and, others show 0 and -1
And when I grab all three in a query, they are then all 0 and -1.  Filtering by TRUE, though doesn't consistently lead to the results I expect.  And fails when I least expect it.  And DESPITE the fact that in the query's datasheet view shows 0 and -1, I CANNOT filter by -1.  But I can filter all three by 1 Which is a surprising result to say the least--since Access is showing me -1.

Not surprising is that I can create bulletproof filters with = 0 and <> 0.
Your mileage clearly varies from mine.  That's nice.  But since you clearly cannot make a bulletproof case for the infallibility of TRUE/FALSE in the way you'd like to handle it, I am not sure what the objection is.

And at any rate, it's all about creating apps that are maintainable over the long run.  How does adding controls to tables aid in that regard?  It doesn't.  It's one of the many things MS has flanged into Access to make it 'friendlier' for the end user.  And one of those things -- like hyperlink fields, or permitting subdatasheets -- that it makes long-run sense to avoid.

Build an app.
  ..Will hundreds of apps and thousands of tables do?
 Use checkboxes instead of numerics in table.
  ..Whenever it makes sense (i.e. I only have TWO values!)
 Forget to disallow null for the column or set a default, since you've got that looked after in code/forms.
   ..It is my job to not forget details like this, otherwise the client might as well do it himself or hire an amateur.
 Hand that app to a user with enough knowledge to want to expand or innovate on it, but not a power user.
   ..Not a good idea, but OK
 How do you put an unchecked checkbox in the Query Editor?
  ..?? As I mentioned earlier, if you define a Yes/No data type, it is your responsibility to ensure that it doesn't allow null.  If you want to allow null, you ALWAYS have to consider null whenever you are searching for not True.
 Or why, when you put 'Yes' in there that the query returns nothing?
  .. Because "Yes" isn't what is stored.  -1 and 0 are what is stored but you can refer to them as True or False.  The only time you would ever see "Yes" is if you changed the display type to NOT be a text box.
 These are all things that the user will then need to discover, that you already know.
  .. I would never put him in that position.
 And easily avoidable.
  .. Absolutely

 You can ensure that the table displays the values that actually exist in it.
  .. It will
 And it's odd.
 In my app, with the same SQL Server backend some of my bit columns, in Access, show 0 and 1, while others show 0 and -1
 .. Most likely the table with the "-1" was converted from Jet/ACE and the "1" table was made in SQL Server.  That is why when using Access, using True and False avoids the issue.
 Hell, in the same table I've got bit fields that some of them show 0 and 1 and, others show 0 and -1
 .. That really doesn't have anything to do with displaying a checkbox.
 And when I grab all three in a query, they are then all 0 and -1.  Filtering by TRUE, though doesn't consistently lead to the results I expect.  And fails when I least expect it.  
.. What are you expecting to happen that doesn't happen?
And DESPITE the fact that in the query's datasheet view shows 0 and -1, I CANNOT filter by -1.  But I can filter all three by 1 Which is a surprising result to say the least--since Access is showing me -1.
..  Access is definitely confusing the issue which is one more reason to use True and False.  NOT "True" and  "Not True"
..  In a database linked to SQL Server, I would NEVER use a filter.  I don't want Access to bring down all the rows and filter locally.  That defeats the purpose of using SQL Server.  It is best to use queries with criteria.

 Not surprising is that I can create bulletproof filters with = 0 and <> 0.
 Your mileage clearly varies from mine.  That's nice.  But since you clearly cannot make a bulletproof case for the infallibility of TRUE/FALSE in the way you'd like to handle it, I am not sure what the objection is.  When I allow users to "filter" forms, I have the querydef refer to the unbound selection fields when the selection is a simple one or two fields.  When the selection is complex as it can be for reports, I build the SQL on the fly.

 And at any rate, it's all about creating apps that are maintainable over the long run.  How does adding controls  to tables aid in that regard?  
..  You are not adding a control to a table.  You are "allowing" Access to display data in a logical way.  Most people think that checkboxes are intuitive and easy to read.  They don't want to look at 0 and -1, especially users.  If you don't want to see a checkbox, DON'T use a Yes/No data type, use an Integer.

It doesn't.  It's one of the many things MS has flanged into Access to make it 'friendlier' for the end user.  And one of those things -- like hyperlink fields, or permitting subdatasheets -- that it makes long-run sense to avoid.

Your whole argument revolves around avoiding something because you or someone else has misused it in the past.
Let's agree to disagree on another topic.

@Gus,
Apparently I missed a couple of posts and thought you were referring to the exchange between Nick and me.  I agree 100% that Yes/No fields should be used ONLY when the field poses a question that can be answered by Yes/No or True/False and NEVER to indicate 1 of 2 choices such as English or German.

Your whole argument revolves around avoiding something because you or someone else has misused it in the past.
Yup, misused it isn't quite right, but created/inherited something that makes a Boolean field a bit of a landmine.

And when I say 'filter' in the context here (which is discussing getting an Access query to behave) that is synonymous with a criteria or WHERE clause

I understand that in the wide scope of the universe there are many ways for the WHERE clause of a SQL statement in Access to do things that the query's creator did not anticipate when a Boolean field is in play, like when
a) Null gets involved
b) backends change or get intermixed
c) linked tables to other files/datasources get involved.
d) local tables and linked tables to SQL Server get intermixed
e) you converted your backend to SQL Server back in the day when Access DIDN'T deal properly with bit fields (been there, done that) and so you converted your bit fields to int fields (and maybe back again! after proper support for SQL Server's TRUE value been 1 was added)
f) any number of other things.

I get it.
You'd like to advise everyone to test for TRUE and FALSE when you're using a Boolean field as a criteria.
..  Access is definitely confusing the issue which is one more reason to use True and False.  NOT "True" and  "Not True"
Indeed, Access is definitely confusing the issue -- which is an absolute, undeniably, reliably, universally and permanently good reason to test for =0 and <>0 because absolutely nothing -- Not Access, Not Excel, Not SQL Server and Not Oracle either -- gets confused about that.  False is universally 0.

You don't like negation logic.  Fair enough.  You would recommend not using negation logic unless it serves a purpose.  Fair enough.  It almost sounds like you've been tortured with some hideously complex WHERE NOT EXISTS logic somewhere along the line.  Fair enough.  Be in the business long enough and you'll develop tricks, habits and patterns and want to share those with other and with fervor occasionally (Like folks who date back to the A97 era will religiously rename controls that have the same name as a fieldname -- despite that now being an MS Access default and never having caused grief post A2003.)  Fair enough.

 Filtering by TRUE, though doesn't consistently lead to the results I expect.  And fails when I least expect it.  
.. What are you expecting to happen that doesn't happen?
Gee, I don't know.  I expect that TRUE should return all values that are NOT FALSE.  That's the expected and intuitive result, on my part.  Mathematically that's the expected result.  But I have learned that no, TRUE does not necessarily equal NOT FALSE.  I still think instinctively that TRUE = NOT FALSE and NOT TRUE = FALSE -- but practically, I know that can be incorrect depending upon circumstances, usually very difficult to detect circumstances yet to boot.

Let's agree to disagree on another topic.
Sure.  I am not sure why you chimed at the point you did.  At that point there was no bone of contention about the 'proper' way to create query criteria on Boolean fields.  You brought that up.  My point is that in the query editor there's no option to put a checkbox in the criteria row -- so checkboxes in the datasheet is just damn confusing.

Who in their right minds would recommend checkboxes / yes-no and/or lookup fields in tables after the first time they had confused the hell out of themselves when trying to create query criteria?  I don't think you recommend 'controls' in tables either.  You can use checkboxes and/or Yes/No formatting in the table.  Doesn't mean it's a good idea.  Neither are lookup fields.  Neither is applying formats to DateTime fields until you truly understand what you are actually storing and what that formatting is really doing.

I was reinforcing @Dale Fye's comment @ID: 40525262
Keep your tables for the true values of your data, and don't muck with presentation there.
It's confusing over the very long run.

I expect that TRUE should return all values that are NOT FALSE

Wrong, Wrong, Wrong IF you have allowed those pesky nulls to creep in.

permanently good reason to test for =0 and <>0 because absolutely nothing -- Not Access, Not Excel, Not SQL Server and Not Oracle either -- gets confused about that

Wrong again once you let nulls creep in.

I am not sure why you chimed at the point you did

You were  talking about the "badness" of certain elements, one of which was checkboxes and I disagree.

Who in their right minds would recommend checkboxes / yes-no and/or lookup fields in tables after the first time they had confused the hell out of themselves when trying to create query criteria?

I can assure you that I am in my right mind and I expect you to refrain from getting personal in your argument.

I don't think you recommend 'controls' in tables either

Controls are not stored in tables.  That is simply a clear way of displaying True and False.  It is ONLY if you allow null to muddy the waters that there is any issue at all.  You might say that textboxes are bad also because people unwitting allow ZLS and that causes significantly more problems since it is much more common.

The recurring theme revolves around nulls in Yes/No fields.  Once you understand that Access is wrong to allow them at all, you will define the field as required and give it a default of No or Yes (whichever makes sense) or give them no default and make the user choose.  If nulls can't be saved to the field, all your assumptions about True and False returning the expected rows will be fulfilled.  Even the convoluted use of Not True and Not False will return the expected rows.

HAPPY NEW YEAR!!!!!!

I expect you to refrain from getting personal in your argument.
I never wish to give offense.

Controls are not stored in tables
Call it what you wish.  The appearance to combobox-like and checkbox-like UI elements in the display of a table or query.
I don't find these things to be helpful, at all.  In my opinion, tables and queries are not places to gussie up the presentation of data.

That is simply a clear way of displaying True and False.
I will disagree, politely.  It is a way of obfuscating the stored values of zero for NULL and FALSE and some other number (usually -1 or 1) for TRUE, and in my considered opinion provides neither clarity or simplicity.  We agree to disagree on this point.

It is ONLY if you allow null to muddy the waters that there is any issue at all.
Access permits it.  SQL Server permits it.  Three value logic for checkboxes exists and someone reading this post in the years and decades ahead may need to deal with the issues that occur because, while Access is willing to permit NULL in a Boolean field, it has no present method to display it differently than FALSE.  We do not disagree that the existence of NULL in Boolean fields presents complications

But, someone reading this post may have decided that they had very good reasons for wanting to permit null in their Boolean fields or they may have inherited such circumstances from another developer.  There is a broad scope of experience at play.

It becomes worthwhile to know that in the Venn diagram of the values in a Boolean field, there may be THREE possible values.  You may religiously wish to eradicate the third as an article of faith, much like the old saying 'to use GOTO is to depart from the path of righteousness.'  But that doesn't mean that everyone can or will -- and so, to recognize and guard against the possibility is prudent.  And to provide advice with explanation about how to do so is neither incorrect, nor a sin, nor worthy of a jeremiad.

Once you understand that Access is wrong to allow them at all
That is your considered opinion.  It clearly differs from MS's.  And I doubt very highly that MS will deprecate three-value logic at this point.  Like the t-shirt says: 'NULL happens, man.  Deal with it!'
:D

Nick67

Since you obviously believe that it is OK to allow null in a data type that should only allow true and false, I'll leave it to you to wrangle with the issues that causes.  Just don't complain about not getting the results you expect.

OK. I think I'd like to "normalize" the database by adding the extra table with just the two values and index it to the corresponding job opening table. That seems doable and also appears to allow for further refinement if there's a time in the future when I would need yet another country/language.

So the only question left is.... Can I run the mail merge off a query? And if I understand correctly, I am able to create a query that pulls from multiple tables -- that should be a no-brainer, right?

Thanks for all the help! And, despite the moderately heated discussion, the points made actually helped me to understand the underlying processes better, so thanks for the detailed argumentation.

>> Can I run the mail merge off a query? And if I understand correctly, I am able to create a query that pulls from multiple tables -- that should be a no-brainer, right?

Yes. And if you couldn't, then you Make a Table from the query anyway.

So I create a table with a primary key and 2 other fields: the long form of the language and the short abbreviation.

To create a one-to-one relationship, I just connect the primary key of the language table to the primary key of the job opening table -- is that correct? And I do not need to enforce referential integrity because if a record in the job openings field is updated or deleted, I don't want anything to be updated or deleted in the language table -- is that also correct?

Thanks...

OK. That makes good sense. And though I enforce referential integrity, I don't need to cascade anything because I don't want to make any changes to the language table from data entry in the job openings table. Am I correct in that assumption?

Another thing happened when I did what you said that surprised me a bit. The "one-to-many" is actually a "many-to-one" from the job openings table. That's correct, isn't it?

As a little background, I've never done any database work professionally -- it's always been private. And, believe it or not, I started way back with Borland's Paradox (and WordPerfect's DataPerfect). I missed the FoxBase craze and the dBase world, but have dabbled in Access ever since Office 97. I know just enough to be dangerous.... ;-)

Thanks again for the help.

This is actually clearer...
So then, is this many-to-one between job opening and language tables correct? Can you elaborate?

I understand that I want many possible entries in the phone number tables, or contacts or openings per each company. Do I interpret this as wanting many possible openings for each language? I suppose that makes good sense, but I'm getting that I might not understand relationships quite like I should...

Looks good.
Now for your mail merge(s)
You'll create a couple of queries that pull all the data together that the merge needs, and in one you'll have JOBOpen.LangID = 1 for the English merge and in the other JOBOpen.LangID = 2 for the German merge.

:) LOL Boom!
Another old, old argument is about the use of anything other than an autonumber as a primary key.  Let's not blow up that bridge :)
SInce it is in ruins now anyway...
Long ago I was persuaded by @harfang's analysis of 'natural vs surrogate' keys in @Jim Dettman's article here  The minute a sequential autonumber key is created, it begins to capture information.  Let's say you enter 20 language values.  With an autonumber sequential key, you now know what order you created them in.  Since the key is now capturing information it is no longer strictly 'synthetic' since it has meaning and relationship to the data.
And indexes on numeric data perform better than indexes on text.
And an autonumber CANNOT be changed -- which I like.
And when you look at raw data you won't see anything immediately useful--which indicates that another table is involved that has the useful data in it's most compact form.  When you do see meaningful data in a table, you know you are looking at its repository.
But that's me.

Clearly @PatHartman and I (and many, many others) differ in this.  And that's ok.  Choose what makes sense for you, your data and your application.

I understand that I want many possible entries in the phone number tables, or contacts or openings per each company. Do I interpret this as wanting many possible openings for each language? I suppose that makes good sense, but I'm getting that I might not understand relationships quite like I should...

It's always funny how Access creates relationship visually.
It does look backward.
Try deleting/recreating it.
Drag LangID from JOBLang and drop it on LangID in JOBOpen.
Does it reverse?

OK. You've seen the relationships. So if I do stay with the autonumber primary key, I'm still not sure how to link it in the form.

I have 2 subforms on tabs that appear in the main form -- one for contacts and one for openings. They are both in tabular(?) format; like a normal datasheet with rows for records and columns for fields. The question is, then, how do I get the field to function as a lookup field for what's listed in the languages table? (So that when I add a job opening record, the language field appears as a drop-down -- perhaps with a default value already listed -- that pulls the long name values from the language table. Does that make sense?

What I did was to use "Lookup Wizard..." under Data Type and it created the relationship for me. I now have the lookup possibilities when I look at the table in datasheet view. I'm hoping that will be automatically pulled over when I create the subform. Does it sound like I did that right?

So bottom line, I no longer show a one-to-many or many-to-one in relationship view. It's just a single line. But when I edit the relationship, it still shows One-To-Many and it does not enforce referential integrity. Remember, this was the automatically-generated relationship from Access' own wizard.

Oh, and it didn't change anything regardless of which table I dragged it from...

Got it. I tried both ways, but the wizard worked better. The individual instructions were great, but I was left with a blank combo box dropdown. I looked, and just putting in the table name alone for the Row Source didn't work. The wizard built a great little query and that worked fine.

But in any event, I am grateful that I got some better insight into the back end through your explanation. I made sure to rename the combo box (so it can be referred to in other programming, right?). And at the end of the wizard it asked if I wanted to store the value in the table, or just in memory; I stored it in the table in the LangID field...

I sure appreciate the help. I do like how it's shaping up. I'll make sure that I give some answer credit in the next little while...

However, if you use it, you absolutely must change the Name property on the Other tab at the end because you want to have a meaningful name not something like combo133.
Amen that!

Of course, whether to name the control after the field it is bound to, or not,  is another one of those bridges that we need not blow up :)  Have a solid naming convention.  Stick to it.

Nick67