Type "13" error

J

JIT

I am recieving an error message that simply states "13." I have over 50,000
records in this data set and it shows the error for each one. The following
is the VB code.

Option Compare Database
Function ImportPIC31004()
Dim Path As String
Dim File As String

Path = "P:\111111Supply Chain\"
File = "PIC31004.txt"
ReadPIC31004File Path, File

End Function
Function ReadPIC31004File(FilePath As String, DataFile As String)
Dim Inline As String, rst As DAO.Recordset
On Error GoTo ErrHandler

Set rst = CurrentDb.OpenRecordset("ASN Data Current")

Open FilePath + DataFile For Input As #1
Line Input #1, Inline
Do Until EOF(1)


rst.AddNew
rst!PDC = Left(Inline, 5)
rst![Conveyance Number] = Mid(Inline, 6, 10)
rst![Part Number] = Mid(Inline, 17, 10)
rst![Supplier Code] = Mid(Inline, 28, 5)
rst![Supplier Ship Date] = DateValue(Mid(Inline, 36, 10))
rst![Shipper Number] = Mid(Inline, 47, 16)
rst![ASN/ASC Bill of Lading] = Mid(Inline, 64, 17)
rst![ASN/ASC Qty] = Val(Mid(Inline, 81, 8))
rst![Carrier SCAC] = Mid(Inline, 90, 4)
rst![Tran Mode] = Mid(Inline, 98, 2)
rst![Tran Code] = Mid(Inline, 104, 2)
rst![# Days Old] = Val(Mid(Inline, 112, 3))
rst![Shpmt Srce] = Mid(Inline, 129, 1)
rst.Update

Line Input #1, Inline
Loop

ErrHandler:
Select Case Err
Case 0
Exit Function
Case 55
Close #1
Resume
Case Else
MsgBox Err
MsgBox Err.Description
Resume
End Select
End Function


Can anyone look this over and tell me what is wrong with this import?
 
D

Dirk Goldgar

In
JIT said:
I am recieving an error message that simply states "13." I have over
50,000 records in this data set and it shows the error for each one.
The following is the VB code.

Option Compare Database
Function ImportPIC31004()
Dim Path As String
Dim File As String

Path = "P:\111111Supply Chain\"
File = "PIC31004.txt"
ReadPIC31004File Path, File

End Function
Function ReadPIC31004File(FilePath As String, DataFile As String)
Dim Inline As String, rst As DAO.Recordset
On Error GoTo ErrHandler

Set rst = CurrentDb.OpenRecordset("ASN Data Current")

Open FilePath + DataFile For Input As #1
Line Input #1, Inline
Do Until EOF(1)


rst.AddNew
rst!PDC = Left(Inline, 5)
rst![Conveyance Number] = Mid(Inline, 6, 10)
rst![Part Number] = Mid(Inline, 17, 10)
rst![Supplier Code] = Mid(Inline, 28, 5)
rst![Supplier Ship Date] = DateValue(Mid(Inline, 36, 10))
rst![Shipper Number] = Mid(Inline, 47, 16)
rst![ASN/ASC Bill of Lading] = Mid(Inline, 64, 17)
rst![ASN/ASC Qty] = Val(Mid(Inline, 81, 8))
rst![Carrier SCAC] = Mid(Inline, 90, 4)
rst![Tran Mode] = Mid(Inline, 98, 2)
rst![Tran Code] = Mid(Inline, 104, 2)
rst![# Days Old] = Val(Mid(Inline, 112, 3))
rst![Shpmt Srce] = Mid(Inline, 129, 1)
rst.Update

Line Input #1, Inline
Loop

ErrHandler:
Select Case Err
Case 0
Exit Function
Case 55
Close #1
Resume
Case Else
MsgBox Err
MsgBox Err.Description
Resume
End Select
End Function


Can anyone look this over and tell me what is wrong with this import?
Click to expand...

Error number 13 is "Type Mismatch". It seems likely that either one of
the fields in your recordset is numeric when you're treating it as text,
or the value you're parsing from your input text file for [Supplier Ship
Date] -- Mid(Inline, 36, 10) -- cannot be interpreted as a date.

The easiest way to debug this is to set a breakpoint in the code, start
it running to get to the breakpoint, and then step through it to see
where it hits the error. At that point you can check the definitions
and values of everything.

Incidentally, I don't recommend hard-coding your file number as 1.
Better to use FreeFile() to get the first available file number, assign
it to a variable, and then use that variable in your I/O statements --
just in case file #1 isn't available.
 
J

JIT

Now I am receiving a "3421" Data type conversion error?

Dirk Goldgar said:
In
JIT said:
I am recieving an error message that simply states "13." I have over
50,000 records in this data set and it shows the error for each one.
The following is the VB code.

Option Compare Database
Function ImportPIC31004()
Dim Path As String
Dim File As String

Path = "P:\111111Supply Chain\"
File = "PIC31004.txt"
ReadPIC31004File Path, File

End Function
Function ReadPIC31004File(FilePath As String, DataFile As String)
Dim Inline As String, rst As DAO.Recordset
On Error GoTo ErrHandler

Set rst = CurrentDb.OpenRecordset("ASN Data Current")

Open FilePath + DataFile For Input As #1
Line Input #1, Inline
Do Until EOF(1)


rst.AddNew
rst!PDC = Left(Inline, 5)
rst![Conveyance Number] = Mid(Inline, 6, 10)
rst![Part Number] = Mid(Inline, 17, 10)
rst![Supplier Code] = Mid(Inline, 28, 5)
rst![Supplier Ship Date] = DateValue(Mid(Inline, 36, 10))
rst![Shipper Number] = Mid(Inline, 47, 16)
rst![ASN/ASC Bill of Lading] = Mid(Inline, 64, 17)
rst![ASN/ASC Qty] = Val(Mid(Inline, 81, 8))
rst![Carrier SCAC] = Mid(Inline, 90, 4)
rst![Tran Mode] = Mid(Inline, 98, 2)
rst![Tran Code] = Mid(Inline, 104, 2)
rst![# Days Old] = Val(Mid(Inline, 112, 3))
rst![Shpmt Srce] = Mid(Inline, 129, 1)
rst.Update

Line Input #1, Inline
Loop

ErrHandler:
Select Case Err
Case 0
Exit Function
Case 55
Close #1
Resume
Case Else
MsgBox Err
MsgBox Err.Description
Resume
End Select
End Function


Can anyone look this over and tell me what is wrong with this import?
Click to expand...

Error number 13 is "Type Mismatch". It seems likely that either one of
the fields in your recordset is numeric when you're treating it as text,
or the value you're parsing from your input text file for [Supplier Ship
Date] -- Mid(Inline, 36, 10) -- cannot be interpreted as a date.

The easiest way to debug this is to set a breakpoint in the code, start
it running to get to the breakpoint, and then step through it to see
where it hits the error. At that point you can check the definitions
and values of everything.

Incidentally, I don't recommend hard-coding your file number as 1.
Better to use FreeFile() to get the first available file number, assign
it to a variable, and then use that variable in your I/O statements --
just in case file #1 isn't available.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
J

JIT

I changed three fields from "Text" to "Number" requirements. It is once
again popping up with the error for every record. Thank you again for your
help.
 
D

Dirk Goldgar

In
JIT said:
I changed three fields from "Text" to "Number" requirements. It is
once again popping up with the error for every record. Thank you
again for your help.
Click to expand...

To debug this, we're going to need to know what field(s) are giving the
error, what value(s) you're trying to assign to the field(s), and
exactly what line(s) of code is giving the error. To determine that, I
think you're going to have set a breakpoint and step through the code,
as I described. Do you need instructions for how to do that?
 
J

JIT

Yes I do. FYI. I have tried to debug in the Module, but now it asks for the
MACRO name everytime and cannot find any MACRO's. Therefore, I do not know
what line the error is in or the field. When i run the Macro that runs the
code is when these errors pop up, one after another.
 
D

Dirk Goldgar

In
JIT said:
Yes I do. FYI. I have tried to debug in the Module, but now it asks
for the MACRO name everytime and cannot find any MACRO's. Therefore,
I do not know what line the error is in or the field. When i run the
Macro that runs the code is when these errors pop up, one after
another.
Click to expand...

Set a breakpoint on the line ...
rst.AddNew
Click to expand...

in the function ReadPIC31004File. To do that, open the module in the VB
editor, locate that line in the function, and either click on it and
then press F9, or else just click in the vertical bar on the left side
of the module window, next to the line of code.

You won't be able to step directly into the code of the function,
because it requires arguments. So what you need to do after setting the
breakpoint is do whatever you normally do that results in the function
being called properly -- click a command button, run a macro, whatever.
Then the code will be invoked in the normal way, come to the breakpoint
you set, and stop. At that point, you can step through the code by
pressing the F8 key.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DateValue Format Question 4
error 13 type mismatch 2
Error sending emails from Access through Outlook 0
Importing to Access Records 6
Editing saved UID and PWD through relinking ODBC tables 0
An Error while running Error Handling Code 4
Function to convert CUSIP code to ISIN code cusipconv() 1
"Data type mismatch" error 5

Top