Solved

How to select cast to boolean

Posted on 2006-06-21
12
11,197 Views
Last Modified: 2008-01-09
Does MS ACCESS support CASE statements like sql?

I basically want to do this:

select customerNo,
(select case count(*) from visits where visits.customerNo = customerNo
          when 0 then false
          else true
          end)
from
Customers

basically, getting a true or false column if they have ever had a visit.
0
Comment
Question by:jjacksn
[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
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 168 total points
ID: 16954342
The closest I know is perhaps to use IIF

SELECT CustomerNo, IIF(Count(*)=0,True,False) AS BoolValue
FROM Visits

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 166 total points
ID: 16954346
>Does MS ACCESS support CASE statements like sql?
No, Access does not use CASE WHEN...THEN...END, only Iff(condition, value if true, value if false)
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16954351
(too slow)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 58

Assisted Solution

by:harfang
harfang earned 166 total points
ID: 16955824
Hello jjacksn

The comparison itself will evaluate to a boolean. You do not need a function call:

select customerNo,
(select count(*)>0 from visits where customerNo = Customers.customerNo) As HasVisits
from Customers

(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16955836
Or, for that matter:

select customerNo,
customerNo In (select customerNo from visits) as HasVisits
from Customers

Cheers!
(°v°)
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 16956827
For speed, I think I'd vote with rockiroads and jimhorn's suggestions
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 16956845
But I think you should write the query like this:

select customerNo, iif(count(*) > 0 , true, false)
from Customers as c
 inner join visits as v
 on c.customerno = v.customerno
group by customerNo

or like this

select customerNo, iif(cc>0, true, false)
FROM (
select customerNo, count(*) as cc
from Customers as c
 inner join visits as v
 on c.customerno = v.customerno
group by customerNo
) as subquery

Evaluate for speed and let us know which is quicker for you.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16956978
I beg to differ, but

    IIf( something > 0, True, False ) As Result

is exactly equivalent to

    ( something > 0 ) As Result

only slower. Why call a VB function to tell us that true is true and false is false?

(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16956995
Also, ala_frosty, if you use an inner join, you will only have customers with visits, and hence the condition will always evaluate to true... It would be like saying: select distinct customerNo from visits.

(°v°)
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 16989467
There's a forest around here somewhere .. if I could only see 'round these pesky trees ...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

735 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