[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2000 Module Code Error

Posted on 2014-02-13
8
Medium Priority
?
401 Views
Last Modified: 2014-02-14
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(FSeconds)

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(FSeconds)

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
0
Comment
Question by:thayduck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 39856196
Give this a try - added null/non numeric handling and general error handling:


Function FormatSecondsToTime(FSeconds)

Dim dt
Dim FHrs

On Error goto eh

if isnumeric(FSeconds) = False then
    FormatSecondsToTime = "00:00"
    exit function
end if

FHrs = Int(FSeconds / 3600)
 
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

exit function

eh:

msgbox "Error " & Err.Number & ": " & err.description

End Function

Open in new window


If that doesn't help, how are you calling/using this function?
0
 

Author Comment

by:thayduck
ID: 39856220
This job runs unattended in the morning via job scheduler on a remote PC that no one
accesses.

Will this code require someone to view or answer ?

msgbox "Error " & Err.Number & ": " & err.description
0
 

Author Comment

by:thayduck
ID: 39856244
A daily .exe program runs each morning that produce these reports.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 39856296
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:

Debug.Print "Error " & Err.Number & ": " & err.description

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39856303
<<
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...
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39857458
Of course they will fail. They are intended to receive a count of seconds, not a date(time) value or string:

    FormatSecondsToTime(FSeconds)

So what is the field returning? A date/time value or a string expression for a date/time?

/gustav
0
 

Author Comment

by:thayduck
ID: 39858828
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 ?
0
 

Author Closing Comment

by:thayduck
ID: 39858831
Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question