Workday function error

[shank]

I'm experimenting with function: dhAddWorkDaysA
Found here: http://access.mvps.org/access/datetime/date0012.htm


It works great if I don't use the Array.

SELECT dhAddWorkDaysA(10, Now()) AS Days;

But if I use the Array, I get error: Data type mismatch in criteria expression.
What am I getting wrong?

SELECT dhAddWorkDaysA(10, Now(), Array(#11/8/2021#, #11/9/2021#)) AS Days;

Thanks!

[Welshgasman]

Why not walk through the code with F8 ?
Plus the second parameter expects a date, not date and time, which is what Now() produces?

[pbaldy]

The function expects a date. What result are you expecting by passing an array?

[Micron]

I cannot recall ever seeing an array used like that. Usually you retrieve an array value by referring to the index number of the item. Array(1) would return whatever is stored in that index/position (zero being the item in the 1st position). If you found posted code that uses what you're using, could you provide a link for that? Even if you could, the function only takes 3 parameters and you'd be trying to provide 4 because what you show is a 2 dimensional array.

[Welshgasman]

Works for me? I think?, you do the math

? dhAddWorkDaysA(10, Date(), Array(#11/8/2021#, #11/9/2021#))
18/11/2021

@Micron,
Here are the comments?

' 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).

[Welshgasman]

Why do you need a Select?
I agree it falls over with that error, if you try to use it in a query statement, but why?

[shank]

Why do you need a Select?
I agree it falls over with that error, if you try to use it in a query statement, but why?

I was using a select statement to experiment how I can use to solve my needs. For UPS shipping travel days, assume 5 days travel time, I need to calculate an ETA date without counting weekends. Because the function has capabilities of working around holidays with the Array, I wanted to experiment with that as well.

Thanks!

[pbaldy]

I'm blind, did not notice the 3rd argument could be an array. Sorry for the confusion.

[shank]

I found this thread: https://microsoft.public.access.quer...ria-expression
that describes the same issues "Data type mismatch in criteria expression" I'm having.
The resolve appears to be a Wrapper function and the below code was posted. But not all final details.

I'm getting an error...
Error: Item not found in this collection
Line: varA(lngA) = rst!MyDate.Value

I assume it's not getting the dates I put in a Table Holidays with field [Holidays].
If I'm correct, how do I get the recordset to populate?

Public Function MyCountWorkDays(lngDays As Long, _
Optional dtmDate As Date = 0) As Date
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngA As Long
Dim varA() As Variant


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Holidays", dbOpenDynaset, dbReadOnly)


If rst.EOF = False And rst.BOF = False Then
rst.MoveLast
rst.MoveFirst
ReDim varA(0 To rst.RecordCount - 1) As Variant
lngA = 0
Do While rst.EOF = False
varA(lngA) = rst!MyDate.Value
rst.MoveNext
lngA = lngA + 1
Loop
End If


rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing


MyCountWorkDays = dhAddWorkDaysA(lngDays, dtmDate, varA)
End Function

I'm pasting the below original code that gets the data mismatch error.

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

Thank you!

[Welshgasman]

You use the correct name of your date field?
I would expect MyDate is just the example name?
You are meant to replace that with YourDate but not literally, use the real name of your date field.

In your case that appears to be Holidays ?

[shank]

Yes, that was the issue. I edited this post. I had another issue, but for whatever reason, it's now working as expected. I'm going to post my end code herein.

I have a table:

Holidays
11/8/2021
11/9/2021
11/10/2021

Query: SELECT MyCountWorkDays(3,Date()) AS Days;
Today's date happens to be: 11/3/2021.
Query results: 11/11/2021

Public Function MyCountWorkDays(lngDays As Long, _
Optional dtmDate As Date = 0) As Date
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngA As Long
Dim varA() As Variant


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Holidays", dbOpenDynaset, dbReadOnly)


If rst.EOF = False And rst.BOF = False Then
rst.MoveLast
rst.MoveFirst
ReDim varA(0 To rst.RecordCount - 1) As Variant
lngA = 0
Do While rst.EOF = False
varA(lngA) = rst!Holidays.Value
rst.MoveNext
lngA = lngA + 1
Loop
End If


rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing


MyCountWorkDays = dhAddWorkDaysA(lngDays, dtmDate, varA)
End Function

' ********* Code Start **************
'
' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.
'


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

Thank you all for your guidance!