Hello,
I'm trying to modify a select statement in VBA Editor in Excel.
Here's the original that ran with no errors:
[code]
"SELECT CMCV_CKCK_BASE.CKPY_REF_ID, CMCV_CKCK_BASE.CKCK_CK_NO, CMCV_CKCK_BASE.CKCK_PAYEE_NAME, CMCV_CKPY_BASE.CKPY_NET_AMT, CMCV_CKCK_BASE.CKCK_PRINTED_DT, CMCV_CKST_BASE.CKST_STS_DT" & Chr(13) & "" & Chr(10) & "FROM (CMCV_CKCK_B" _
, _
"ASE INNER JOIN CMCV_CKPY_BASE ON CMCV_CKCK_BASE.CKPY_REF_ID = CMCV_CKPY_BASE.CKPY_REF_ID) INNER JOIN CMCV_CKST_BASE ON CMCV_CKCK_BASE.CKPY_REF_ID = CMCV_CKST_BASE.CKPY_REF_ID" & Chr(13) & "" & Chr(10) & "WHERE (((CMCV_CKCK_BASE." _
, _
"CKCK_REISS_USUS_ID)='') AND ((CMCV_CKST_BASE.CKST_STS)='97' Or (CMCV_CKST_BASE.CKST_STS)='98' Or (CMCV_CKST_BASE.CKST_STS)='99' Or (CMCV_CKST_BASE.CKST_STS)='X7' Or (CMCV_CKST_BASE.CKST_STS)='X8' Or (" _
, _
"CMCV_CKST_BASE.CKST_STS)='X9' Or (CMCV_CKST_BASE.CKST_STS)='RC'))" & Chr(13) & "" & Chr(10) & "GROUP BY CMCV_CKCK_BASE.CKPY_REF_ID, CMCV_CKCK_BASE.CKCK_CK_NO, CMCV_CKCK_BASE.CKCK_PAYEE_NAME, CMCV_CKPY_BASE.CKPY_NET_AMT, CMCV_CKC" _
, _
"K_BASE.CKCK_PRINTED_DT, CMCV_CKST_BASE.CKST_STS_DT" & Chr(13) & "" & Chr(10) & "ORDER BY CMCV_CKCK_BASE.CKCK_CK_NO;" & Chr(13) & "" & Chr(10) & "" _
)
Here's what I modified it to - getting a "type mismatch error"
[code]
"SELECT CMCV_CKCK_BASE.CKPY_REF_ID, CMCV_CKCK_BASE.CKCK_CK_NO, CMCV_CKCK_BASE.CKCK_PAYEE_NAME, CMCV_CKPY_BASE.CKPY_NET_AMT, CMCV_CKCK_BASE.CKCK_PRINTED_DT, CMCV_CKST_BASE.CKST_STS_DT" _
, _
" FROM CMCV_CKST_BASE INNER JOIN CMCV_CKCK_BASE ON (CMCV_CKCK_BASE.CKPY_REF_ID = CMCV_CKST_BASE.CKPY_REF_ID) AND (CMCV_CKCK_BASE.CKCK_SEQ_NO = CMCV_CKST_BASE.CKCK_SEQ_NO)) INNER JOIN CMCV_CKPY_BASE ON CMCV_CKCK_BASE.CKPY_REF_ID=CMCK_CKPY_BASE.CKPY_REF_ID" _
, _
" WHERE ((CMCV_CKCK_BASE.CKCK_CK_NO<>0) AND ((CMCV_CKST_BASE.CKST_STS='98' Or CMCV_CKST_BASE.CKST_STS='99' Or CMCV_CKST_BASE.CKST_STS='97') And CMCV_CKST_BASE.CKST_STS<>'RC'))")
The reason I need to modify it is the 1st one (old one with no errors) needs to link by 2 fields not 1 and the RC criteria was wrong.
Any ideas? I've been messing with this all morning trying things and can't figure out what is causing the error.
Thanks
Erica