Results 1 to 8 of 8
  1. #1
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80

    Passing a Dataset to a Function

    In a situation where I need to pass a dataset to a function so it can manipulate it and return a single double value.


    Situation is, Record in dataset may contain any of several flags (LL, LF, SLF) SOME of those flags require simple calculations involving values in the same record. Others, though, require calculations that involve looking back or forward by 2, 3, or as many as 4 records. Need to understand better what I can or can't do when I pass the entire dataset to the function either ByRef or ByVal. If I need to do A + B, but in some cases B needs to be the B from two records ago, and the A needs to be the A from one record forward, how's the best way to do that? ((Forward and backward in this case are date-driven, so B-2 means the B value from two days ago, ergo 2 records ago.) If I pass the entire recordset to the function, can I manipulate it there just as if I had loaded it there in the first place? Or do I need to capture and pass all the before-and-after values at the calling level?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK you lost me part way through.
    Please step back and give an overview of what your system is designed to do.
    Forget about datasets, functions and other details.
    Explain the purpose of this without using jargon
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    Okay, so I have a table that I open as a dataset. Each record consists of a date, several numeric (double) fields, a flag field, and a blank numeric field I need to fill in with a calculation.

    EXAMPLE 6/15/18 .01179 1,2134 .00031 2.2525 LL _____

    The calculation for the last field uses the other numeric fields, and is easy if it uses those values from the same record. HOWEVER, depending on the flag value the calculation sometimes needs to look back a day or two or forward a day or two for the values it needs. (It's about holidays, weekends, etc.)

    So if I'm working my way through the recordset doing the math and populating that last field, and Iencounter a flag that says "Use Value #1 from two days ago", looking for the easiest way to do that.

    Hope that's simple enough.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you're OK with how to handle the calculations and moving back and forth among the records, then you can pass the entire recordset to a function and do what you need to do there. It has to be a function if you want a value to be returned to the calling procedure, otherwise a sub is ok too. Simply declare the rs, set it, and pass it to your function. Like

    Code:
    Dim rs As DAO.Recordset
    Dim lngMyVar as Long
    
    Set rs = currentdb.operecordset("myQuery")
    lngMyVar = MyFunction(rs)
    ....
    Code:
    Public/Private MyFunction(rs as Recordset) As Long
    Do stuff
    ...
    MyFunction = the result of your calculations goes here
    End Function
    A reference to DAO is required, of course.
    Last edited by Micron; 06-17-2018 at 12:04 PM. Reason: code clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    Well, the moving back and forth part is where I'm looking for an easier way. So if my RS has, say, 10 records and I'm working in the 5th record, what's the simplest way to say "Okay, do this math, but go back three records to get that value"? I can imagine a lot of ways to use movelast, movenext, etc., but am looking for the simplest way. As in "myResult = ThisRecord.a + "Thisrecord-2.b" or something along those lines. As in how to refer backward or forward to grab a value from a record in the same RS, without, hopefully, actually having to do the movelast/movefirst stuff.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you can't retrieve the value as you pass the record in question, then you have no choice but to move back. You mention 3 records back, but don't say if that is a constant. If it is, it should be as simple as rs.Move -3, assuming DAO type recordset. However, if you need to move back to where you were, you will also need to establish bookmarks during your move sequences, assuming your rs type allows bookmarks. Also, you obviously can't invoke a 3 row move backwards if you are on the 2nd row, so your move number (long) might need to be a variable, assuming you're doing this in some sort of loop.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you are making it more complicated than it needs to be. You said it is about dates (weekends/holidays,etc.), can you describe a bit more what exactly your flags need to do. I think all you need is a function that gets the numeric values, the base date and the flag and based on the flag determines the first (or second or third) previous (or next) business date, etc. Do you have a table for the holidays?
    There are many custom functions to work with dates, here is an oldie I use:
    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Function datNextWorkDate(datReferenceDate As Date, intDays As Integer) As Date
    datNextWorkDate = DLookup("[Calendar Date]", "[qryWorkDaysDESC]", "[Calendar Date] <= #" & (datReferenceDate - intDays) & "#")
    End Function
    
    
    Public Function vcAddBusinessDaysWithHolidayDESC(datStart As Date, lDays As Long) As Date
    Dim rHoliday As DAO.Recordset, vHolidays()
    Set rHoliday = CurrentDb.OpenRecordset("qryHolidaysDESC")
    vHolidays = rHoliday.GetRows(rHoliday.RecordCount)
    Set rHoliday = Nothing
    vcAddBusinessDaysWithHolidayDESC = dhSubtractWorkDaysA(lDays, datStart, vHolidays)
    End Function
    Function usbNetWorkdays(StartDate As Date, EndDate As Date) As Double
    
    
    Dim retval As Double
    
    
    If StartDate > EndDate Then
        Do While EndDate <= StartDate
            If WeekDay(EndDate) = 1 Or WeekDay(EndDate) = 7 Then
                EndDate = EndDate + 1
            Else
                retval = retval + 1
                EndDate = EndDate + 1
            End If
        Loop
    Else
        Do While StartDate <= EndDate
            If WeekDay(StartDate) = 1 Or WeekDay(StartDate) = 7 Then
                StartDate = StartDate + 1
            Else
                retval = retval + 1
                StartDate = StartDate + 1
            End If
        Loop
    End If
    
    
    If retval = 0 Then
        retval = 1
    End If
    
    
    usbNetWorkdays = retval - 1
    
    
    End Function
    Function dhFirstDayInWeek(Optional dtmDate As Date = 0) As Date
        ' Returns the first day in the week specified
        ' by the date in dtmDate.
        ' Uses localized settings for the first day of the week.
        If dtmDate = 0 Then
            ' Did the caller pass in a date? If not, use
            ' the current date.
            dtmDate = Date
        End If
        dhFirstDayInWeek = dtmDate - WeekDay(dtmDate, _
         vbUseSystem) + 1
    End Function
    Function dhLastDayInWeek(Optional dtmDate As Date = 0) As Date
        ' Returns the last day in the week specified by
        ' the date in dtmDate.
        ' Uses localized settings for the first day of the week.
        If dtmDate = 0 Then
            ' Did the caller pass in a date? If not, use
            ' the current date.
            dtmDate = Date
        End If
        dhLastDayInWeek = dtmDate - WeekDay(dtmDate, vbUseSystem) + 7
    End Function
    
    
    Public Function YearStart(WhichYear As Integer) As Date
    'It returns the first Monday of the specified year
    Dim WeekDay As Integer
    Dim NewYear As Date
    
    
    NewYear = DateSerial(WhichYear, 1, 1)
    WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0
    
    
    If WeekDay < 4 Then
        YearStart = NewYear - WeekDay
    Else
        YearStart = NewYear - WeekDay + 7
    End If
    
    
    End Function
    Public Function WeekStart(WhichWeek As Integer, WhichYear As _
                        Integer) As Date
    'It returns the first Monday of the specified week
    WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7)
    
    
    End Function
    
    
    Public Function NthDayOfWeek(Y As Integer, M As Integer, _
        n As Integer, DOW As Integer) As Date
    
    
        NthDayOfWeek = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1), _
         (DOW + 1) Mod 8)) + ((n - 1) * 7))
    
    
    End Function
    
    
    
    
    Public Function SpecificWeekday(ByVal D As Date, Optional _
        ByVal WhatDay As VbDayOfWeek = vbSaturday, _
        Optional GetNext As Boolean = True) As Date
        SpecificWeekday = (((D - WhatDay + GetNext) \ 7) - GetNext) * 7 + WhatDay
    End Function
    Function FirstOfNextMonth(datDATE As Date) As Date
       Dim dtm As Date
       dtm = datDATE
       FirstOfNextMonth = DateSerial(Year(dtm), Month(dtm) + 1, 1)
    End Function
    Function FirstOfThisMonth(datDATE As Date) As Date
       Dim dtm As Date
       dtm = datDATE
       FirstOfThisMonth = DateSerial(Year(dtm), Month(dtm), 1)
    End Function
    Function LastOfNextMonth(datDATE As Date) As Date
       LastOfNextMonth = DateAdd("m", 1, FirstOfNextMonth(datDATE)) - 1
    End Function
    Function LastOfThisMonth(datDATE As Date) As Date
       LastOfThisMonth = DateAdd("d", -1, FirstOfNextMonth(datDATE))
    End Function
    Public Function dhSubtractWorkDaysA(lngDays As Long, _
    Optional dtmDate As Date = 0, _
    Optional adtmDates As Variant) As Date
        'modified by Vlad to subtract dates
        ' Add the specified number of work days to the
        ' specified date.
        
        ' Modified from code in
        ' "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' In:
        '   lngDays:
        '       Number of work days to add to the start date.
        '   dtmDate:
        '       date on which to start looking.
        '       Use the current date, if none was specified.
        '   adtmDates (Optional):
        '       Array containing holiday dates. Can also be a single
        '       date value, if that's what you want.
        ' Out:
        '   Return Value:
        '       The date of the working day lngDays from the start, taking
        '       into account weekends and holidays.
        ' Example:
        '   dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
        '   returns #2/25/2000#, which is the date 10 work days
        '   after 2/9/2000, if you treat 2/16 and 2/17 as holidays
        '   (just made-up holidays, for example purposes only).
        
        ' Did the caller pass in a date? If not, use
        ' the current date.
        Dim lngCount As Long
        Dim dtmTemp As Date
        
        If dtmDate = 0 Then
            dtmDate = Date
        End If
        
        dtmTemp = dtmDate
        
        For lngCount = 1 To lngDays
            dtmTemp = dhPreviousWorkdayA(dtmTemp, adtmDates)
        Next lngCount
        dhSubtractWorkDaysA = dtmTemp
    End Function
    Public Function dhAddWorkDaysA(lngDays As Long, _
    Optional dtmDate As Date = 0, _
    Optional adtmDates As Variant) As Date
        ' Add the specified number of work days to the
        ' specified date.
        
        ' Modified from code in
        ' "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' In:
        '   lngDays:
        '       Number of work days to add to the start date.
        '   dtmDate:
        '       date on which to start looking.
        '       Use the current date, if none was specified.
        '   adtmDates (Optional):
        '       Array containing holiday dates. Can also be a single
        '       date value, if that's what you want.
        ' Out:
        '   Return Value:
        '       The date of the working day lngDays from the start, taking
        '       into account weekends and holidays.
        ' Example:
        '   dhAddWorkDaysA(10, #2/9/2000#, Array(#2/16/2000#, #2/17/2000#))
        '   returns #2/25/2000#, which is the date 10 work days
        '   after 2/9/2000, if you treat 2/16 and 2/17 as holidays
        '   (just made-up holidays, for example purposes only).
        
        ' Did the caller pass in a date? If not, use
        ' the current date.
        Dim lngCount As Long
        Dim dtmTemp As Date
        
        If dtmDate = 0 Then
            dtmDate = Date
        End If
        
        dtmTemp = dtmDate
        For lngCount = 1 To lngDays
            dtmTemp = dhNextWorkdayA(dtmTemp, adtmDates)
        Next lngCount
        dhAddWorkDaysA = dtmTemp
    End Function
    Public Function dhNextWorkdayA( _
    Optional dtmDate As Date = 0, _
    Optional adtmDates As Variant = Empty) As Date
        
        ' Return the next working day after the specified date.
        
        ' Modified from code in
        ' "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' Requires:
        '   SkipHolidays
        '   IsWeekend
        
        ' In:
        '   dtmDate:
        '       date on which to start looking.
        '       Use the current date, if none was specified.
        '   adtmDates (Optional):
        '       Array containing holiday dates. Can also be a single
        '       date value.
        ' Out:
        '   Return Value:
        '       The date of the next working day, taking
        '       into account weekends and holidays.
        ' Example:
        '   ' Find the next working date after 5/30/97
        '   dtmDate = dhNextWorkdayA(#5/23/1997#, #5/26/97#)
        '   ' dtmDate should be 5/27/97, because 5/26/97 is Memorial day.
        
        ' Did the caller pass in a date? If not, use
        ' the current date.
        If dtmDate = 0 Then
            dtmDate = Date
        End If
        
        dhNextWorkdayA = SkipHolidaysA(adtmDates, dtmDate + 1, 1)
    End Function
    
    
    Public Function dhPreviousWorkdayA( _
    Optional dtmDate As Date = 0, _
    Optional adtmDates As Variant = Empty) As Date
        
        ' Return the previous working day before the specified date.
        
        ' Modified from code in
        ' "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' Requires:
        '   SkipHolidays
        '   IsWeekend
        
        ' In:
        '   dtmDate:
        '       date on which to start looking.
        '       Use the current date, if none was specified.
        '   adtmDates (Optional):
        '       Array containing holiday dates. Can also be a single
        '       date value.
        ' Out:
        '   Return Value:
        '       The date of the previous working day, taking
        '       into account weekends and holidays.
        ' Example:
        '   ' Find the next working date before 1/1/2000
        
        '   dtmDate = dhPreviousWorkdayA(#1/1/2000#, Array(#12/31/1999#, #1/1/2000#))
        '   ' dtmDate should be 12/30/1999, because of the New Year's holidays.
        
        ' Did the caller pass in a date? If not, use
        ' the current date.
        If dtmDate = 0 Then
            dtmDate = Date
        End If
        
        dhPreviousWorkdayA = SkipHolidaysA(adtmDates, dtmDate - 1, -1)
    End Function
    
    
    Public Function dhFirstWorkdayInMonthA( _
    Optional dtmDate As Date = 0, _
    Optional adtmDates As Variant = Empty) As Date
        
        ' Return the first working day in the month specified.
        
        ' Modified from code in
        ' "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' Requires:
        '   SkipHolidays
        '   IsWeekend
        
        ' In:
        '   dtmDate:
        '       date within the month of interest.
        '       Use the current date, if none was specified.
        '   adtmDates (Optional):
        '       Array containing holiday dates. Can also be a single
        '       date value.
        ' Out:
        '   Return Value:
        '       The date of the first working day in the month, taking
        '       into account weekends and holidays.
        ' Example:
        '   ' Find the first working day in 1999
        '   dtmDate = dhFirstWorkdayInMonthA(#1/1/1999#, #1/1/1999#)
        
        Dim dtmTemp As Date
        
        ' Did the caller pass in a date? If not, use
        ' the current date.
        If dtmDate = 0 Then
            dtmDate = Date
        End If
        
        dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate), 1)
        dhFirstWorkdayInMonthA = SkipHolidaysA(adtmDates, dtmTemp, 1)
    End Function
    
    
    Public Function dhLastWorkdayInMonthA( _
    Optional dtmDate As Date = 0, _
    Optional adtmDates As Variant = Empty) As Date
        
        ' Return the last working day in the month specified.
        
        ' Modified from code in
        ' "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' Requires:
        '   SkipHolidays
        '   IsWeekend
        
        ' In:
        '   dtmDate:
        '       date within the month of interest.
        '       Use the current date, if none was specified.
        '   adtmDates (Optional):
        '       Array containing holiday dates. Can also be a single
        '       date value.
        ' Out:
        '   Return Value:
        '       The date of the last working day in the month, taking
        '       into account weekends and holidays.
        ' Example:
        '   ' Find the last working day in 1999
        '   dtmDate = dhLastWorkdayInMonthA(#12/1/1999#, #12/31/1999#)
        
        Dim dtmTemp As Date
        
        ' Did the caller pass in a date? If not, use
        ' the current date.
        If dtmDate = 0 Then
            dtmDate = Date
        End If
        
        dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
        dhLastWorkdayInMonthA = SkipHolidaysA(adtmDates, dtmTemp, -1)
    End Function
    
    
    Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal dtmEnd As Date, _
     Optional adtmDates As Variant = Empty) _
     As Integer
    
    
        ' Count the business days (not counting weekends/holidays) in
        ' a given date range.
        
        ' Modified from code in
        ' "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' Requires:
        '   SkipHolidays
        '   CountHolidays
        '   IsWeekend
        
        ' In:
        '   dtmStart:
        '       Date specifying the start of the range (inclusive)
        '   dtmEnd:
        '       Date specifying the end of the range (inclusive)
        '       (dates will be swapped if out of order)
        '   adtmDates (Optional):
        '       Array containing holiday dates. Can also be a single
        '       date value.
        ' Out:
        '   Return Value:
        '       Number of working days (not counting weekends and optionally, holidays)
        '       in the specified range.
        ' Example:
        '   Debug.Print dhCountWorkdaysA(#7/2/2000#, #7/5/2000#, _
        '    Array(#1/1/2000#, #7/4/2000#))
        '
        '   returns 2, because 7/2/2000 is Sunday, 7/4/2000 is a holiday,
        '   leaving 7/3 and 7/5 as workdays.
        
        Dim intDays As Integer
        Dim dtmTemp As Date
        Dim intSubtract As Integer
        
        ' Swap the dates if necessary.>
        If dtmEnd < dtmStart Then
            dtmTemp = dtmStart
            dtmStart = dtmEnd
            dtmEnd = dtmTemp
        End If
        
        ' Get the start and end dates to be weekdays.
        dtmStart = SkipHolidaysA(adtmDates, dtmStart, 1)
        dtmEnd = SkipHolidaysA(adtmDates, dtmEnd, -1)
        If dtmStart > dtmEnd Then
            ' Sorry, no Workdays to be had. Just return 0.
            dhCountWorkdaysA = 0
        Else
            intDays = dtmEnd - dtmStart + 1
            
            ' Subtract off weekend days.  Do this by figuring out how
            ' many calendar weeks there are between the dates, and
            ' multiplying the difference by two (because there are two
            ' weekend days for each week). That is, if the difference
            ' is 0, the two days are in the same week. If the
            ' difference is 1, then we have two weekend days.
            intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)
            
            ' The answer to our quest is all the weekdays, minus any
            ' holidays found in the table.
            intSubtract = intSubtract + _
             CountHolidaysA(adtmDates, dtmStart, dtmEnd)
            
            dhCountWorkdaysA = intDays - intSubtract
        End If
    End Function
    
    
    Private Function CountHolidaysA( _
    adtmDates As Variant, _
    dtmStart As Date, dtmEnd As Date) As Long
    
    
        ' Count holidays between two end dates.
        '
        ' Modified from code in
        ' "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' Required by:
        '   dhCountWorkdays
        
        ' Requires:
        '   IsWeekend
        
        
        Dim lngItem As Long
        Dim lngCount As Long
        Dim blnFound As Long
        Dim dtmTemp As Date
        
        On Error GoTo HandleErr
        lngCount = 0
        Select Case VarType(adtmDates)
            Case vbArray + vbDate, vbArray + vbVariant
                ' You got an array of variants, or of dates.
                ' Loop through, looking for non-weekend values
                ' between the two endpoints.
                For lngItem = LBound(adtmDates) To UBound(adtmDates)
                    dtmTemp = adtmDates(lngItem)
                    If dtmTemp >= dtmStart And dtmTemp <= dtmEnd Then
                        If Not IsWeekend(dtmTemp) Then
                            lngCount = lngCount + 1
                        End If
                    End If
                Next lngItem
            Case vbDate
                ' You got one date. So see if it's a non-weekend
                ' date between the two endpoints.
                If adtmDates >= dtmStart And adtmDates <= dtmEnd Then
                    If Not IsWeekend(adtmDates) Then
                        lngCount = 1
                    End If
                End If
        End Select
    
    
    ExitHere:
        CountHolidaysA = lngCount
        Exit Function
        
    HandleErr:
        ' No matter what the error, just
        ' return without complaining.
        ' The worst that could happen is that the code
        ' include a holiday as a real day, even if
        ' it's in the table.
        Resume ExitHere
    End Function
    
    
    Private Function FindItemInArray(varItemToFind As Variant, _
    avarItemsToSearch As Variant) As Boolean
        Dim lngItem As Long
        
        On Error GoTo HandleErrors
        
        For lngItem = LBound(avarItemsToSearch) To UBound(avarItemsToSearch)
            If avarItemsToSearch(lngItem) = varItemToFind Then
                FindItemInArray = True
                GoTo ExitHere
            End If
        Next lngItem
        
    ExitHere:
        Exit Function
        
    HandleErrors:
        ' Do nothing at all.
        ' Return False.
        Resume ExitHere
    End Function
    
    
    Private Function IsWeekend(dtmTemp As Variant) As Boolean
        ' If your weekends aren't Saturday (day 7) and Sunday (day 1),
        ' change this routine to return True for whatever days
        ' you DO treat as weekend days.
        
        ' Modified from code in "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' Required by:
        '   SkipHolidays
        '   dhFirstWorkdayInMonth
        '   dbLastWorkdayInMonth
        '   dhNextWorkday
        '   dhPreviousWorkday
        '   dhCountWorkdays
        
        If VarType(dtmTemp) = vbDate Then
            Select Case WeekDay(dtmTemp)
                Case vbSaturday, vbSunday
                    IsWeekend = True
                Case Else
                    IsWeekend = False
            End Select
        End If
    End Function
    
    
    Private Function SkipHolidaysA( _
    adtmDates As Variant, _
    dtmTemp As Date, intIncrement As Integer) As Date
        ' Skip weekend days, and holidays in the array referred to by adtmDates.
        ' Return dtmTemp + as many days as it takes to get to a day that's not
        ' a holiday or weekend.
        
        ' Modified from code in
        ' "Visual Basic Language Developer's Handbook"
        ' by Ken Getz and Mike Gilbert
        ' Copyright 2000; Sybex, Inc. All rights reserved.
        
        ' Required by:
        '   dhFirstWorkdayInMonthA
        '   dbLastWorkdayInMonthA
        '   dhNextWorkdayA
        '   dhPreviousWorkdayA
        '   dhCountWorkdaysA
        
        ' Requires:
        '   IsWeekend
        
        Dim strCriteria As String
        Dim strFieldName As String
        Dim lngItem As Long
        Dim blnFound As Boolean
        
        On Error GoTo HandleErrors
        
        ' Move up to the first Monday/last Friday, if the first/last
        ' of the month was a weekend date. Then skip holidays.
        ' Repeat this entire process until you get to a weekday.
        ' Unless adtmDates an item for every day in the year (!)
        ' this should finally converge on a weekday.
        
        Do
            Do While IsWeekend(dtmTemp)
                dtmTemp = dtmTemp + intIncrement
            Loop
            Select Case VarType(adtmDates)
                Case vbArray + vbDate, vbArray + vbVariant
                    Do
                        blnFound = FindItemInArray(dtmTemp, adtmDates)
                        If blnFound Then
                            dtmTemp = dtmTemp + intIncrement
                        End If
                    Loop Until Not blnFound
                Case vbDate
                    If dtmTemp = adtmDates Then
                        dtmTemp = dtmTemp + intIncrement
                    End If
            End Select
        Loop Until Not IsWeekend(dtmTemp)
        
    ExitHere:
        SkipHolidaysA = dtmTemp
        Exit Function
        
    HandleErrors:
        ' No matter what the error, just
        ' return without complaining.
        ' The worst that could happen is that we
        ' include a holiday as a real day, even if
        ' it's in the array.
        Resume ExitHere
    End Function
    ' ********* Code End **************
    Cheers,
    Vlad

  8. #8
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    The moving back and forth is not a constant, can be anywhere from 0 to three days. Why is a complicated story involving regular weekends, three day weekends including a Monday, three day weekends including a Friday, what the stock market did on the date in question, etc., etc. So I was hoping for something like Excel's "ActiveCell(-3,0)" feature. C'est la vie, will program around it either with an array of values or with moveback/movenext. Thanks to all for your inputs.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Passing dynamic name of array to a Function
    By mcarval22 in forum Programming
    Replies: 41
    Last Post: 07-29-2016, 04:53 PM
  2. passing a form name to a function woes
    By newbieX in forum Programming
    Replies: 4
    Last Post: 09-26-2014, 02:26 PM
  3. Simple passing into function question
    By Ruegen in forum Programming
    Replies: 3
    Last Post: 07-24-2014, 11:23 PM
  4. VBA passing rs! into function
    By Ruegen in forum Programming
    Replies: 15
    Last Post: 03-17-2014, 04:01 PM
  5. Replies: 11
    Last Post: 05-17-2013, 06:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums