Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to select cast to boolean

Posted on 2006-06-21
12
Medium Priority
?
11,219 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 672 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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 664 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 66

Expert Comment

by:Jim Horn
ID: 16954351
(too slow)
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 58

Assisted Solution

by:harfang
harfang earned 664 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

688 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