UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Wanting A Null Return When Null Into Date Calc Function, Access 2016    
 
   
techexpressinc
post Mar 30 2020, 03:49 PM
Post#1



Posts: 486
Joined: 6-October 08
From: indiana, usa


Background: Working on an effort switching the Due-Date from using Calendar-Days to using Work-Days. Added a couple workday functions to the DB.
The one giving me trouble is this one.
The function adds WorkDays.
Before a function was not needed, date plus number days to do equal Due-Date.
I want a null to return from the function. But, i cannot get it to work. Any ideas?

Attached is a snap of the query and results.
Below is the code:
CODE
Public Function fAddWorkDays(dtStartDate As Variant, _
                             lngWorkDays As Variant, _
                             Optional blIncludeStartdate As Boolean = False) _
                             As Date
    Dim lngDays As Long
    Dim lngSaturdays As Long
    Dim lngOffset As Long
    Dim lngSundays As Long
    
  ' check for nulls
  '
DoCmd.SetWarnings False
'Application.DisplayAlerts = False

  If Not IsDate(dtStartDate) Or IsNull(dtStartDate) Then
        ' fAddWorkDays = Null
        fAddWorkDays = ""
  End If

DoCmd.SetWarnings True
' Application.DisplayAlerts = True
    
    Dim dtEndDate As Date
    
    'First ... GUESS at the End Date you need to cover the workdays you are adding.
    'I ASSUME that the number of days that are added will always toss you into a
    'week end, then I add the number of work weeks to it the get the number of
    'saturdays and sundays.
    lngSaturdays = 1 + lngWorkDays \ 5  'this is Integer Division
    lngSundays = lngSaturdays
    
    dtEndDate = DateAdd("d", lngWorkDays + lngSaturdays + lngSundays, dtStartDate)
    
    'Next, as much as I hate to do it, loop until the fNetWorkdays equals the number
    'of days requested.
    Do Until lngWorkDays = lngDays
        
        'Count the number of work days between the ESTIMATED end date
        'and the start date
        lngDays = fNetWorkdays(dtStartDate, dtEndDate, blIncludeStartdate)
        
        'Make an adjustment to the end date
        If lngDays <> lngWorkDays Then
            lngOffset = lngWorkDays - lngDays
            dtEndDate = dtEndDate + lngOffset
        End If
            
    Loop
    
    'Make sure the end day is NOT a Saturday/Sunday
    Do Until Weekday(dtEndDate, vbMonday) < 6 '6th day of the week with Mon as first day
        dtEndDate = dtEndDate - 1
    Loop
    
    'Make sure the end day is NOT a holiday
    Do Until DCount("*", "tblHolidays", "[HolidayDate]=#" & dtEndDate & "#" & _
                                    " And Weekday([HolidayDate],1) Not In (1,7)") = 0
        dtEndDate = dtEndDate - 1
    Loop
    
    'Once we are out of the loop, the end date should be set to the correct date
    fAddWorkDays = dtEndDate
    
End Function


Thank you,
Russ
This post has been edited by techexpressinc: Mar 30 2020, 03:56 PM
Attached File(s)
Attached File  ERROR_PIC_3_30_2020.jpg ( 76.66K )Number of downloads: 1
Attached File  QUERY_CALLING_FUNCTION_3_30_2020.jpg ( 24.86K )Number of downloads: 2
 

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
theDBguy
post Mar 30 2020, 04:32 PM
Post#2


UA Moderator
Posts: 78,098
Joined: 19-June 07
From: SunnySandyEggo


Hi Russ. My philosophy is to use the function rather than modify it, only because I think it was created and tested by the developer to cover multiple scenarios. If you modify it to fit your specific scenario, it might break it for other cases (potentially). So, if I were you, I would pass a date value to the function where it would return a pre-determined result (I would recommend 12/31/1899), if the answer was to be a null. For example, if I pass today's date (3/30/2020) and the number 2 to that function, I should get 4/1/2020. But, if I pass 12/31/1899 and a zero (0) to it, then I should get 12/31/1899 back. When I get this date back, I can then treat or convert it to a Null value, for use elsewhere.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Mar 30 2020, 04:48 PM
Post#3


UtterAccess Moderator
Posts: 12,807
Joined: 6-December 03
From: Telegraph Hill


I agree with DBG.

You can test whether you have a date a date before even calling the function:
CODE
DueDate: IIf(IsDate([dtrcvd]) = True, fAddWorkDays([dtrcvd], [plandocsvstddays]), Null)


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
techexpressinc
post Mar 30 2020, 05:16 PM
Post#4



Posts: 486
Joined: 6-October 08
From: indiana, usa


Thanks guys, I will test in the morning.
Russ

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
ADezii
post Mar 30 2020, 05:38 PM
Post#5



Posts: 2,990
Joined: 4-February 07
From: USA, Florida, Delray Beach


If you want to return a NULL Value from this Function, or for that matter any Function, the Function itself must return a Variant, as in:
CODE
Public Function fAddWorkDays(dtStartDate As Variant, _
                             lngWorkDays As Variant, _
                             Optional blIncludeStartdate As Boolean = False) _
                             As Variant

This post has been edited by ADezii: Mar 30 2020, 05:38 PM
Go to the top of the page
 
techexpressinc
post Mar 31 2020, 08:39 AM
Post#6



Posts: 486
Joined: 6-October 08
From: indiana, usa


Thank you again - UtterAccess is by far the best MS-Access forum. Quick intelligent responses always.

I did do the VBA code fix, instead of the query, sorry DB Guy. This is a unique instance, and the Network-Day modules have been custom up a bit to pull in the Holiday table, then I had to another minor tweak to getting the correct results.

Here is the new code module one add days, module two calculate number of days between two dates:

CODE
Public Function fAddWorkDays(dtStartDate As Variant, _
                             lngWorkDays As Variant, _
                             Optional blIncludeStartdate As Boolean = False) _
                             As Variant

    Dim lngDays As Long
    Dim lngSaturdays As Long
    Dim lngOffset As Long
    Dim lngSundays As Long
    
  ' check for nulls
  '

  If Not IsDate(dtStartDate) Or IsNull(dtStartDate) Then
         fAddWorkDays = Null
        Exit Function
  End If

  
   Dim dtEndDate As Date
    
    'First ... GUESS at the End Date you need to cover the workdays you are adding.
    'I ASSUME that the number of days that are added will always toss you into a
    'week end, then I add the number of work weeks to it the get the number of
    'saturdays and sundays.
    lngSaturdays = 1 + lngWorkDays \ 5  'this is Integer Division
    lngSundays = lngSaturdays
    
    dtEndDate = DateAdd("d", lngWorkDays + lngSaturdays + lngSundays, dtStartDate)
    
    'Next, as much as I hate to do it, loop until the fNetWorkdays equals the number
    'of days requested.
    Do Until lngWorkDays = lngDays
        
        'Count the number of work days between the ESTIMATED end date
        'and the start date
        lngDays = fNetWorkdays(dtStartDate, dtEndDate, blIncludeStartdate)
        
        'Make an adjustment to the end date
        If lngDays <> lngWorkDays Then
            lngOffset = lngWorkDays - lngDays
            dtEndDate = dtEndDate + lngOffset
        End If
            
    Loop
    
    'Make sure the end day is NOT a Saturday/Sunday
    Do Until Weekday(dtEndDate, vbMonday) < 6 '6th day of the week with Mon as first day
        dtEndDate = dtEndDate - 1
    Loop
    
    'Make sure the end day is NOT a holiday
    Do Until DCount("*", "tblHolidays", "[HolidayDate]=#" & dtEndDate & "#" & _
                                    " And Weekday([HolidayDate],1) Not In (1,7)") = 0
        dtEndDate = dtEndDate - 1
    Loop
    
    'Once we are out of the loop, the end date should be set to the correct date
    fAddWorkDays = dtEndDate
    
End Function


CODE
Option Compare Database
Public Function fNetWorkdays(dtStartDate As Variant, dtEndDate As Variant, _
                          Optional blIncludeStartdate As Boolean = False) _
                             As Long
' was Public Function fNetWorkdays(dtStartDate As Date, dtEndDate As Date, _
'                             Optional blIncludeStartdate As Boolean = False) _
'                             As Long
' If bad dates load zero and exit March 2020 - RMN
'
  If Not IsDate(dtStartDate) Or Not IsDate(dtEndDate) Then
    fNetWorkdays = 0
    Exit Function
  End If
                  
'
                
'Returns the number of workdays between the two passed dates.  Saturdays and
'Sundays are NOT considered workdays.  Plus there is an assumption that a
'table exists that is named tblHolidays that identifies EACH holiday date
'in a field named HolidayDate.  By default the function will NOT count the
'first date in the range as a work date, if you pass a True value to
'blIncludeStartdate, the function will count the start date as a work date
'if it is not a Saturday,Sunday or Holiday.
'''''''''''''''''''''''''''''''''''''''''''
'Author: Brent Spaulding
'Version: 3
'Date: 1/13/2007
'''''''''''''''''''''''''''''''''''''''''''
    Dim lngDays As Long
    Dim lngSaturdays As Long
    Dim lngSundays As Long
    Dim lngHolidays As Long
    Dim lngAdjustment As Long
    Dim blStartIsHoliday As Boolean
    Dim strSQL As String
    'Count the number of RAW days between the dates
    lngDays = DateDiff("d", dtStartDate, dtEndDate)
       'Count the number of Sundays between the two days. Note the use of "ww" as
    'the date interval which forces the count of sundays
    lngSundays = DateDiff("ww", dtStartDate, dtEndDate, vbSunday)
    'Count the number of Saturdays between the two dates.  Note the use of "w" as
    'the date interval which will count the <day of first date in DateDiff()>.
    'So, to count the Saturdays, I adjust the start date of the datediff function
    'to the saturday BEFORE the dtStartDate of the passed range, thus the number
    'of Saturdays between the passed range is returned.
    lngSaturdays = DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSaturday, _
                                dtStartDate, _
                                dtStartDate - Weekday(dtStartDate, vbSunday)), _
                             dtEndDate)
    'Count the number of holidays AND determine if the start date is a holiday
    strSQL = "SELECT HolidayDate FROM tblHolidays" & _
             " WHERE HolidayDate Between #" & dtStartDate & _
                               "# And #" & dtEndDate & "# And" & _
                               " Weekday(HolidayDate, 1) Not In (1,7)" & _
             " ORDER BY HolidayDate DESC"
    With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If Not .EOF Then
            'Get the number of holidays between the dates specified.
            'Need to populate a DAO recordset to ensure a good rcd count
            .MoveLast
            lngHolidays = .RecordCount
            
            'Determine if the start date is a holiday.  Since the rst is
            'in descending order the last record SHOULD be no earlier than
            'the start date, so if the start date is equal to the FIRST record
            'then, the start date is a holiday.
            If !HolidayDate = dtStartDate Then
                blStartIsHoliday = True
            End If
        End If
    End With
    'Make an adjustment based on whether or not the start date is to be
    'included in the calc
    If (Weekday(dtStartDate, vbSunday) = vbSunday) Or _
       (Weekday(dtStartDate, vbSunday) = vbSaturday) Or _
       blStartIsHoliday = True Then
        lngAdjustment = 0
    Else
        If blIncludeStartdate = True Then
            lngAdjustment = 1
        End If
    End If
    'Return the result
    Dim CKNEG As Long
    CKNEG = lngDays - lngSundays - lngSaturdays - lngHolidays + lngAdjustment
    If CKNEG < 0 Then
       fNetWorkdays = 0
    Else
       fNetWorkdays = CKNEG
    End If
End Function












--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
theDBguy
post Mar 31 2020, 01:39 PM
Post#7


UA Moderator
Posts: 78,098
Joined: 19-June 07
From: SunnySandyEggo


Hi Russ. No worries. In the end, you have to do what you think is best for your case. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2020 - 01:11 AM