Thread: Function returning Error Value

1. Registered User
Join Date
Oct 2013
Posts
165

Unanswered: Function returning Error Value

Dear Seniors,

I have created a following function in access, however my function is returning error when there is no value in CPYdate

Public Function Overdue(REMARKS As String, Deadline As Date, CPYdate As Date) As Integer

If IsNull (CPYdate) Then
Overdue = Workdays(Date, Deadline)
Else
If REMARKS = "NA" Then
Overdue = ""
Else
If (Deadline > CPYdate) Then
Overdue = -((Workdays(CPYdate, Deadline) + 1))
Else
Overdue = (Workdays(CPYdate, Deadline) - 1)
End If
End If
End If

End Function

My objective is to calculate the working days between 2 dates.

Workdays is a another user defined function I got it from the internet and its working fine.

Thanks and Regards

2. Moderator
Join Date
Mar 2009
Posts
5,442
Code:
```Public Function Overdue(REMARKS As String, Deadline As Date, CPYdate As Date) As Integer

If IsNull (CPYdate) Then
Overdue = Workdays(Date, Deadline)
Else
If REMARKS = "NA" Then
Overdue = ""
Else
If (Deadline > CPYdate) Then
Overdue = -((Workdays(CPYdate, Deadline) + 1))
Else
Overdue = (Workdays(CPYdate, Deadline) - 1)
End If
End If
End If

End Function```
1. What's Workdays(), how and where is it defined?
2. If the function OverDue() is declared As Integer, you can't expect it to return a string:
Code:
`Overdue = ""`
Originally Posted by velu130486
I have created a following function in access, however my function is returning error when there is no value in CPYdate
3. Why don't you test for a Null or Empty CPYdate parameter if you know that it can be received Null or Empty by the function?

3. Registered User
Join Date
Oct 2013
Posts
165
Hi Sinndho,

As I said, I got the following code from Internet for calculating the Working days and I had pasted this Code in Different Module of same database.

1. What's Workdays(), how and where is it defined?

Option Compare Database
Option Explicit

Public Function Weekdays(ByRef StartDate As Date, _
ByRef EndDate As Date _
) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Sunday and
' do not total two per week in number, this function will
' require modification.
On Error GoTo Weekdays_Error

' The number of weekend days per week.
Const ncNumberOfWeekendDays As Integer = 2

' The number of days inclusive.
Dim varDays As Variant

' The number of weekend days.
Dim varWeekendDays As Variant

' Temporary storage for datetime.
Dim dtmX As Date

' If the end date is earlier, swap the dates.
If EndDate < StartDate Then
dtmX = StartDate
StartDate = EndDate
EndDate = dtmX
End If

' Calculate the number of days inclusive (+ 1 is to add back startDate).
varDays = DateDiff(Interval:="d", _
date1:=StartDate, _
date2:=EndDate) + 1

' Calculate the number of weekend days.
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=StartDate, _
date2:=EndDate) _
* ncNumberOfWeekendDays) _
+ IIf(DatePart(Interval:="w", _
Date:=StartDate) = vbSunday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=EndDate) = vbSaturday, 1, 0)

' Calculate the number of weekdays.
Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
Exit Function

Weekdays_Error:
Weekdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Weekdays"
Resume Weekdays_Exit
End Function

Public Function Workdays(ByRef StartDate As Date, _
ByRef EndDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String

' DateValue returns the date part only.
StartDate = DateValue(StartDate)
EndDate = DateValue(EndDate)

nWeekdays = Weekdays(StartDate, EndDate)
If nWeekdays = -1 Then
Workdays = -1
GoTo Workdays_Exit
End If

strWhere = "[Holiday] >= #" & StartDate _
& "# AND [Holiday] <= #" & EndDate & "#"

' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _
Criteria:=strWhere)

Workdays = nWeekdays - nHolidays

Workdays_Exit:
Exit Function

Workdays_Error:
Workdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit

End Function

As I understood that the above function includes both Start date and End date. However in my calculation I would like to exclude the End date.How to do the same?

2. If the function OverDue() is declared As Integer, you can't expect it to return a string:

I Had replaced the Code Overdue = "" as Overdue = 0. But still its returning error value.

3. Why don't you test for a Null or Empty CPYdate parameter if you know that it can be received Null or Empty by the function?

Could you please explain me how to test the same? I am getting the error value only if the CPYdate is null. If it is not null (i.e) it contains date then the function returns some value.

Public Function Overdue(REMARKS As String, Deadline As Date, CPYdate As Date) As Integer

If IsNull (CPYdate) Then
Overdue = Workdays(Date, Deadline)
Else
If REMARKS = "NA" Then
Overdue = ""
Else
If (Deadline > CPYdate) Then
Overdue = -((Workdays(CPYdate, Deadline) + 1))
Else
Overdue = (Workdays(CPYdate, Deadline) - 1)
End If
End If
End If

End Function

Is there any other simpler method to do the calculation of Working days between 2 dates?

Thanks and Regards

4. Moderator
Join Date
Mar 2009
Posts
5,442
Originally Posted by velu130486
As I understood that the above function includes both Start date and End date. However in my calculation I would like to exclude the End date.How to do the same?
For excluding the end date from the calculation pass DateAdd("d", -1, EndDate) as the EndDate argument to the function.
Originally Posted by velu130486
Could you please explain me how to test the same? I am getting the error value only if the CPYdate is null. If it is not null (i.e) it contains date then the function returns some value.
See the functions IsNull() and IsEmpty() in Access help.

Originally Posted by velu130486
Is there any other simpler method to do the calculation of Working days between 2 dates?
The pair of functions (Weekdays() and Workdays()) seems to be overcomplicated to me, with some questionable features (why declare the arguments ByRef, as this is the default in VBA?). The use of the optional parameter strHolidays confuses me. However I don't have nor the time nor the inclination to test, debug and, if necessary, amend them. I'll then just suppose they work the way they should.

5. Registered User
Join Date
Oct 2013
Posts
165
Hi Sinndho,

[QUOTE=Sinndho;6616363]
Code:
```Public Function Overdue(REMARKS As String, Deadline As Date, CPYdate As Date) As Integer

If IsNull (CPYdate) Then
Overdue = Workdays(Date, Deadline)
Else
If REMARKS = "NA" Then
Overdue = 0
Else
If (Deadline > CPYdate) Then
Overdue = -((Workdays(CPYdate, Deadline) + 1))
Else
Overdue = (Workdays(CPYdate, Deadline) - 1)
End If
End If
End If

End Function```
In the above function, I had declared Remarks as String, and some of my field values contains exactly NA, but still I am getting error in my calculation.

My objective is to If the Remarks field is equal to NA, then the function should return blank or zero.

Thanks and Regards

6. Moderator
Join Date
Mar 2009
Posts
5,442
Which error (Err.Number + Err.Description), when trying to execute which line and what are the values passed when the error occurs?

7. Registered User
Join Date
Oct 2013
Posts
165
Hi Sinndho,

Sorry I am not able to understand your question fully. Herewith I had enclosed my Database and Screenshots for your review.

I am getting #error when the Field contains Blank Values and NA.

Kindly help me to solve this issue.

Public Function Overdue(REMARKS As String, Deadline As Date, CPY As Date) As Integer

If (IsNull(REMARKS) And IsNull(CPY)) Then
Overdue = Now() - Deadline
Else
If (Deadline > CPY) Then
Overdue = CDbl(CPY - Deadline)
Else
If (Deadline < CPY) Then
Overdue = CDbl(CPY - Deadline)
Else
Overdue = 0
End If
End If
End If
End Function

Thanks and Regards

8. Moderator
Join Date
Mar 2009
Posts
5,442
Originally Posted by velu130486
I am getting #error when the Field contains Blank Values and NA.
Did you checked the IsNull() and IsEmpty() functions in Access help? There are what you need, possibly Nz() too. Thest the values before passing them to the function Workdays().

9. Registered User
Join Date
Oct 2013
Posts
165
Hi Sinndho,

I had tried Nz() and Isempty() based on my knowledge, but still it returns error.

So I had modified my function as follows and now it works fine.

Public Function Overdue1(REMARKS As String, Deadline As Date, CPY As Date) As Integer
Dim L As Integer

L = Len(REMARKS)

If L = 0 And Deadline <= Date Then
Overdue1 = WorkingDays2(DateValue(Deadline), Date)
Else
If L = 0 And Deadline > Date Then
Overdue1 = -WorkingDays2(Date, DateValue(Deadline))
Else
If L = 2 Then
Overdue1 = 0
Else
If (DateValue(Deadline) < DateValue(CPY)) Then
Overdue1 = WorkingDays2(DateValue(Deadline), DateValue(CPY))
Else
If (DateValue(Deadline) > DateValue(CPY)) Then
Overdue1 = (-(WorkingDays2(DateValue(CPY), DateValue(Deadline))))
Else
Overdue1 = 0
End If
End If
End If
End If
End If
End Function

Now my query is in the above function, I am using lot of If condition whether it will create any problems. I had made the database (2 tables with 10000 Rows) and combined in a query. When I export to excel it takes too much time.

Is it because of poor database design or its usual. I was browing in website for Database performance and found that every tables should have primary key. If I add autonumber to all table and make it as primary key, then the performance would increase or not.

If possible, could you please provide me the link where I can learn the basics of database design.

Thanks and Regards

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
When posting code please enclose it in [ c o d e ] & [ c o d e ] tags, but remove the spaces !. It makes you code easier to read. Whilst we are at it ident your code so its easy to see if then else blocks. Also consider using meaningfull table, column AND variable names.
ferinstance declaring a variable l = len(something) makes the code harder to read. declaring it
RemarkLength makes that variable self documenting and easy to understand. Bear in mind most of the cost of software development is in maintenance. The easier your code reads the less time is taken to comprehend the codes function.

11. Registered User
Join Date
Oct 2013
Posts
165
Thanks Healdem,

Thanks and Regards

12. Registered User
Join Date
Oct 2013
Posts
165
Dear Seniors,

I had created the Document Tracking database for my project use and its around 20 MB. After finishing the database I had used Analyze performance in Database tools and Implemented all the optimization.

After that It worked well for 2 days and yesterday Suddenly access was crashed yesterday by creating the backup file.

Eventhough I got the back up file I am worried about this crash. Could you explain me what will be reason for this crash, It is beacuse of poor database design, If it is how can I rectify the same.

My database is a very small one, I have only 12 Tables,70 Queries, 3 Forms and 6 macros only & userdefined functions.

I have the Primary key in four tables remaining tables I could not create the primary key bacause of my data structure.

Kindly help me to understand the reason for this crash and how to avoid the same in coming days

Thanks and Regards