Dear Friends,
I'm using MS Access Version is 2002. And need to convert String Value to Date format (20110315 --> 15/03/2011) but all records do not have value. There are some records with null value. I tried below SQL in VBA but unfortunately this is also not working and throwing error "Error detected, error # 13, Type mismatch"
strSQL = "SELECT CUSTOMER_NO, TRADE_NO, BUY_SELL, " & _
" OFFICE, SECNO, SECNAME, ISIN_NO, " & _
" MAT_DATE, TR_CCY, NOMINAL, TURNOVER, " & _
" CDate(Left(VALUE_DATE, 4) & " / " & Mid(VALUE_DATE, 5, 2) & " / " & Mid(VALUE_DATE, 7, 2)) AS VALUE_DATE, " & _
" INCOME_CCY, INCOME_CC, RM " & _
" FROM " & sTmpTableName
Same SQL is working if I exectue as query.
I tried work around but that is also throwing same error:
CurrentDb.Execute ("UPDATE " & sTmpTableName & _
" SET VALUE_DATE = CDate(Left(VALUE_DATE, 4) & " / " & Mid(VALUE_DATE, 5, 2) & " / " & Mid(VALUE_DATE, 7, 2)) " & _
" WHERE VALUE_DATE IS NOT NULL ")
If I execute Update Statment directely as query it works but when you this query in VBA it is throwing error "Error detected, error # 13, Type mismatch"
Appreciate your help on this.
Thanks
Ria
I'm using MS Access Version is 2002. And need to convert String Value to Date format (20110315 --> 15/03/2011) but all records do not have value. There are some records with null value. I tried below SQL in VBA but unfortunately this is also not working and throwing error "Error detected, error # 13, Type mismatch"
strSQL = "SELECT CUSTOMER_NO, TRADE_NO, BUY_SELL, " & _
" OFFICE, SECNO, SECNAME, ISIN_NO, " & _
" MAT_DATE, TR_CCY, NOMINAL, TURNOVER, " & _
" CDate(Left(VALUE_DATE, 4) & " / " & Mid(VALUE_DATE, 5, 2) & " / " & Mid(VALUE_DATE, 7, 2)) AS VALUE_DATE, " & _
" INCOME_CCY, INCOME_CC, RM " & _
" FROM " & sTmpTableName
Same SQL is working if I exectue as query.
I tried work around but that is also throwing same error:
CurrentDb.Execute ("UPDATE " & sTmpTableName & _
" SET VALUE_DATE = CDate(Left(VALUE_DATE, 4) & " / " & Mid(VALUE_DATE, 5, 2) & " / " & Mid(VALUE_DATE, 7, 2)) " & _
" WHERE VALUE_DATE IS NOT NULL ")
If I execute Update Statment directely as query it works but when you this query in VBA it is throwing error "Error detected, error # 13, Type mismatch"
Appreciate your help on this.
Thanks
Ria