[thelist] Conditionals in Access

Ken Schaefer ken at adOpenStatic.com
Fri Oct 31 22:39:24 CST 2003


I think IIF() will do what you want:

IIF(expression, true part, false part)

If expression is true, then true part is returned, else false part is
returned.

Also, IN() is very slow in Access if you have lots of records. Try using an
EXISTS or NOT EXISTS query instead. Also, make sure you have appropriate
indexes defined.

Cheers
Ken

Microsoft MVP - Windows Server (IIS)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Scott Brady" <evolt at scottbrady.net>
Subject: [thelist] Conditionals in Access


:
: This query is incredibly slow (I think it's the combination of the SUM()
and the IN sub-query, because other similar queries that have either of
those (but not both) are nice and speedy.
:
: Worse, I have a ColdFusion page where I'm running this query twice (once
for "IN" and once for "NOT IN"), and I hope there's a better way to do this
in Access.
:
: Ideally, what I'd do is something like this add a column in the main
select that determines the activity level and then order the results by that
column first.  Theoretically, it would look something like this:
:
: SELECT
: u.user_id,
: u.preferred_name,
: u.first_name,
: u.last_name,
: us.status,
: SUM(t.num_swims) AS balance,
: CASE WHEN (DateDiff('d',Now(),MAX(t.date_practice)) < 90)
: 'Active'
: ELSE
: 'Inactive'
: END CASE
: AS activityLevel
: FROM
: (
: usersTbl u LEFT OUTER JOIN
: practice_transactionTbl t
: ON t.member_id = u.user_id
: )
: INNER JOIN
: user_statusTbl us
: ON us.user_status_id = u.user_status_id
: WHERE
: u.active_code = 1
: AND UCASE(us.status) = 'MEMBER'
: GROUP BY
: activityLevel,
: u.user_id,
: u.last_name,
: u.preferred_name,
: u.first_name,
: us.status,
: ORDER BY
: activityLevel, u.last_name, u.preferred_name
:
: But, I can't really find any info on conditionals in Access SQL.  So, I
was wondering if there are any options I have, assuming I'm even making any
sense.
:
: This site is in ColdFusion MX 6.1 on IIS.



More information about the thelist mailing list