select case syntax for ACCESS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to do the following in a QUERY. SVC_CONNECT_CNT is an existing
field/column but I want my result set to include a new column called FEE.

select case TINSCC.SVC_CONNECT_CNT
case is >= 30000
FEE = 3000.00
case 10000 to 29999
FEE = 2000.00
case else
FEE = 50.00
end select

TINSCC.SVC_CONNECT_CNT , TINWSYS.NUMBER0, TINWSYS.D_PWS_FED_TYPE_CD,
TINWSYS.NAME, TINWSYS.ACTIVITY_STATUS_CD, TINWSYS.D_PWS_ST_TYPE_CD,
TINLGENT.NAME, TINLGENT.ADDR_LINE_ONE_TXT, TINLGENT.ADDR_LINE_TWO_TXT,
TINLGENT.ADDRESS_CITY_NAME, TINLGENT.ADDRESS_STATE_CODE,
TINLGENT.ADDRESS_ZIP_CODE, TINWSLEC.ACTIVE_IND_CD, TINWSLEC.TYPE_CODE,
TINLGENT.TYPE_CODE

from (TINLGENT INNER JOIN TINWSLEC ON (TINLGENT.TINLGENT_IS_NUMBER =
TINWSLEC.TINLGENT_IS_NUMBER) AND (TINLGENT.TINLGENT_ST_CODE =
TINWSLEC.TINLGENT_ST_CODE)) INNER JOIN (TINSCC INNER JOIN TINWSYS ON
(TINSCC.TINWSYS_ST_CODE = TINWSYS.TINWSYS_ST_CODE) AND
(TINSCC.TINWSYS_IS_NUMBER = TINWSYS.TINWSYS_IS_NUMBER)) ON
(TINWSLEC.TINWSYS_IS_NUMBER = TINWSYS.TINWSYS_IS_NUMBER) AND
(TINWSLEC.TINWSYS_ST_CODE = TINWSYS.TINWSYS_ST_CODE)

where (((TINWSYS.ACTIVITY_STATUS_CD)='A') AND ((TINWSLEC.ACTIVE_IND_CD)='A')
AND ((TINWSLEC.TYPE_CODE)='FC'))

order by TINWSYS.D_PWS_FED_TYPE_CD, TINWSYS.NAME, TINLGENT.NAME;


Pasting the above code into a query > SQL view
gives syntax errors. Please HELP !!
 
AFAIR you can't use Select Case statements against Jet. You'll need to use
IIf() instead.

SELECT IIf(TINSCC.SVC_CONNECT_CNT >= 3000, 3000, IIf(TINSCC.SVC_CONNECT_CNT
BETWEEN 1000 AND 29999, 2000, 50)) As FEE FROM.........

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
 
You can't use a Case statement with Access. Try using the SWITCH Statement or
nested IIF

SELECT
(TINSCC.SVC_CONNECT_CNT>=30000,3000,TINSCC.SVC_CONNECT_CNT>10000,2000,True,50)
as FEE,
....
 
Graham said:
AFAIR you can't use Select Case statements against Jet. You'll need to use
IIf() instead.

SELECT IIf(TINSCC.SVC_CONNECT_CNT >= 3000, 3000, IIf(TINSCC.SVC_CONNECT_CNT
BETWEEN 1000 AND 29999, 2000, 50)) As FEE FROM.........
Click to expand...

The respective function in Jet is SWITCH e.g. (untested)

SELECT SWITCH(
TINSCC.SVC_CONNECT_CNT >= 3000, 3000,
TINSCC.SVC_CONNECT_CNT BETWEEN 1000 AND 29999, 2000,
TRUE, 50
) As FEE
 
Back
Top