Solved

Reg. Yes/No field value display in UNION query

Posted on 2004-08-20
5
589 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 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

732 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