thayduck
asked on
Access 2000 Module Code Error
There is bad Timestamp data in Oracle table that cause Access 2000 Modules to error.
field should contain :
2013/10/05 21:45:10
But sometimes field contains this:
2013/10/05 **Time is missing and date is where time should be
What code can be added to this module (#1) and 2 others (#2 #3) so module handles these bad records and keeps running. Right now job just stops and I get calls because reports did not get created.
I did not write this application, I inherited. Looks like VB code, I think.
#1
'This function formats a time interval into HHHH:MM:SS
Function FormatSecondsToTime(FSecon ds)
Dim dt
Dim FHrs
FHrs = Int(FSeconds / 3600) **Fails here with Run Time Error 440
Dim FMin
FMin = Int((FSeconds - (FHrs * 3600)) / 60)
Dim FSec
FSec = Int((FSeconds - (FHrs * 3600) - (FMin * 60)))
If FHrs > 0 Then
dt = dt + CStr(FHrs) + ":"
End If
If (FMin < 10 And FHrs > 0) Then
dt = dt + "0" + CStr(FMin) + ":"
Else
dt = dt + CStr(FMin) + ":"
End If
If FSec < 10 Then
dt = dt + "0" + CStr(FSec)
Else
dt = dt + CStr(FSec)
End If
FormatSecondsToTime = dt
End Function
#2
'This function formats a time interval into MM:SS
Function FormatSecondsToMinutes(FSe conds)
Dim dt
Dim FMin
If FSeconds < 0 Then
FSeconds = 0
End If
FMin = Int(FSeconds / 60)
Dim FSec
FSec = Int((FSeconds - (FMin * 60)))
dt = dt + CStr(FMin) + ":"
If FSec < 10 Then
dt = dt + "0" + CStr(FSec)
Else
dt = dt + CStr(FSec)
End If
FormatSecondsToMinutes = dt
End Function
#3
'This function formats a time into HH:MM
Function FormatShortTime(FTime)
If IsNull(FTime) Then
FormatShortTime = " "
Else
Dim FHr
FHr = DatePart("h", FTime)
Dim FMin
FMin = DatePart("n", FTime)
If FMin < 10 Then
FormatShortTime = CStr(FHr) + ":0" + CStr(FMin)
Else
FormatShortTime = CStr(FHr) + ":" + CStr(FMin)
End If
End If
End Function
field should contain :
2013/10/05 21:45:10
But sometimes field contains this:
2013/10/05 **Time is missing and date is where time should be
What code can be added to this module (#1) and 2 others (#2 #3) so module handles these bad records and keeps running. Right now job just stops and I get calls because reports did not get created.
I did not write this application, I inherited. Looks like VB code, I think.
#1
'This function formats a time interval into HHHH:MM:SS
Function FormatSecondsToTime(FSecon
Dim dt
Dim FHrs
FHrs = Int(FSeconds / 3600) **Fails here with Run Time Error 440
Dim FMin
FMin = Int((FSeconds - (FHrs * 3600)) / 60)
Dim FSec
FSec = Int((FSeconds - (FHrs * 3600) - (FMin * 60)))
If FHrs > 0 Then
dt = dt + CStr(FHrs) + ":"
End If
If (FMin < 10 And FHrs > 0) Then
dt = dt + "0" + CStr(FMin) + ":"
Else
dt = dt + CStr(FMin) + ":"
End If
If FSec < 10 Then
dt = dt + "0" + CStr(FSec)
Else
dt = dt + CStr(FSec)
End If
FormatSecondsToTime = dt
End Function
#2
'This function formats a time interval into MM:SS
Function FormatSecondsToMinutes(FSe
Dim dt
Dim FMin
If FSeconds < 0 Then
FSeconds = 0
End If
FMin = Int(FSeconds / 60)
Dim FSec
FSec = Int((FSeconds - (FMin * 60)))
dt = dt + CStr(FMin) + ":"
If FSec < 10 Then
dt = dt + "0" + CStr(FSec)
Else
dt = dt + CStr(FSec)
End If
FormatSecondsToMinutes = dt
End Function
#3
'This function formats a time into HH:MM
Function FormatShortTime(FTime)
If IsNull(FTime) Then
FormatShortTime = " "
Else
Dim FHr
FHr = DatePart("h", FTime)
Dim FMin
FMin = DatePart("n", FTime)
If FMin < 10 Then
FormatShortTime = CStr(FHr) + ":0" + CStr(FMin)
Else
FormatShortTime = CStr(FHr) + ":" + CStr(FMin)
End If
End If
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A daily .exe program runs each morning that produce these reports.
That does popup a message box with an OK button.
How are you currently handling runtime errors? The basic idea is to handle general errors.
I *Think* the lines I placed at the top of the code will avoid the error you are seeing, and the message box at the end is simply to prevent total crashes for other errors. The message box is one method, but if you have an existing method for logging errors, you would use that instead.
Alternatively, change the message box to this - which requires no user interaction, but which you (the developer) can review in the code window later:
How are you currently handling runtime errors? The basic idea is to handle general errors.
I *Think* the lines I placed at the top of the code will avoid the error you are seeing, and the message box at the end is simply to prevent total crashes for other errors. The message box is one method, but if you have an existing method for logging errors, you would use that instead.
Alternatively, change the message box to this - which requires no user interaction, but which you (the developer) can review in the code window later:
Debug.Print "Error " & Err.Number & ": " & err.description
<<
A daily .exe program runs each morning that produce these reports.
>>
There is likely more to it than that. The function is most likely being called from a query or from some other code right in the database...
A daily .exe program runs each morning that produce these reports.
>>
There is likely more to it than that. The function is most likely being called from a query or from some other code right in the database...
Of course they will fail. They are intended to receive a count of seconds, not a date(time) value or string:
FormatSecondsToTime(FSecon ds)
So what is the field returning? A date/time value or a string expression for a date/time?
/gustav
FormatSecondsToTime(FSecon
So what is the field returning? A date/time value or a string expression for a date/time?
/gustav
ASKER
All I want is the program to ignore the records that do not have the correct date and time (2013/10/05 21:45:10) so it completes normally. Since I get these records from a Oracle database I have no idea why they are bad and I have no control over how these records get put out there. This scheduled job just runs every morning on a remote PC in Canada that contains this old access database. Been running this way for over 7 years. But, every so often I get these records with bad dates.
MBIZUP:
I put your code into these modules except for any msgbox entries. Now, this morning, the jobs ran without stopping and reports were produced. That is exactly what I wanted.
Your code bypassed the bad date records.
Also if u have time, can u explain what you did and how/why it bypasses these bad date records ?
MBIZUP:
I put your code into these modules except for any msgbox entries. Now, this morning, the jobs ran without stopping and reports were produced. That is exactly what I wanted.
Your code bypassed the bad date records.
Also if u have time, can u explain what you did and how/why it bypasses these bad date records ?
ASKER
Thanks.
ASKER
accesses.
Will this code require someone to view or answer ?
msgbox "Error " & Err.Number & ": " & err.description