Convert String Value to Date format (20110315 --> 15/03/2011) (1 Viewer)

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

ria,

Check the length of the String (i.e. using Len([Field] & "")) before converting to Date.

IIF(Len([Field] & "") <> 0, Convert, Null)

OR - if you're positive that it will either be Null or has a value then:

Nz(Convert, Null)

Thanks for the reply. What is the syntax to convert?

I have tried below syntax but that is not working?

CDate(Left(VALUE_DATE, 4) & " / " & Mid(VALUE_DATE, 5, 2) & " / " & Mid(VALUE_DATE, 7, 2)) AS VALUE_DATE

Regards
Ria

I think you want to re-read my post.

Try this in your query. Not tested but should work

fyi: IsEmpty() is only used for Variants

alright then check for ZLS

DCrake said:

alright then check for ZLS

Click to expand...

That was what I mentioned in my first post but the OP didn't seem to take note. Even the syntax was written too.

Reason why failing was that it was trying to to convert back to front

CDate(Left(VALUE_DATE, 4) & " / " & Mid(VALUE_DATE, 5, 2) & " / " & Mid(VALUE_DATE, 7, 2)) AS VALUE_DATE


2010/13/01

is not a valid date.

Something like this perhaps: