I want a calculated field in a query to appear as a check-box. How can this be done?

[Thadeus1991]

Good morning everybody,



I have written the following expression in a query, asking the question if the difference between a date and today is 12 months or less:

12_month_limit: IIf(DateDiff("m",[Most recent audit date],Now())<=12,True,False)

The formula works and outputs either 0 or -1.

However, I would like the output to appear as a checkbox in the query, rather than 0 & -1. When looking at the property sheet section Lookup --> Display Control, currently "Text Box" is selected. The other options are "List Box" and "Combo Box". Unfortunately, check-box does not appear.

Does anyone know how to change the output to check boxes, rather than 0 &-1?

Thank you for your help!!

[Bob Fitz]

Might be better not to open the query directly but use the query as the Record Source of a form. The calculated field could then be used as the Control Source of a check box control.
FWIW I would never allow users direct access to a query. I consider that to be a recipe for disaster.

[Thadeus1991]

Might be better not to open the query directly but use the query as the Record Source of a form. The calculated field could then be used as the Control Source of a check box control.
FWIW I would never allow users direct access to a query. I consider that to be a recipe for disaster.

Thanks for the tip!

I agree with you: I would not allow users access to queries either - that could be disastrous. However, the database is used by me and a colleague (both of us with similar skill levels of Access) only.
I'd prefer to use a query rather than a form since queries can be adjusted very easily and quickly to get an Excel output (e.g. hide or show certain fields).

If creating a form is the only way, I could, of course, do that but it'd be a lot more usable and convenient in my case if somehow the formatting of the query field can be changed to check-box!

[Bob Fitz]

Have you considered using a datasheet type of form. The columns are easily hidden.

[Thadeus1991]

Have you considered using a datasheet type of form. The columns are easily hidden.

If I did that, would the Excel output look the same as with a query?

In particular:
If I export a query with tick boxes (not from a calculated field but simply from a table), they appear in Excel as "TRUE" or "FALSE" and are interpreted by Excel as numbers, not text.
Would this be the same from a report with tick boxes?

[Bob Fitz]

If I export a query with tick boxes (not from a calculated field but simply from a table), they appear in Excel as "TRUE" or "FALSE" and are interpreted by Excel as numbers, not text.
Would this be the same from a report with tick boxes?

I don't know. Perhaps you could try it and let us know the outcome.

[Thadeus1991]

Just tried it, unfortunately, they are exported as -1 or 0, not TRUE/FALSE.

[Bob Fitz]

What results do you get if you change

12_month_limit: IIf(DateDiff("m",[Most recent audit date],Now())<=12,True,False)

To

12_month_limit: IIf(DateDiff("m",[Most recent audit date],Now())<=12,"True","False")

[Thadeus1991]

It does appear as True and False in the query output as well as in Excel.

Excel does however interpret these as words, not as values, so I cannot do arithmetic with them :-/