5

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?

Improve this question

6 Answers 6

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Uh, @HansUp, you really need to fire up Access and try this again. CheckBox is, in fact, one of the choices and ListBox is not. Please edit your answer so I can vote it up as the correct answer.
@David, Did you try with Access 2007? I tried before posting, and Check Box is not among the choices for Display Control on a calculated field expression. A2007 SP2
Ultimately, the value of displaying it as a checkbox is that the Gridview in the ASP .Net web page that will display the data can use a checkbox. Forms will not help me. My Lookup tab "Display Control" has the values: Text Box, List Box, Combo Box. My General tab "Format" has the values: General Number Currency, Euro, Fixed, Standard, Percent, Scientific. Nothing like "Yes/No" or "True/False"
The format box doesn't display "Yes/No" or "True/False" among the choices, but you can type it in anyway (without the quotes).
The format box changes the display in Access (to a literal "Yes", "No", "True, or "False"). I have not been able to get the query to display a check-box, nor the native GridView function (which appears as an integer). If I really need a check box, I guess I will use a template field.
1

I was hoping to comment on HansUp's answer, but that seems to be unavailable...

My own experience in Access 2007 is as per HansUp's original response - the Lookup > Display Control setting only offers me Text Box; List Box; and Combo Box.

Improve this answer

Comments

1

I ran into the same problem when creating queries to export Access-Data to JSON. The values ended up as numbers, not as booleans. The only way I could get around this problem programmatically was to have the Javascript part of the import reconvert the data type. A bad hack but I see no other way

Improve this answer

Comments

0

That's the way it works and I'm unable to produce the results you want. The only way I know is to produce a temporary table.

Improve this answer

Comments

0

Possibly ASP .Net is expecting a +1 as being the true value. Try putting an ABS on the logical expression. Just guessing though.

Improve this answer

Comments

0

The only way I found to make MyBoolResult a checkbox is the following:

  1. Create a new (empty) radio button
  2. Change it into a checkbox (by right mouse button and "Change to")
  3. Put MyBoolResult as its Contol Source property
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.