Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

Reg. Yes/No field value display in UNION query

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
laks_win
Asked:
laks_win
  • 2
  • 2
2 Solutions
 
flavoCommented:
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
 
solution46Commented:
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
 
laks_winAuthor Commented:
thank u Both a lot. I am clear now.

regards
Laks
0
 
flavoCommented:
Cheers mate.

Good luck with your project..

Dave
0
 
solution46Commented:
Glad to help, Laks. Cheers for the nod.

s46.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now