Hide error message

[Michaelcip]

I'm doing an elapsed time calculation (based on "On time and how much has
elapsed" article by Sal Ricciardi) & want to hide the error message
("#error") in the field "Total Time" that is displaying the calculation in my
form. My simple attempts to rectify have failed. Thanks in advance, MC

 

[Marshall Barton]

I'm doing an elapsed time calculation (based on "On time and how much has
elapsed" article by Sal Ricciardi) & want to hide the error message
("#error") in the field "Total Time" that is displaying the calculation in my
form. My simple attempts to rectify have failed. Thanks in advance, MC

I have no idea what article is about, but you can suppress
displaying #Error by using an text box expression something
like:
=IIf(IsError(calculation, Null, calculation))

If you want to display something instead of Null, use
whatever you want in place of Null.

 

[Michaelcip]

I got an error message that said that "The expression you entered has a
function containing the wrong # of arguements" - though expression works
properly on its own. This is my code for my calculation field now,
interjecting your suggestion:
"=IIf(IsError(ElapsedTimeString([StartTime],[EndTime]), Null,
ElapsedTimeString([StartTime],[EndTime])))" The "ElapsedTimeString" is code
that I got from that article located in the Microsoft self-help site; like I
was saying, this code is working as it states, but it produces an error
message that I'm trying to hide. Any other thoughts on this? Thanks, MC

 

[Marshall Barton]

I got an error message that said that "The expression you entered has a
function containing the wrong # of arguements" - though expression works
properly on its own. This is my code for my calculation field now,
interjecting your suggestion:
"=IIf(IsError(ElapsedTimeString([StartTime],[EndTime]), Null,
ElapsedTimeString([StartTime],[EndTime])))" The "ElapsedTimeString" is code
that I got from that article located in the Microsoft self-help site; like I
was saying, this code is working as it states, but it produces an error
message that I'm trying to hide.

No, I have no other thoughts. Let's fix that expression
before going off on tangents:

=IIf(IsError(ElapsedTimeString([StartTime],[EndTime])),
Null, ElapsedTimeString([StartTime],[EndTime]))

Once you get that simple minded workaround correct, you
should analyze the code in the function to figure out why it
causes #Error

If you need help with a specific question about the code in
the function, post a url to the web page and a Copy/Paste of
your version.

 

[Michaelcip]

I adjusted the expression, still no go. Here's the code for
"ElapsedTimeString":

Public Function HoursAndMinutes(interval As Variant) As String
'*************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'*************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long

If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

' 1440 = 24 hrs * 60 mins
totalminutes = Int(CSng(interval * 1440))
minutes = totalminutes Mod 60

' 86400 = 1440 * 60 secs
totalseconds = Int(CSng(interval * 86400))
seconds = totalseconds Mod 60

' Round up the minutes and adjust hours
If seconds > 30 Then minutes = minutes + 1
If minutes > 59 Then hours = hours + 1: minutes = 0
HoursAndMinutes = hours & ":" & Format(minutes, "00")

End Function

Public Function ElapsedTimeString(dateTimeStart As Date, _
dateTimeEnd As Date) _
As String
'*************************************************************
' Function ElapsedTimeString(dateTimeStart As Date,
' dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and
' an ending Date/Time formatted as a string that looks like
' this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*************************************************************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))

' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))

' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", _
minutes & " Minutes"))

str = str & IIf(minutes = "0", "", _
IIf(seconds <> "0", ", ", " "))

' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", _
seconds & " Seconds"))

ElapsedTimeString = IIf(str = "", "0", str)

End Function

Many thanks, MC

I got an error message that said that "The expression you entered has a
function containing the wrong # of arguements" - though expression works
properly on its own. This is my code for my calculation field now,
interjecting your suggestion:
"=IIf(IsError(ElapsedTimeString([StartTime],[EndTime]), Null,
ElapsedTimeString([StartTime],[EndTime])))" The "ElapsedTimeString" is code
that I got from that article located in the Microsoft self-help site; like I
was saying, this code is working as it states, but it produces an error
message that I'm trying to hide.

No, I have no other thoughts. Let's fix that expression
before going off on tangents:

=IIf(IsError(ElapsedTimeString([StartTime],[EndTime])),
Null, ElapsedTimeString([StartTime],[EndTime]))

Once you get that simple minded workaround correct, you
should analyze the code in the function to figure out why it
causes #Error

If you need help with a specific question about the code in
the function, post a url to the web page and a Copy/Paste of
your version.

 

[Marshall Barton]

What a mess! What does the HoursAndMinutes function have
to do with the problem? I don't see where it is used in the
ElapsedTimeString function.

The ElapsedTimeString function looks like it was written by
a newbie and is difficult for me to follow. I haven't been
able ro spot an obvious point that is causing #Error.

I think you will have to place break points in the function
and step through it one line at a time to find where it goes
off the rails. When you do that, be sure to set up a test
case with whatever argument types and values exhibit the
problem.

If you have further questions, please ask a specific
question. It's almost impossible for someone else to figure
out a subtle problem given broad information such as a bunch
of code and "it doesn't work".

 

[Michaelcip]

Well,...actually I said that the code DOES work. I then referenced the
article,...the contents of the article,...the dillema incurred BEYOND the
article of which I hoped someone could address. I TRULY appreciate your
previous constructive comments; all the best Mr. Barton.

What a mess! What does the HoursAndMinutes function have
to do with the problem? I don't see where it is used in the
ElapsedTimeString function.

The ElapsedTimeString function looks like it was written by
a newbie and is difficult for me to follow. I haven't been
able ro spot an obvious point that is causing #Error.

I think you will have to place break points in the function
and step through it one line at a time to find where it goes
off the rails. When you do that, be sure to set up a test
case with whatever argument types and values exhibit the
problem.

If you have further questions, please ask a specific
question. It's almost impossible for someone else to figure
out a subtle problem given broad information such as a bunch
of code and "it doesn't work".
--
Marsh
MVP [MS Access]

I adjusted the expression, still no go. Here's the code for
"ElapsedTimeString":

Public Function HoursAndMinutes(interval As Variant) As String
'*************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'*************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long

If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

' 1440 = 24 hrs * 60 mins
totalminutes = Int(CSng(interval * 1440))
minutes = totalminutes Mod 60

' 86400 = 1440 * 60 secs
totalseconds = Int(CSng(interval * 86400))
seconds = totalseconds Mod 60

' Round up the minutes and adjust hours
If seconds > 30 Then minutes = minutes + 1
If minutes > 59 Then hours = hours + 1: minutes = 0
HoursAndMinutes = hours & ":" & Format(minutes, "00")

End Function

Public Function ElapsedTimeString(dateTimeStart As Date, _
dateTimeEnd As Date) _
As String
'*************************************************************
' Function ElapsedTimeString(dateTimeStart As Date,
' dateTimeEnd As Date) As String
' Returns the time elapsed between a starting Date/Time and
' an ending Date/Time formatted as a string that looks like
' this:
' "10 days, 20 hours, 30 minutes, 40 seconds".
'*************************************************************
Dim interval As Double, str As String, days As Variant
Dim hours As String, minutes As String, seconds As String

If IsNull(dateTimeStart) = True Or _
IsNull(dateTimeEnd) = True Then Exit Function

interval = dateTimeEnd - dateTimeStart
days = Fix(CSng(interval))
hours = Format(interval, "h")
minutes = Format(interval, "n")
seconds = Format(interval, "s")

' Days part of the string
str = IIf(days = 0, "", _
IIf(days = 1, days & " Day", days & " Days"))
str = str & IIf(days = 0, "", _
IIf(hours & minutes & seconds <> "000", ", ", " "))

' Hours part of the string
str = str & IIf(hours = "0", "", _
IIf(hours = "1", hours & " Hour", hours & " Hours"))
str = str & IIf(hours = "0", "", _
IIf(minutes & seconds <> "00", ", ", " "))

' Minutes part of the string
str = str & IIf(minutes = "0", "", _
IIf(minutes = "1", minutes & " Minute", _
minutes & " Minutes"))

str = str & IIf(minutes = "0", "", _
IIf(seconds <> "0", ", ", " "))

' Seconds part of the string
str = str & IIf(seconds = "0", "", _
IIf(seconds = "1", seconds & " Second", _
seconds & " Seconds"))

ElapsedTimeString = IIf(str = "", "0", str)

End Function

 

[Marshall Barton]

Well,...actually I said that the code DOES work. I then referenced the
article,...the contents of the article,...the dillema incurred BEYOND the
article of which I hoped someone could address. I TRULY appreciate your
previous constructive comments; all the best Mr. Barton.

From what I understand in your posts, I don't see how you
can say that the code does work. I sure looks to me that
with some arguments the function is failing and causing the
text box to display #Error

Just because it works "most" of the time, doesn't mean it
works. It seems to me that you have one or more records
that have values in the start and/or end time fields that
causes the function to fall on its face.

 

[Michaelcip]

I should have been clearer, my apologies. The code works when there are
values in the 2 txt box fields ("StartTime"/"EndTime") that the "Total Time"
field is calculating. When the form opens (@ a fresh new record) it hasn't
any values in the 2 fields ("StartTime"/"EndTime") so the "Total Time"
txt-box reads "#error". I wanted to hide the "#error" message, while the
"Total Time" txt-box awaits for the 2 fields ("StartTime"/"EndTime") to be
populated. Does this make sense? MC

 

[Marshall Barton]

That makes a lot more sense. That problem is caused by the
function's declaration typing the start and end arguments as
Date and then feeding it Null instead. That will definitely
generate the error.

Since the function already checks if they are Null (never
gets that far because a date argument can never be null),
you only need to change the one line:

Public Function ElapsedTimeString(dateTimeStart As Variant,
_
dateTimeEnd As Variant) _
As Variant

Did I say that the function's code was really weak?

 

[Michaelcip]

This did the trick Marshal. Many, many thanks! I believe the author that
wrote this (linked in the Microsoft website) wrote it like a decade ago; I
may've attempted to use the code outside of it's scope. I've just recently
delved into VBA & was impressed w/ what he has just the same. As a newbie to
VBA, what would you recommend for me to futher my education of it?? Thanks
again, MC

That makes a lot more sense. That problem is caused by the
function's declaration typing the start and end arguments as
Date and then feeding it Null instead. That will definitely
generate the error.

Since the function already checks if they are Null (never
gets that far because a date argument can never be null),
you only need to change the one line:

Public Function ElapsedTimeString(dateTimeStart As Variant,
_
dateTimeEnd As Variant) _
As Variant

Did I say that the function's code was really weak?
--
Marsh
MVP [MS Access]

I should have been clearer, my apologies. The code works when there are
values in the 2 txt box fields ("StartTime"/"EndTime") that the "Total Time"
field is calculating. When the form opens (@ a fresh new record) it hasn't
any values in the 2 fields ("StartTime"/"EndTime") so the "Total Time"
txt-box reads "#error". I wanted to hide the "#error" message, while the
"Total Time" txt-box awaits for the 2 fields ("StartTime"/"EndTime") to be
populated. Does this make sense? MC

 

[Marshall Barton]

This did the trick Marshal. Many, many thanks! I believe the author that
wrote this (linked in the Microsoft website) wrote it like a decade ago; I
may've attempted to use the code outside of it's scope. I've just recently
delved into VBA & was impressed w/ what he has just the same. As a newbie to
VBA, what would you recommend for me to futher my education of it??

I am probably a poor source of book recommendations. It has
been over 30 years since I used a book to learn a
programming language. Generally, I know what kind of things
to do and only need to find it in Help to get the specifics.
Trial and error experimenting covers most of the fine
details and these newsgroups provide answers to the nuances.

If you want to use books to help you learn (a good idea), I
suggest that you post a request for recommendations in a new
thread. Whether you do that or not, I strongly recommend
that you read the VBA Help topics as a reference manual and
experiment in a test mdb.