SQL code error

[Finedean]

Hi,
I am getting the following error on this sql code:

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Like'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'CLAIM_HMASTERS_VS'.

It works if I don't incluce this line:
IIf(CLAIM_DETAILS_HCVW.HSERVICECD Like ' 17%','Baby','Mother') AS ClaimType

Here is the complete sql code:

use mis
select CLAIM_HMASTERS_VS.CLAIMNO, CLAIM_DIAGS_VS.DIAG, CLAIM_DIAGS_VS.DIAGDESC, PROV_MASTERS_HCVW.LASTNAME AS FACILITY,
CLAIM_HMASTERS_VS.MEMBID, CLAIM_HMASTERS_VS.MEMBNAME, CLAIM_HMASTERS_VS.DATERECD, CLAIM_HMASTERS_VS.DATEPAID,
CLAIM_HMASTERS_VS.DATEFROM, CLAIM_HMASTERS_VS.HPCODE, CLAIM_DIAGS_VS.DIAGREFNO,
IIf(CLAIM_DETAILS_HCVW.HSERVICECD Like ' 17%','Baby','Mother') AS ClaimType

FROM (((CLAIM_HMASTERS_VS INNER JOIN CLAIM_DETAILS_HCVW ON CLAIM_HMASTERS_VS.CLAIMNO = CLAIM_DETAILS_HCVW.CLAIMNO)

INNER JOIN CLAIM_DIAGS_VS ON CLAIM_HMASTERS_VS.CLAIMNO = CLAIM_DIAGS_VS.CLAIMNO)INNER JOIN PROV_MASTERS_HCVW ON
CLAIM_HMASTERS_VS.PROVID = PROV_MASTERS_HCVW.PROVID)

where CLAIM_HMASTERS_VS.CLAIMNO like '200601119%' AND CLAIM_HMASTERS_VS.HPCODE LIKE 'HIPA' AND CLAIM_DIAGS_VS.DIAGREFNO LIKE '1'

Any Help will be appreciated

Dean

 

[Golom]

The error message looks like it is coming from SQL Server but "IIF" is an Access construct. Look at the "CASE ... ELSE ... END" construct if you are running this on SQL Server.

 

[Finedean]

Worked. thank you very much

Dean