Go Premium for a chance to win a PS4. Enter to Win

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

How to select cast to boolean

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
jjacksn
Asked:
jjacksn
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
rockiroadsCommented:
The closest I know is perhaps to use IIF

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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(too slow)
0
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!

 
harfangCommented:
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
 
harfangCommented:
Or, for that matter:

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

Cheers!
(°v°)
0
 
ala_frostyCommented:
For speed, I think I'd vote with rockiroads and jimhorn's suggestions
0
 
ala_frostyCommented:
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
 
harfangCommented:
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
 
harfangCommented:
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
 
ala_frostyCommented:
There's a forest around here somewhere .. if I could only see 'round these pesky trees ...
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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