How to output a boolean from a query

Question

In MS Access 2007 (using Access 2000 format) I am attempting to get a boolean output from a query such that the result is displayed as a checkbox rather than 0 or -1.

When the query is passing along a boolean input, this is working properly. When I attempt to make an evaluation, the result is 0 or -1, but not a TRUE/FALSE checkbox.

I have tried:

SELECT (MyInt > 1) AS MyBoolResult
SELECT CBool(MyInt > 1) AS MyBoolResult
SELECT IIF((MyInt > 1), TRUE, FALSE) AS MyBoolResult
SELECT CBool(IIF((MyInt > 1), TRUE, FALSE)) AS AS MyBoolResult

In each case my result is a 0 or -1, and I cannot get this into a display checkbox down stream in an ASP .Net web page using a GridView.

Is what I want possible? If so, how is it accomplished?

Answer 1

With your query in design view, click in your field expression box, then press Alt+F11 to bring up the property sheet. If you then switch to the Lookup tab, you can see there are only 3 three choices for Display Control: Text Box; List Box; and Combo Box. You can type in Check Box, but it won't be accepted.

So, you can't have a calculated field expression displayed as a check box in the result set from an Access 2007 query.

Update: I may have misinformed you. If you do have a Check Box option for Display Control, choose it. (I don't have that choice available on my system, but I'm second guessing whether my system is normal.)

If you want something displayed other than -1 or 0, you could try entering True/False (or Yes/No) in the Format box on the field's property sheet.