Office & Productivity Software forum

Resolved Question

[Access 2010] Change default formats for data types

by cag8f / September 27, 2014 8:54 PM PDT

Whenever I import data, the default format for Boolean data types is "-1,0." I know how to subsequently change this format to "Yes/No," but I'd like to change the default setting to "Yes/No." Is this possible?

Thanks in advance.

cag8f has chosen the best answer to their question. View answer
Discussion is locked
You are posting a reply to: [Access 2010] Change default formats for data types
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: [Access 2010] Change default formats for data types
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.

All Answers

Best Answer chosen by cag8f

Collapse -
Re: import
by Kees_B Forum moderator / September 27, 2014 9:13 PM PDT

If I create a new table in an Access 2010 .accdb file, and make a Boolean field, the default notation is true/false, which I can change to yes/no or on/off. No mention on "-1/0" at all. With importing, it might depend on where you import from. You didn't tell, so I can't try.

But I don't think you can change the built-in defaults.


Collapse -
Importing from CSV
by cag8f / September 27, 2014 10:14 PM PDT
In reply to: Re: import

Thanks for the reply. I'm importing from a CSV file.

Collapse -
Re: csv-file
by Kees_B Forum moderator / September 28, 2014 11:05 PM PDT
In reply to: Importing from CSV

I did a little bit of experiment, and that's the way it works. The import makes the new table, since it's smart enough to recognise the contents of this columns as boolean values it makes a boolean field in the new table and it sets the properties of the boolean field to show as 0 and -1. As you noticed, it's easy to change to another mode of showing.

Of course, this only is relevant if you import to a new table. If you import to an existing tables, the existing properties of all fields are kept. For one time operations, I wouldn't mind so much, for repeating operations you better use an existing table anyhow. So it's a very minor (indeed cosmetic) annoyance you'll have to live with.


Collapse -
In programming parlance
by Jimmy Greystone / September 28, 2014 1:52 AM PDT

In programming parlance, there's really no difference between any of these things. Generally 0 means false and any non-zero value means true. So my guess would be that the CSV file you're importing from is using numeric values to indicate the boolean disposition.

Make a copy of the CSV file and then open that copy in Notepad or any other text editor really. Use the Replace All function to find -1 values and then replace that with "Yes" and then repeat the process only with 0/No. Then try importing the modified CSV file and see what happens. Be sure to give it a pretty thorough going over because there's always the chance that your modifications hit something else. You may find you need to go back and do something like search for ",0," and replace that with ",No," so if you had some other field that was say "1000000" you don't wind up with "1NoNoNoNoNoNo" etc. This is why you make the copy. That way you can always delete it and make a new copy until you've worked everything out.

Collapse -
by cag8f / September 28, 2014 11:07 AM PDT

Thanks for your assistance. My CSV has these values set to "Yes" or "No," yet Access sets these to 0, -1 after import.

Collapse -
As stated
by Jimmy Greystone / September 28, 2014 10:51 PM PDT
In reply to: Thanks

As stated, there's functionally no difference between them. You're only offered the different options in Access for cosmetic reasons because they all evaluate to either 0 (false) or non-zero (true). If you wanted to build a query for a database, you could use those values interchangeably if you're trying to search based on a boolean field. Of course it never hurts to be sure, so load the file into Access and then just make three very simple queries where you know what the outcome should be or can easily verify by looking at the table created when importing. Make one query using true/false, one using yes/no, and one using -1/0. All three should return exactly the same results.

If the above works, you can just use yes/no or true false later and it will function exactly the same. You just have a small, purely cosmetic, issue where the data is -1/0 instead of yes/no, but it's still exactly the same.

Collapse -
Not the point
by cag8f / September 29, 2014 12:10 AM PDT
In reply to: As stated

Thanks for your help. I understand that they are all functionally the same. But I am teaching teenagers how to use Access for the first time. The book from which they learn uses Access, but in their examples, Boolean fields are displayed as Yes/No. So I'd like Access to display Yes/No for them automatically upon import.

Collapse -
More to the point?
by R. Proffitt Forum moderator / September 29, 2014 12:18 AM PDT
In reply to: Not the point

One of the fun areas of working with data in general is to not fret so much about this import. That is, what mattered in the past was the REPORT on said data.

I could make the REPORT show Yes/No on the printout, PDF or Web display and the underlying data be 1, 0, true/false or other.

Yes we could write some Access code to add a column that would use the source data to make a Yes/No but only a beginner would do that.

Collapse -
Au contraire!
by Jimmy Greystone / September 29, 2014 1:55 AM PDT
In reply to: Not the point

Au contraire! I'd say that makes it all the more relevant to the point. Here's a perfect opportunity to teach these students how to deal with unexpected situations. In the real world, the data you get will be full of errors and you'll have to be able to clean it up. You might have a database that's very old and has been maintained by multiple people. The first person may have preferred 0/1 for true/false, the next person might have used yes/no, etc. You will rarely have nice clean scrubbed data like you get from textbooks and sometimes even there you'll run into unexpected and/or peculiar behavior between versions. So here's your chance to go beyond the textbook and teach these kinds how to deal with a simple unexpected situation.

Frankly, I'd think any good educator would have seized on this opportunity already, though with the way teachers are made into scapegoats for virtually everything these days and school budgets have cut so deep into the bone they've practically nothing left, I wouldn't blame you if you've developed kind of PTSD.

View this as the opportunity it is, not a problem. The one thing I had to teach myself pretty much all the way through school was critical thinking. All the way through college, the classes were great at giving me the facts, but not so much at teaching me how to use those facts. So here's your chance to help rectify that problem with one group of students. It's not much, granted, but it is what you as an individual can do to help make the world a slightly better place and give this group of students (along with others who may follow) a small edge over everyone else when trying to find a job. If you plan the seed of how to think critically and logically work through and/or around problems that come up, it's something that can be applied to virtually any other area of life.

Collapse -
by cag8f / September 29, 2014 2:21 AM PDT
In reply to: Au contraire!

I appreciate all the enthusiasm. But really I just asked if something small was possible in Access. You're going off on philosophical tangents regarding how I should be educating our future leaders. I hear what you're saying, and I will take the advice to heart. Thanks for your help.

Collapse -
Yes it's possible.
by R. Proffitt Forum moderator / September 29, 2014 2:41 AM PDT
In reply to: Thanks

That is, to write a little code to clean it up or read the file and add records exactly like you wish. Some courses are limited to only what menus a software title has. But Access has us open to writing bits of code to fill in where the menus fall short.

Collapse -
by cag8f / September 29, 2014 8:51 AM PDT
In reply to: Yes it's possible.

OK thanks. If I were to pursue the writing-code route, what would be my next step?

Collapse -
Example to follow.
by R. Proffitt Forum moderator / September 29, 2014 9:34 AM PDT
In reply to: OK
Collapse -
by cag8f / September 29, 2014 12:32 PM PDT
In reply to: Example to follow.

Thanks. This is good to know, but is probably beyond the scope of my present issue. I'll pursue a separate workaround.

Popular Forums
Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions


Help, my PC with Windows 10 won't shut down properly

Since upgrading to Windows 10 my computer won't shut down properly. I use the menu button shutdown and the screen goes blank, but the system does not fully shut down. The only way to get it to shut down is to hold the physical power button down till it shuts down. Any suggestions?