Hi Programmers,
There is module to read CSV file and update an access table. CSV file contains two fields (First is Date & Time and the second is Quantity)in each line, usually 24 0r more lines. The date field is inputing into a string variable and then converting it into date format. While doing this, occassionally a type mismatch error is coming, because while inputing date into a string, it is getting values like ":00:00" or "06/21/01 03:00:0006/21/01 03:00:00" and when trying to convert it raises error.
Please help me to solve this and put some idea to trap this error. Following is a part of my code:-
::::::::::::::::::::::::::::
'READING CSV FILE
Open "\DFS\dfsprd.IN" For Input As #1 'Open file for input
Dim xDate As String
Do While Not EOF(1) 'loop until end of file
xDate = ""
Input #1, xDate, AQty 'read data into two variables
' Tdate = CDate(Trim(xDate))
' ADate = FormatDateTime(Tdate, vbGeneralDate)
ADate = FormatDateTime(xDate, vbGeneralDate)
If adoHrActRS.RecordCount <> 0 Then
'find the record if exist else addnew
Call RstCheck
Else
'add recordds as new
Act_HrAdd
End If
Loop
Close #1
End If
'close the recordset
adoHrActRS.Close
Set adoHrActRS = Nothing
End Sub
There is module to read CSV file and update an access table. CSV file contains two fields (First is Date & Time and the second is Quantity)in each line, usually 24 0r more lines. The date field is inputing into a string variable and then converting it into date format. While doing this, occassionally a type mismatch error is coming, because while inputing date into a string, it is getting values like ":00:00" or "06/21/01 03:00:0006/21/01 03:00:00" and when trying to convert it raises error.
Please help me to solve this and put some idea to trap this error. Following is a part of my code:-
::::::::::::::::::::::::::::
'READING CSV FILE
Open "\DFS\dfsprd.IN" For Input As #1 'Open file for input
Dim xDate As String
Do While Not EOF(1) 'loop until end of file
xDate = ""
Input #1, xDate, AQty 'read data into two variables
' Tdate = CDate(Trim(xDate))
' ADate = FormatDateTime(Tdate, vbGeneralDate)
ADate = FormatDateTime(xDate, vbGeneralDate)
If adoHrActRS.RecordCount <> 0 Then
'find the record if exist else addnew
Call RstCheck
Else
'add recordds as new
Act_HrAdd
End If
Loop
Close #1
End If
'close the recordset
adoHrActRS.Close
Set adoHrActRS = Nothing
End Sub