Solved

Reg. Yes/No field value display in UNION query

Posted on 2004-08-20
5
565 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
  • 2
  • 2
5 Comments
 
LVL 34

Assisted Solution

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

Dave
0
 
LVL 9

Accepted Solution

by:
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...

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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