I have a query with the following IIF statement

OnlineEnrolment: IIf([qry_ebs_final_CAN-ENROL-ONLINE].[SP_CAN_ENROL]='Y',True,False)

It gives the correct results, but the format that it gives them is what I am struggling with. It responds back with 0 or -1. I want it to respond TRUE or FALSE.

How can I change this other than making TRUE and FALSE strings? I still want them as boolean values.



  • 1
    0 and -1 are how Access represents boolean values. What's wrong with that? If this is for display purposes, then you'll need to convert the values to strings anyway to make the UI independent of the database representation. – Jon Seigel Jul 2 '13 at 15:05

This is how TRUE and FALSE work in VBA (and Access): https://stackoverflow.com/questions/4275800/what-are-the-integer-values-of-boolean-false-and-true-in-vb6

If you want a 1 and a 0 like most other programming languages, return 1 and 0 and evaluate that instead of using the keywords TRUE and FALSE. That will make it consistent with other languages, but it won't be internally consistent with Access/VBA.

Something like this:

| improve this answer | |
  • so the whole workflow where this fits in is. There is a table which has a number of fields which are Data Type Yes/No and format True/False so that in the form they appear as tick boxes. Then the query that includes the above IIF statement outputs in conjunction with the table into an excel spreadsheet. Depending on whether the true/false comes from the field or the query, it displays either right aligned or left.. – Wheelz Jul 3 '13 at 7:34
  • In Excel, what do the results look like in terms of their differences? An example might help illustrate the problem. – efesar Jul 3 '13 at 15:12
  • They are the same, just the ones which come from a table are right aligned and the ones which come from the IIF query are left aligned. – Wheelz Jul 4 '13 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.