DateValue Format Question

G

Guest

I am currently importing a text document that has a supplier ship date field
in the format yyyy-mm-dd. I have the following vb import code.

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

Path = "P:\111111Supply Chain\"
File = "PIC31022.txt"
ReadPIC31022File Path, File

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

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

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, 79, 10)
rst![Supplier Code] = Mid(Inline, 6, 5)
rst![Part Number] = Mid(Inline, 13, 10)
rst![Supplier Ship Date] = DateValue(Mid(Inline, 25, 10))
rst![Shipper Number] = Mid(Inline, 37, 16)
rst![ASN/ASC Bill of Lading] = Mid(Inline, 54, 17)
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


I believe that when Access tries to import the text file, it reads the
dashes(-) and produces an error. The error I am receiving is a "13" type
mismatch error. Does anyone have any suggestions as to how to make Access
over-look these dashes?

Thank You,

Chad
 
D

Douglas J. Steele

Try:

rst![Supplier Ship Date] = DateValue(Mid("#" & Inline, 25, 10 & "#"))
 
G

Guest

I still am receiving the same error.

Douglas J. Steele said:
Try:

rst![Supplier Ship Date] = DateValue(Mid("#" & Inline, 25, 10 & "#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JIT said:
I am currently importing a text document that has a supplier ship date
field
in the format yyyy-mm-dd. I have the following vb import code.

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

Path = "P:\111111Supply Chain\"
File = "PIC31022.txt"
ReadPIC31022File Path, File

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

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

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, 79, 10)
rst![Supplier Code] = Mid(Inline, 6, 5)
rst![Part Number] = Mid(Inline, 13, 10)
rst![Supplier Ship Date] = DateValue(Mid(Inline, 25, 10))
rst![Shipper Number] = Mid(Inline, 37, 16)
rst![ASN/ASC Bill of Lading] = Mid(Inline, 54, 17)
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


I believe that when Access tries to import the text file, it reads the
dashes(-) and produces an error. The error I am receiving is a "13" type
mismatch error. Does anyone have any suggestions as to how to make Access
over-look these dashes?

Thank You,

Chad
Click to expand...
Click to expand...
 
G

Guest

Still getting the same error.

Douglas J. Steele said:
Try:

rst![Supplier Ship Date] = DateValue(Mid("#" & Inline, 25, 10 & "#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JIT said:
I am currently importing a text document that has a supplier ship date
field
in the format yyyy-mm-dd. I have the following vb import code.

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

Path = "P:\111111Supply Chain\"
File = "PIC31022.txt"
ReadPIC31022File Path, File

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

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

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, 79, 10)
rst![Supplier Code] = Mid(Inline, 6, 5)
rst![Part Number] = Mid(Inline, 13, 10)
rst![Supplier Ship Date] = DateValue(Mid(Inline, 25, 10))
rst![Shipper Number] = Mid(Inline, 37, 16)
rst![ASN/ASC Bill of Lading] = Mid(Inline, 54, 17)
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


I believe that when Access tries to import the text file, it reads the
dashes(-) and produces an error. The error I am receiving is a "13" type
mismatch error. Does anyone have any suggestions as to how to make Access
over-look these dashes?

Thank You,

Chad
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry, don't know what I was thinking!

That should have been:

rst![Supplier Ship Date] = DateValue("#" & Mid(Inline, 25, 10) & "#")

(of course, I'm assuming you've double checked that the date actually starts
in position 25)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JIT said:
I still am receiving the same error.

Douglas J. Steele said:
Try:

rst![Supplier Ship Date] = DateValue(Mid("#" & Inline, 25, 10 & "#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JIT said:
I am currently importing a text document that has a supplier ship date
field
in the format yyyy-mm-dd. I have the following vb import code.

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

Path = "P:\111111Supply Chain\"
File = "PIC31022.txt"
ReadPIC31022File Path, File

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

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

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, 79, 10)
rst![Supplier Code] = Mid(Inline, 6, 5)
rst![Part Number] = Mid(Inline, 13, 10)
rst![Supplier Ship Date] = DateValue(Mid(Inline, 25, 10))
rst![Shipper Number] = Mid(Inline, 37, 16)
rst![ASN/ASC Bill of Lading] = Mid(Inline, 54, 17)
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


I believe that when Access tries to import the text file, it reads the
dashes(-) and produces an error. The error I am receiving is a "13"
type
mismatch error. Does anyone have any suggestions as to how to make
Access
over-look these dashes?

Thank You,

Chad
Click to expand...
Click to expand...
Click to expand...
 

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

Type "13" error 7
Invalid Use of Null 3
Extract files from BLOB corrupts PDF 11
Importing to Access Records 6
Excel workbook and printing 3
Exclude current record of a select 5
Export data to multiple excel spreadsheets 2
Microsoft VBA and Printing to templates using code 1

Top