Yes/No field from 0/-1 to check box

[roland_access]

Taken over a database from a colleague who has since left. The main table has several Yes/No fields, and the data contained within them is in the raw form 0 or -1, not the default check box appearance. Perhaps this was because the table was created originally from a source such as Excel.

Anyway, I know I can add a new Yes/No field and update its contents with the old one to have the check box style, then delete the old and rename the new, but there are about 10 Yes/No fields and about 100,000 records so it would take a while. Any quicker way of doing it that ive overlooked?

Thanks in advance, using Access 2000

 

[dcx693]

There's no need to do this type of conversion, and in fact, it's not possible. Tables in database systems are for storing "raw" data. Checkboxes are just physical representations of the -1 and 0 values in your table. If you'd rather see them as checkboxes, you can easily create a query where you add all the fields from your table to the query. In Access XP (I'm not sure about the other versions, but can check), the Yes/No fields are presented as checkboxes.

That may solve your problem right there. Be aware that forms are available in Access for the very purpose of displaying data from your tables in different ways, including showing Yes/No fields as checkboxes, radio buttons, toggle buttons, etc....

 

[roland_access]

Oh yes, I know that the data is stored as 0/-1 regardless of how it is displayed in datasheet view. However, I print a lot of one off reports for various departments that are just print outs of query results. Designing an Access report would be a waste of time as its a one off.

The query's datasheet view has -1/0 for the Yes/No fields and this can be confusing to someone who doesnt know that much about data. A simple checkbox is much easier for them to read. Hence my preference to change the way the Yes/No is displayed in datasheet view.

 

[dcx693]

No problem. Just make a small change to your query. In query design view, go to the View menu and choose Properties. That will display the query properties dialog box. Do not click in that box. Instead, click somewhere in the field that you want to display as a checkbox. The query properties box will change to field properties. There is a general and a lookup tab. Click on the lookup tab. In the display control, choose check box and run your query.

With all the yes/no fields that I've used, Access (2000 and 2002) have by default displayed them as checkboxes. Not sure why yours isn't, but there you have it.

 

[roland_access]

Worked perfectly, thank you.

I think the table in question was originally created from an Excel sheet that was exported from a different database. The Yes/No data was therefor stored as 0/-1 in the Excel sheet and Access adopted this method of viewing Yes/No field in the new table in datasheet view as opposed to the default check box.

 

[dcx693]

No prob. Good to know about that possbility with Excel. I've got lots of Excel users.

 

[roland_access]

I fixed the problem by changing the Lookup of each offending field in the table from Text Box to Check Box. Now all queries based off that table will default to check box rather than text box.