?
Solved

Reg. Yes/No field value display in UNION query

Posted on 2004-08-20
5
Medium Priority
?
611 Views
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

Laks.R
0
Comment
Question by:laks_win
[X]
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
5 Comments
 
LVL 34

Assisted Solution

by:flavo
flavo earned 300 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...

Dave
0
 
LVL 9

Accepted Solution

by:
solution46 earned 300 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...

IIf([yesnofield],"Yes","No")

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;

s46.
0
 

Author Comment

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

regards
Laks
0
 
LVL 34

Expert Comment

by:flavo
ID: 11867172
Cheers mate.

Good luck with your project..

Dave
0
 
LVL 9

Expert Comment

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

s46.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 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