Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Thanks

Kieran

share|improve this question
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
add comment

1 Answer

This is how TRUE and FALSE work in VBA (and Access): http://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:

    IIf([qry_ebs_final_CAN-ENROL-ONLINE].[SP_CAN_ENROL]='Y',1,0)
share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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