Reg. Yes/No field value display in UNION query

Posted on 2004-08-20
Last Modified: 2006-11-17
Hi Experts

I have a field in a table with "Yes/No" data type. While using the simple query for that table the result of the "Yes/No" field is working Ok. But when i combine another table using the "UNION" operator, the value displays for the "Yes/No" field is 0 for False and -1 for True. Why? Please..

Thanx in advance

Question by:laks_win
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 34

Assisted Solution

flavo earned 75 total points
ID: 11857457
That's what Access (and VB and id assume most programimg languages (MATLAB is) and most RDBMS too would all use 0 for false and either 1 or -1 for true) actually stores yes/no and true/false as.  It wouldnt be very efficient to store "Yes" and "No".

As to why it decides to show it like that in a union, im not sure...


Accepted Solution

solution46 earned 75 total points
ID: 11857978
In both cases, as flavo points out, Access stored the info as -1 for yes, 0 for no. The only reason I can think of for one table displaying it as yes/no is that you have some formatting going on somewhere (e.g. Display Control = Text Box; Format = Yes/No). The UNION query is displaying the literal values without any formatting. You could try replacing the yes/no field with...


This will format True (or Yes or -1) as "Yes" and False (or No or 0) as "No". Just tried this and it worked fine. This is the test query I used...
SELECT value, IIf(yesno, "Yes", "No")
FROM yesno1
UNION SELECT value, IIf(yesno, "Yes", "No")
FROM yesno2;


Author Comment

ID: 11867144
thank u Both a lot. I am clear now.

LVL 34

Expert Comment

ID: 11867172
Cheers mate.

Good luck with your project..


Expert Comment

ID: 11872607
Glad to help, Laks. Cheers for the nod.


Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question