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.
LVL 5
jjacksnAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rockiroadsConnect With a Mentor Commented:
The closest I know is perhaps to use IIF

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

0
 
Jim HornConnect With a Mentor Microsoft 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
harfangConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.