Getting #Error Message

[cec]

I have a code that gives me the "#Error" message if the text box that it is referencing is blank.... how can I avoid this from displaying on the form view? as some of the text boxes will be blank...



thanks

[R_Badger]

Can you give more detail, its a little vague?
What is the code?

Having just said that however you may find the nz function useful, reference:
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

Just in case this helps any more;
nz([forms]![form_name]![text_box_name],value_if_textbox_is_null)

[cec]

Hi R Badger

this is the code

Public Function WorkingHrsMins(StartDate As Date, EndDate As Date) As String
'................................................. ...................
' Name: WorkingHrsMins
' Inputs: StartDate As Date & time
' EndDate As Date & time
' Returns: String- number of hours & minutes not inclusive of weekends
' Author: Arvin Meyer
' Date: February 19, 1997
' Modified by Steve S (ssanfu)
' Date: April 11, 2012
' Comment: Accepts two dates and returns the number
' of work hours and minutes between 8am and 8pm
' Note that this function does not account for holidays.
'................................................. ...................
On Error GoTo Err_WorkingHrs

Dim intCountDays As Integer
Dim intHours As Integer
Dim intMinutes As Integer
Dim intTotalMin As Integer
Dim dtStart As Date
Dim dtEnd As Date
Dim dtTemp As Date
Dim tmStart As Date
Dim tmEnd As Date

intMinutes = 0
intHours = 0
intCountDays = 0
intTotalMin = 0

'check end date > start date
If EndDate < StartDate Then
dtTemp = StartDate
StartDate = EndDate
EndDate = dtTemp
End If

'get just the date portion
dtStart = Int(StartDate)
dtEnd = Int(EndDate)
'get just the time portion
tmStart = StartDate - dtStart
tmEnd = EndDate - dtEnd

If dtStart <> dtEnd Then

'skip the first day
dtStart = dtStart + 1

Do While dtStart < dtEnd
'Make the above < and not <= to not count the EndDate

Select Case Weekday(dtStart)
Case Is = 1, 7
'do nothing
Case Is = 2, 3, 4, 5, 6
intCountDays = intCountDays + 1
End Select
dtStart = dtStart + 1
Loop
intTotalMin = intCountDays * 720

'first day minutes
intTotalMin = intTotalMin + DateDiff("n", tmStart, #8:00:00 PM#)
'Last day minutes
intTotalMin = intTotalMin + DateDiff("n", #8:00:00 AM#, tmEnd)
Else
intTotalMin = DateDiff("n", StartDate, EndDate)
End If

intHours = intTotalMin \ 60 'hours
intMinutes = intTotalMin Mod 60 'minutes

'return value
WorkingHrsMins = intHours & "hrs " & intMinutes & "min"

Exit_WorkingHrs:
Exit Function

Err_WorkingHrs:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingHrs
End Select

End Function

[RagJose]

I would not declare the function arguments as Date if there is a chance a Null value can come through. I'd declare the arguments Variant and test them with IsNull() before using them (or not) in calculations. Suggest you try that.

[cec]

Not sure that I follow that i'm a new at this...

[RagJose]

Okay, your function returns a string like "hh hrs mm min". Let's say if you have blank arguments you would want "No hrs worked" as result. I edited your code to do that. Changes are in bold. Replace the result string as you see fit. Hope this works as you expect.

Public Function WorkingHrsMins(StartDate As Variant, EndDate As Variant) As String
'................................................. ...................
' Name: WorkingHrsMins
' Inputs: StartDate As Date & time
' EndDate As Date & time
' Returns: String- number of hours & minutes not inclusive of weekends
' Author: Arvin Meyer
' Date: February 19, 1997
' Modified by Steve S (ssanfu)
' Date: April 11, 2012
' Comment: Accepts two dates and returns the number
' of work hours and minutes between 8am and 8pm
' Note that this function does not account for holidays.
'................................................. ...................
On Error GoTo Err_WorkingHrs

Dim intCountDays As Integer
Dim intHours As Integer
Dim intMinutes As Integer
Dim intTotalMin As Integer
Dim dtStart As Date
Dim dtEnd As Date
Dim dtTemp As Date
Dim tmStart As Date
Dim tmEnd As Date

If IsNull(StartDate) or IsNull(EndDate) Then
WorkingHrsMins = "No hrs worked"
Exit Function
End If

intMinutes = 0
intHours = 0
intCountDays = 0
intTotalMin = 0

'check end date > start date
If EndDate < StartDate Then
dtTemp = StartDate
StartDate = EndDate
EndDate = dtTemp
End If

'get just the date portion
dtStart = Int(StartDate)
dtEnd = Int(EndDate)
'get just the time portion
tmStart = StartDate - dtStart
tmEnd = EndDate - dtEnd

If dtStart <> dtEnd Then

'skip the first day
dtStart = dtStart + 1

Do While dtStart < dtEnd
'Make the above < and not <= to not count the EndDate

Select Case Weekday(dtStart)
Case Is = 1, 7
'do nothing
Case Is = 2, 3, 4, 5, 6
intCountDays = intCountDays + 1
End Select
dtStart = dtStart + 1
Loop
intTotalMin = intCountDays * 720

'first day minutes
intTotalMin = intTotalMin + DateDiff("n", tmStart, #8:00:00 PM#)
'Last day minutes
intTotalMin = intTotalMin + DateDiff("n", #8:00:00 AM#, tmEnd)
Else
intTotalMin = DateDiff("n", StartDate, EndDate)
End If

intHours = intTotalMin \ 60 'hours
intMinutes = intTotalMin Mod 60 'minutes

'return value
WorkingHrsMins = intHours & "hrs " & intMinutes & "min"

Exit_WorkingHrs:
Exit Function

Err_WorkingHrs:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingHrs
End Select

End Function

[cec]

Thanks worked like a charm!