Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    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

    Could you please help me to correct my 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
    R. Vadivelan

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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 = ""
    Quote Originally Posted by velu130486 View Post
    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?
    Have a nice day!

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    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
    R. Vadivelan

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    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.
    Quote Originally Posted by velu130486 View Post
    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.

    Quote Originally Posted by velu130486 View Post
    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.
    Have a nice day!

  5. #5
    Join Date
    Oct 2013
    Posts
    163
    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.

    Could you please help me to identify where I had done the mistake.

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

    Thanks and Regards
    R. Vadivelan

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Which error (Err.Number + Err.Description), when trying to execute which line and what are the values passed when the error occurs?
    Have a nice day!

  7. #7
    Join Date
    Oct 2013
    Posts
    163
    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
    R. Vadivelan
    Attached Thumbnails Attached Thumbnails Capture1.JPG   Capture2.JPG  
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    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().
    Have a nice day!

  9. #9
    Join Date
    Oct 2013
    Posts
    163
    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
    R. Vadivelan

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Oct 2013
    Posts
    163
    Thanks Healdem,

    I had changed my function as per your comments.

    Thanks and Regards
    R. Vadivelan

  12. #12
    Join Date
    Oct 2013
    Posts
    163
    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
    R. Vadivelan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •