Access Data Type Conversion to Boolean

Question

Hi All.

I am performing a query on the following table:

Approval

• person (long int)
• item (long int)
• reason (long int)
• date (date)
• valid (bool)

The first 3 fields are linked to other tables. Each person may have any number of entries in this table, for various items, reasons or dates. I then perform the following query:

SELECT Approval.person, 
        Approval.item,
        Approval.reason, 
        Min(Approval.valid) AS valid
FROM Approval
GROUP BY Approval.person, 
        Approval.item, 
        Approval.reason;

Why? Because I am not only interested in "who has valid status", but also "who ever had an approval for this item or reason, and is this approval still valid?"

Now, this works - well, sort of. However, because I have performed a Minimum aggregate function, I have automatically changed the data-type from Yes/No to Numerical (I used the minimum aggregate function simply because I knew of no other better way to do it).

So, the question is, how do I change the numbers -1 & 0 back into a boolean value of True/False? Or, is there a better way of doing this query?

Best regards, Phil

Answer 1

Hi Phil

The function CBool() will change the number back into True or False