CASE statement in query

  • Thread starter Thread starter RW
  • Start date Start date
R

RW

I am trying to use a CASE statement in SQL view on a query using

SELECT
[CustomersPreviousData Table].SiteID
, RegularResults.RecNo
, RegularResults.[First Name]
, RegularResults.[Last Name]
, [CustomersPreviousData Table].Status AS [Previous Status]
, [CustomersPreviousData Table].ApplicationID AS [Previous Application]
, [CustomersPreviousData Table].StatusEffectiveDate AS [Previous Effective
Date]
, dbo_Customers.StatusEffectiveDate AS [DC Effective Date]
, CASE RegularResults.[Matching Result] WHEN "1" THEN 'Eligible' END
, dbo_Customers.RecordCode
, [CustomersPreviousData Table].StatusType

FROM RegularResults
INNER JOIN ([CustomersPreviousData Table]
INNER JOIN dbo_Customers
ON [CustomersPreviousData Table].CustomerID = dbo_Customers.CustomerID)
ON RegularResults.RecNo = [CustomersPreviousData Table].CustomerID

WHERE (((RegularResults.[Matching Result])=1));

I am trying to return the word Eligible when the Matchingresult Field has a
'1' in it. I keep getting an error

Syntax error missing operator in query expression "CASE
RegularResults.[Matching Result] WHEN "1" THEN 'Eligible' END"

The operator is 'WHEN' in SQL. Is Access looking for something else?
 
There is no CASE statement in JetSQL. You can only use CASE with a
passthrough query. Look up the Iif() function in online help. Iif is
inferior to CASE in that it only allows a single condition to be tested.
I am trying to use a CASE statement in SQL view on a query using
Click to expand...
Syntax error missing operator in query expression "CASE
RegularResults.[Matching Result] WHEN "1" THEN 'Eligible' END"

The operator is 'WHEN' in SQL. Is Access looking for something else?
Click to expand...
 
On Tue, 10 Mar 2009 17:53:09 -0400, "Bob Barrows"

And in limited situations you can use the Choose function. See help
file.

-Tom.
Microsoft Access MVP

There is no CASE statement in JetSQL. You can only use CASE with a
passthrough query. Look up the Iif() function in online help. Iif is
inferior to CASE in that it only allows a single condition to be tested.
I am trying to use a CASE statement in SQL view on a query using
Click to expand...
Syntax error missing operator in query expression "CASE
RegularResults.[Matching Result] WHEN "1" THEN 'Eligible' END"

The operator is 'WHEN' in SQL. Is Access looking for something else?
Click to expand...
Click to expand...
 
Can also use Switch( ) function in cases there are more than one condition.
But since Switch( ) is borrowed from VBA, rather than being implemented in
Jet, you are limited to application running under Access supervision ( ie.
not VB6, not Excel, as examples) when you use Switch inside a query.


SELECT Switch( x<40, "green", x<60, "yellow", true, "red") FROM ....


will display "green" if x < 40;
"yellow" if x >=40 but < 60
"red" otherwise



Vanderghast, Access MVP


Bob Barrows said:
There is no CASE statement in JetSQL. You can only use CASE with a
passthrough query. Look up the Iif() function in online help. Iif is
inferior to CASE in that it only allows a single condition to be tested.
I am trying to use a CASE statement in SQL view on a query using
Click to expand...
Syntax error missing operator in query expression "CASE
RegularResults.[Matching Result] WHEN "1" THEN 'Eligible' END"

The operator is 'WHEN' in SQL. Is Access looking for something else?
Click to expand...
Click to expand...
 
Back
Top