CODEWITHSUNDEEP
ms-access
Degan
Degan

Reputation: 989

How to output a boolean from a query

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?

Upvotes: 5

Views: 12148

Answers (6)

Danh
Danh

Reputation: 1

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

Upvotes: 0

Alex
Alex

Reputation: 2389

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

Upvotes: 1

Nicolas
Nicolas

Reputation: 412

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.

Upvotes: 1

HansUp
HansUp

Reputation: 97111

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.

Upvotes: 2

Tony Toews
Tony Toews

Reputation: 7882

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

Upvotes: 0

Knox
Knox

Reputation: 2919

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.

Upvotes: 0

Related Questions