MSACCESS Is there a way for a query with a Boolean field to echo the string representation of the Boolean without taking way it's boolean type?

2
votes

Is there a way in a query for Microsoft Access to have a field that tests likeness of two fields and return the string representation of "True" or "False" instead of a -1 or 0 without loosing the boolean type?

 SQL SYNTAX
 [Table1].[Field1]=[Table2].[Field1] AS Compare

I would like it to be easy something like 

 Cstr([Table1].[Field1]=[Table2].[Field1])

Although I know I could just write a function in VBA to achieve the same effect I'm wondering if there is already a way to represent a Boolean with it's string value without taking away the Boolean nature from it's type.

And I also want to avoid doing

 iff([Table1].[Field1]=[Table2].[Field1], "True", "False")

As although it will visually do the job, it will mean that I will have to make any programmatic comparisons look for the string value of "True" "true" "TRUE"... instead of it's intended function as a yes/no in the first place.

I mainly find this a minor annoyance since in the intermediate window of VBA I can clearly see the string value like this

 debug.? "a"="a"
 True

And I don't get why it's not consistent.

1
ms-accessboolean

1 Answers

5
votes

You can set the Format property to True/False for your query's boolean field expression.

Property Sheet for query field showing Format property

True/False is not included in the choices available in the Format drop down box, but you can type it in.

Applying that format to both field expressions in this query ...

SELECT (1=1) AS boolean_True, (1=2) AS boolean_False
FROM Dual;

... gives me this result in Datasheet View:

boolean_True boolean_False
------------ -------------
        True         False

The underlying values are still Access booleans (integer -1 or 0). Just the presentation of those values is changed.