Trying to get Case Statement to recognize Multiple conditions (1 Viewer)

[bconner]

Hi All,
I am trying to get a Case Statement to evaluate multiple conditions. Example: below when I get diagnosis code 20400 and the age_at_diagnosis is 40 the code is basically ignoring the second condition of the Case "And rs![Age_At_Diag] < 18". How do I get the code to recognize both conditions? Any help is greatly appreciated.





[

Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim db As Database
On Error GoTo err_handler
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Frmt_Conditional_Diagnosis_Data", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Diagnosis_Data_Updated_With_HCC", dbOpenDynaset)
With rs2
If Not (rs.BOF And rs.EOF) Then
' rs.MoveNext
 
  Do Until rs.EOF
 
   On Error Resume Next
 
 
   Select Case rs![DIAG_CD] 'Take the diagnosis codes and map to the appropriate HCC code based on conditions
 
     Case "1940", "20400", "20401", "20402", "20600", "20601", "20602", "20700", "20701", "20702", "20800", "20801", "20802" And rs![Age_At_Diag] < 18
 
         strAgeCond = "age_last < 18"
         strICD9 = rs![DIAG_CD]
         strGender = "N/A"
         strWhere = "Age_Last = '" & strAgeCond & "' and Diagnosis_Code = '" & strICD9 & "' and Gender = '" & strGender & "'"
         strHCC = DLookup("HCC", "Diagnosis_Codes", strWhere)
         strAdditionalHCC = DLookup("Additional_HCC", "Diagnosis_Codes", strWhere)
 
         .AddNew
            rs2!REV_OPT_CLM_KEY = rs!REV_OPT_CLM_KEY
            rs2!REV_OPT_DIAG_SQNC_NBR = rs!REV_OPT_DIAG_SQNC_NBR
            rs2!OWNG_MBR_KEY = rs!OWNG_MBR_KEY
            rs2!DIAG_CD = rs!DIAG_CD
            rs2!REV_OPT_DIAG_VRSN_NBR = rs!REV_OPT_DIAG_VRSN_NBR
            rs2!REV_OPT_SRC_ID = rs!REV_OPT_SRC_ID
            rs2!REV_OPT_DIAG_INFNT_ONLY_IND_CD = rs!REV_OPT_DIAG_INFNT_ONLY_IND_CD
            rs2!REV_OPT_RCRD_TYPE_CD = rs!REV_OPT_RCRD_TYPE_CD
            rs2!RVW_IND_CD = rs!RVW_IND_CD
            rs2!RETRACTED_IND_CD = rs!RETRACTED_IND_CD
            rs2!REV_OPT_DIAG_STRT_DTM = rs!REV_OPT_DIAG_STRT_DTM
            rs2!REV_OPT_DIAG_END_DTM = rs!REV_OPT_DIAG_END_DTM
            rs2!VNDR_PAT_CNTRL_NBR = rs!VNDR_PAT_CNTRL_NBR
            rs2!LAST_UPDT_USER_ID = rs!LAST_UPDT_USER_ID
            rs2!REV_OPT_LOAD_LOG_KEY = rs!REV_OPT_LOAD_LOG_KEY
            rs2!FNC_SOR_CD = rs!FNC_SOR_CD
            rs2!RCRD_STTS_CD = rs!RCRD_STTS_CD
            rs2!LOAD_LOG_KEY = rs!LOAD_LOG_KEY
            rs2!SOR_DTM = rs!SOR_DTM
            rs2!CRCTD_LOAD_LOG_KEY = rs!CRCTD_LOAD_LOG_KEY
            rs2!UPDTD_LOAD_LOG_KEY = rs!UPDTD_LOAD_LOG_KEY
            rs2!MBR_KEY = rs!MBR_KEY
            rs2!CLM_SOR_CD = rs!CLM_SOR_CD
            rs2!CLM_NBR = rs!CLM_NBR
            rs2!REV_OPT_BNFT_YEAR_NBR = rs!REV_OPT_BNFT_YEAR_NBR
            rs2!CLM_REV_OPT_DIAG_VRSN_NBR = rs!CLM_REV_OPT_DIAG_VRSN_NBR
            rs2!CLM_LOAD_ACPTNC_CD = rs!CLM_LOAD_ACPTNC_CD
            rs2!CREATD_CLNDR_DTM = rs!CREATD_CLNDR_DTM
            rs2!LAST_UPDT_DTM = rs!LAST_UPDT_DTM
            rs2!FRST_NM = rs!FRST_NM
            rs2!LAST_NM = rs!LAST_NM
            rs2!BRTH_DT = rs!BRTH_DT
            rs2!HC_ID = rs!HC_ID
            rs2!SRC_MBR_CD = rs!SRC_MBR_CD
            rs2!GNDR_CD = rs!GNDR_CD
            rs2!MBRSHP_SOR_CD = rs!MBRSHP_SOR_CD
            rs2!HCC = strHCC
            rs2!HCC_ADTL = strAdditionalHCC
         .Update
 
    Case Else
 
 
 
         .AddNew
            rs2!REV_OPT_CLM_KEY = rs!REV_OPT_CLM_KEY
            rs2!REV_OPT_DIAG_SQNC_NBR = rs!REV_OPT_DIAG_SQNC_NBR
            rs2!OWNG_MBR_KEY = rs!OWNG_MBR_KEY
            rs2!DIAG_CD = rs!DIAG_CD
            rs2!REV_OPT_DIAG_VRSN_NBR = rs!REV_OPT_DIAG_VRSN_NBR
            rs2!REV_OPT_SRC_ID = rs!REV_OPT_SRC_ID
            rs2!REV_OPT_DIAG_INFNT_ONLY_IND_CD = rs!REV_OPT_DIAG_INFNT_ONLY_IND_CD
            rs2!REV_OPT_RCRD_TYPE_CD = rs!REV_OPT_RCRD_TYPE_CD
            rs2!RVW_IND_CD = rs!RVW_IND_CD
            rs2!RETRACTED_IND_CD = rs!RETRACTED_IND_CD
            rs2!REV_OPT_DIAG_STRT_DTM = rs!REV_OPT_DIAG_STRT_DTM
            rs2!REV_OPT_DIAG_END_DTM = rs!REV_OPT_DIAG_END_DTM
            rs2!VNDR_PAT_CNTRL_NBR = rs!VNDR_PAT_CNTRL_NBR
            rs2!LAST_UPDT_USER_ID = rs!LAST_UPDT_USER_ID
            rs2!REV_OPT_LOAD_LOG_KEY = rs!REV_OPT_LOAD_LOG_KEY
            rs2!FNC_SOR_CD = rs!FNC_SOR_CD
            rs2!RCRD_STTS_CD = rs!RCRD_STTS_CD
            rs2!LOAD_LOG_KEY = rs!LOAD_LOG_KEY
            rs2!SOR_DTM = rs!SOR_DTM
            rs2!CRCTD_LOAD_LOG_KEY = rs!CRCTD_LOAD_LOG_KEY
            rs2!UPDTD_LOAD_LOG_KEY = rs!UPDTD_LOAD_LOG_KEY
            rs2!MBR_KEY = rs!MBR_KEY
            rs2!CLM_SOR_CD = rs!CLM_SOR_CD
            rs2!CLM_NBR = rs!CLM_NBR
            rs2!REV_OPT_BNFT_YEAR_NBR = rs!REV_OPT_BNFT_YEAR_NBR
            rs2!CLM_REV_OPT_DIAG_VRSN_NBR = rs!CLM_REV_OPT_DIAG_VRSN_NBR
            rs2!CLM_LOAD_ACPTNC_CD = rs!CLM_LOAD_ACPTNC_CD
            rs2!CREATD_CLNDR_DTM = rs!CREATD_CLNDR_DTM
            rs2!LAST_UPDT_DTM = rs!LAST_UPDT_DTM
            rs2!FRST_NM = rs!FRST_NM
            rs2!LAST_NM = rs!LAST_NM
            rs2!BRTH_DT = rs!BRTH_DT
            rs2!HC_ID = rs!HC_ID
            rs2!SRC_MBR_CD = rs!SRC_MBR_CD
            rs2!GNDR_CD = rs!GNDR_CD
            rs2!MBRSHP_SOR_CD = rs!MBRSHP_SOR_CD
            rs2!HCC = "N/A"
            rs2!HCC_ADTL = "N/A"
         .Update 
 
 
 
  End Select
 
 
 
   rs.MoveNext
 
  Loop
 
 
Else
MsgBox "There are no Records in the recordset"
End If
 
End With
rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
MsgBox "Finished Update"
 
Exit Sub
err_handler:
rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing
MsgBox Err.Number & " " & Err.Description
End Sub

 

[spikepl]

The clue is in the docs - always a good place to start!

All expressions (each bit separated by comma after CASE), must evaluate to the one of the possible values of the test expression (what follows SELECT CASE) . Your last expression doesn't.