Text box Formula Error Message

  • Thread starter Thread starter Eb1mom
  • Start date Start date
E

Eb1mom

I am using this function to calculate elapsed time. It
works very well if there are dates entered for date1 and
date2. If no dates or only one date is entered the text box
with formula will show ERROR. Dates are entered many hours
or sometimes days apart and users are confused by Error
message. I realize ERROR is correct because math functions
can not be performed on a null value. Is there any way I
can change error message to read "waiting for date entry"?


The formula in text box is "=getelapsedtime([date2]-[date1])"
This in Module
Function GetElapsedTime(interval)
Dim totalhours As Long, totalminutes As Long, totalseconds
As Long
Dim days As Long, hours As Long, Minutes As Long, seconds
As Long
days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
seconds = totalseconds Mod 60
GetElapsedTime = days & " Days " & hours & " Hours " &
Minutes & " Minutes " & seconds & " Seconds "
End Function
 
Instead of
Function GetElapsedTime(interval)
'your code...

End Function

Use (air code):
Function GetElapsedTime(byval varDate2 as variant, byval varDate1 as
variant)

Dim interval as variant

If isNull(varDate1) or IsNull(varDate2) Then
GetElapsedTime = "Waiting For Date Entry"
Exit Function
Else
interval = varDate2 - varDate1
End If

'your code

End Function

Then your textbox control source becomes:
"=getelapsedtime([date2], [date1])"
 
Many Thanks Alphonse, Your code works great. Database users
will no longer be confused.

-----Original Message-----
Instead of
Function GetElapsedTime(interval)
'your code...

End Function

Use (air code):
Function GetElapsedTime(byval varDate2 as variant, byval varDate1 as
variant)

Dim interval as variant

If isNull(varDate1) or IsNull(varDate2) Then
GetElapsedTime = "Waiting For Date Entry"
Exit Function
Else
interval = varDate2 - varDate1
End If

'your code

End Function

Then your textbox control source becomes:
"=getelapsedtime([date2], [date1])"

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us


I am using this function to calculate elapsed time. It
works very well if there are dates entered for date1 and
date2. If no dates or only one date is entered the text box
with formula will show ERROR. Dates are entered many hours
or sometimes days apart and users are confused by Error
message. I realize ERROR is correct because math functions
can not be performed on a null value. Is there any way I
can change error message to read "waiting for date entry"?


The formula in text box is "=getelapsedtime([date2]-[date1])"
This in Module
Function GetElapsedTime(interval)
Dim totalhours As Long, totalminutes As Long, totalseconds
As Long
Dim days As Long, hours As Long, Minutes As Long, seconds
As Long
days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
seconds = totalseconds Mod 60
GetElapsedTime = days & " Days " & hours & " Hours " &
Minutes & " Minutes " & seconds & " Seconds "
End Function
Click to expand...


.
Click to expand...
 
Back
Top