Access: where are my checkboxes?
April 26, 2019 7:47 AM   Subscribe

Thanks to the great help I got in this question, my database queries are now working much better, and all of the records are appearing in the query. However, I can't figure out how to get an SQL query to show checkboxes, rather than 0/-1 as the expression of a yes/no checkbox.

In the non-SQL queries that I used to use, any yes/no checkbox fields would still show in the query as checkboxes (example here).
In the new SQL query, which is working properly to show all of my records, the yes/no checkbox fields from tables are now appearing as 0/-1 (example here). It's not a huge deal, because I know what they mean, but I'd prefer to get it to show those as checkboxes again. I've googled this to no avail, so I'm hoping that some of you database whizzes will have some ideas!
posted by sabotagerabbit to Technology (4 answers total)
Looks to me like you go into the individual field properties, and change the display control to checkbox. May be slightly different, depending on the version.
posted by The_Vegetables at 8:23 AM on April 26, 2019

Looks to me like you go into the individual field properties, and change the display control to checkbox.

There is also a "Format" option in the query designer for a field bound to a boolean type, that gives you some choices" True/False, Yes/No, or On/Off. Right-click the field and choose "Properties" to get to that.

(By the way, I read a blog post once, by some Microsoft old-timer, that explained why a value of true is represented by -1; I think it had something to do with flipping all the bits of zero in 2s complement arithmetic. I don't really care, but I was sort of glad to know that there was an actual reason why).
posted by thelonius at 10:20 AM on April 26, 2019

Many flavors of SQL support a CASE statement, which behaves like a multi-leaf if. If I understand what you're trying to do correctly, you want a column that looks something like this: CASE WHEN val = -1 THEN "NO" ELSE "YES" END
posted by ubiquity at 11:02 AM on April 26, 2019

Mea culpa, I didn't notice at first that you are using Access. In Access, the syntax you want is: SWITCH ( val = 1, 'NO', val = 0, 'YES' ).
posted by ubiquity at 11:09 AM on April 26, 2019

« Older Looking for more 'on brand' working-out-to tv   |   Car mechanics not afraid of rust Newer »

You are not logged in, either login or create an account to post comments