Type Mismatch - Splitting Data Problem

[tbassngal]

Problems again: Runtime Error 13 Type Mismatch: bombing out on

z = CDate(Mid(Left(i, Len(i) - 1), 1 + InStr(i, "(")))

This was working the other day with the help of PHV... whats happened? I am clueless and can't seem to get this right.

Public Function fblnMakeTable_F_ProdSched() MakeTbl_F_Data_ProductionSchedule
Dim a, i, x, y, z, strProdSched As String
Set DB = CurrentDb
strSQL = "Select * from R_DeliveryStatus"
Set RS = DB.OpenRecordset(strSQL, dbOpenDynaset)

RS.MoveFirst
Do While Not RS.EOF
strProdSched = RS.[prodnschedule].Value
x = RS.[FirstOfProjectPOID].Value
If Not IsNull(strProdSched) Then
If Not (strProdSched Like "No New*") Then
If Not (strProdSched Like "Schedule*") Then

a = Split(Replace(strProdSched, " ", ""), ",")
'Debug.Print a
For Each i In a
y = Val(Left(i, InStr(i, "(") - 1))
Debug.Print x
z = CDate(Mid(Left(i, Len(i) - 1), 1 + InStr(i, "(")))
'''z = CDate(Mid(i, 1 + InStr(i, "("), 6))
Debug.Print x

'MsgBox "ProductionPOId=" & x & ", ProdQty=" & y & ", ProdDate=" & z

CurrentDb.Execute "Insert Into tblProdSched (FirstOfProjectPOID, ProdQty, ProdDate) VALUES (" & x & ", '" & y & "', '" & z & "');"
Next
End If
End If
End If
RS.MoveNext
Loop
RS.Close
End Function

Private Function MakeTbl_F_Data_ProductionSchedule()
On Error GoTo ErrorHandler

DoCmd.RunSQL "CREATE TABLE tblProdSched (FirstOfProjectPOID text(20), ProdQty LONG, ProdDate DateTime)"
CurrentDb.TableDefs.Refresh

ErrorHandler:

Select Case Err.Number
Case 3010
DoCmd.DeleteObject acTable, "tblProdSched"
Resume
'Case Else
'MsgBox Err.Number & " " & Err.Description
End Select

End Function

 

[BobStubbs]

Can you post some examples of the data which is in variable i, which you are trying to convert to a date?



Bob Stubbs

 

[PHV]

What are the value of x, i and y when the code bombs out ?

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

 

[tbassngal]

Oh, sorry - PHV, you were helping me with this the other day, the field this is splitting looks something like this:

5 (05/01/05), 10 (05/02/05), 15 (05/03/05), 100 (05/04/05)

z = CDate(Mid(Left(i, Len(i) - 1), 1 + InStr(i, "("))) was working...

' used to be: z = CDate(Mid(i, 1 + InStr(i, "("), 6)) which worked for half the file and then bombed out with the same runtime error.

For this example, the values for the variables are:
x = 14390
i = "6000("
i does not look right - should be "6000(10/2/2005)" I think
z = 10/2/2005

 

[tbassngal]

Please ignore the previous post, I accidentally submitted before I was ready! This may be a data problem, I am looking into it...

Oh, sorry - PHV, you were helping me with this the other day, the field this is splitting looks something like this:

5 (05/01/05), 10 (05/02/05), 15 (05/03/05), 100 (05/04/05)

z = CDate(Mid(Left(i, Len(i) - 1), 1 + InStr(i, "("))) was working...

' used to be: z = CDate(Mid(i, 1 + InStr(i, "("), 6)) which worked for half the file and then bombed out with the same runtime error.


For this example, the values for the variables are:
x = 14390
i = "6000("
y= 6000
z = 10/2/2005 ' code bombs here

 

[tbassngal]

This is a data issue - took me a while to figure out, but I've got it now, thank you very much!