Run-time error '3421': Data type conversion error. 1

[kjv1611]

I was trying something different with trying to change a date format within a text document by spliting a string. Here is the code I have. Does anyone see any stupid errors I'm making, or have any suggestions for a better way to go about the whole thing?

Private Sub FixDateFormat()
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim X As Integer
    Dim strDate As String
    Dim varDate1(2) As Variant
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblTest")
    
    rs.MoveFirst
    X = 1
    Do While Not rs.EOF
        strDate = rs.Fields("TransDate")
        varDate1(1) = Split(strDate, 2)
        rs.Edit
        [b][blue]rs.Fields("NewTransDate") = varDate1(1)[/blue][/b]
        rs.Update
        rs.MoveNext
        X = X + 1
    Loop
    
    MsgBox X & "Records Updated"
    
End Sub

The line in blue bold is the line where I get the error..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

varDate1(1) = Split(strDate, 2)
the second element of the array varDate1 will be an array of strings.
Can you please post some examples of actual tblTest.TransDate values and expected result result for NewTransDate ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

Sure, the value is a date value... here is an example of the current value (in text format in the table). I was wanting to do this, b/c Access won't transfer it to a date format so that I can change the format to eliminate the time for each date entry:

2003-07-24 00:00:00.000

2003-11-05 00:00:00.000

There are a couple examples... also a few of them come up as this:

NULL

Does that help any?

I'll be back tomorrow...

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

Have you tried something like this ?
Do While Not rs.EOF
strDate = rs.Fields("TransDate")
rs.Edit
rs.Fields("NewTransDate") = CDate(strDate)
rs.Update
rs.MoveNext
X = X + 1
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

Will try and post back. Thanks for the idea.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Well, I'm still getting a type mismatch error on this line:

rs.Fields("NewTransDate") = CDate(strDate)

this occurs whether I have the table field, NewTransDate, set to Date or Text

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

And this ?
rs.Fields("NewTransDate") = CDate(Left(strDate, 10))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

Doh! Same error.

rs.Fields("NewTransDate") = CDate(Left(strDate, 10))

One other note as well, if I didn't mention earlier...
The field, TransDate is set to Text format.. would that affect anything? If so, then this may not work, b/c the whole problem I'm having is b/c of it being set to a text format, and not date, so I can't just change the format, but have to edit the string value. Any other ideas for going at it?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Do you know of any String function that I could use to say, delete everything after the first space?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

try this --
rs.Fields("NewTransDate") = Left(strDate, 10)

 

[kjv1611]

Tried that, but it brings back to the original error:

Run-time eror '3421':
Data type conversion error.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[rohalyla]

if rs.Fields("NewTransDate") is a date field then try this:
rs.Fields("NewTransDate") = cdate(Mid(strDate,6,2) & "/" & Mid(strDate,9,2) & "/" & Mid(strDate,1,4))

if its not a date field then:
rs.Fields("NewTransDate") = Mid(strDate,6,2) & "/" & Mid(strDate,9,2) & "/" & Mid(strDate,1,4)

 

[kjv1611]

Thanks, rohalyla, the text one was the trick.. couldn't get the other to work - a data conversion error occured, but the text one worked like a charm, when changed table field format to text!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[rohalyla]

Glad it worked!