Data Type mismatch Criteria Expression

[Guest]

I've made a function that would calculate only business dates between current
data and a date field (Nxt Cpn Dt). I've used the function dhCountWorkdaysA
in a query, which works well but if I put a number for the criteria i have
the error "data type mismatch criteria expression. Here is part of the sql
WHERE (((REPO_TABLE_1.[Nxt Cpn Dt])>Now()) AND (([Nxt Cpn Dt]-[Collateral
maturity date])<1) AND ((dhCountWorkdaysA([Nxt Cpn Dt],Now())-1)=5));

Plase advise

 

[Ken Snell \(MVP\)]

Please post the code for the function that you wrote. Most likely, the error
is caused by how you've declared the variables and arguments in the function
such that they won't handle all the types of data that the function might be
given. Common problem is passing a Null value in an argument to a variable
that is "dim'd" as any nonvariant variable type.

 

[Guest]

Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal dtmEnd As
Date, _
Optional adtmDates As Variant = Empty) _
As Integer

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

intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)

intSubtract = intSubtract + _
CountHolidaysA(adtmDates, dtmStart, dtmEnd)

dhCountWorkdaysA = intDays - intSubtract
End If
End Function

Please post the code for the function that you wrote. Most likely, the error
is caused by how you've declared the variables and arguments in the function
such that they won't handle all the types of data that the function might be
given. Common problem is passing a Null value in an argument to a variable
that is "dim'd" as any nonvariant variable type.

--

Ken Snell
<MS ACCESS MVP>

I've made a function that would calculate only business dates between
current
data and a date field (Nxt Cpn Dt). I've used the function
dhCountWorkdaysA
in a query, which works well but if I put a number for the criteria i have
the error "data type mismatch criteria expression. Here is part of the
sql
WHERE (((REPO_TABLE_1.[Nxt Cpn Dt])>Now()) AND (([Nxt Cpn Dt]-[Collateral
maturity date])<1) AND ((dhCountWorkdaysA([Nxt Cpn Dt],Now())-1)=5));

Plase advise

 

[Douglas J. Steele]

If [Nxt Cpn Dt] is null for any records, you calls to dhCountWorkdaysA will
fail. You could change the declaration to Variants, rather than Dates, and
check for Null inside the function, or you could pick a default date and use
it in conjunction with the Nz function when you're calling the function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal dtmEnd As
Date, _
Optional adtmDates As Variant = Empty) _
As Integer

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

intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)

intSubtract = intSubtract + _
CountHolidaysA(adtmDates, dtmStart, dtmEnd)

dhCountWorkdaysA = intDays - intSubtract
End If
End Function

Please post the code for the function that you wrote. Most likely, the
error
is caused by how you've declared the variables and arguments in the
function
such that they won't handle all the types of data that the function might
be
given. Common problem is passing a Null value in an argument to a
variable
that is "dim'd" as any nonvariant variable type.

--

Ken Snell
<MS ACCESS MVP>

I've made a function that would calculate only business dates between
current
data and a date field (Nxt Cpn Dt). I've used the function
dhCountWorkdaysA
in a query, which works well but if I put a number for the criteria i
have
the error "data type mismatch criteria expression. Here is part of the
sql
WHERE (((REPO_TABLE_1.[Nxt Cpn Dt])>Now()) AND (([Nxt Cpn
Dt]-[Collateral
maturity date])<1) AND ((dhCountWorkdaysA([Nxt Cpn Dt],Now())-1)=5));

Plase advise

 

[Guest]

Hi Doug, Ken –

I’m having something like the same issue as Alex. When I run an Access2000
query with a “dhAddWorkDaysA†function, I get the same error message “data
type mismatch in criteria expressionâ€. The code is posted below, but I only
run into that error message when I add an Array for a holiday, it runs fine
when I run something like “dhAddWorkDaysA(2,[Cal_Date])â€. I believe it’s
something that I probably haven’t declared, but I’ve changed a few things
(Variant and Date declarations) in the Module and still have had no luck.
Any help would be hugely appreciated! tia, vf

Module:

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Date) 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

Query:

SELECT tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#)) AS TicketNewDueDate
FROM tbl_CutoffDate_Nov11
GROUP BY tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
tbl_CutoffDate_Nov11.CutoffDate, tbl_CutoffDate_Nov11.TicketDueDate
ORDER BY dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#));

Again, thanks for your help!!!!

If [Nxt Cpn Dt] is null for any records, you calls to dhCountWorkdaysA will
fail. You could change the declaration to Variants, rather than Dates, and
check for Null inside the function, or you could pick a default date and use
it in conjunction with the Nz function when you're calling the function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal dtmEnd As
Date, _
Optional adtmDates As Variant = Empty) _
As Integer

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

intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)

intSubtract = intSubtract + _
CountHolidaysA(adtmDates, dtmStart, dtmEnd)

dhCountWorkdaysA = intDays - intSubtract
End If
End Function

Please post the code for the function that you wrote. Most likely, the
error
is caused by how you've declared the variables and arguments in the
function
such that they won't handle all the types of data that the function might
be
given. Common problem is passing a Null value in an argument to a
variable
that is "dim'd" as any nonvariant variable type.

--

Ken Snell
<MS ACCESS MVP>

I've made a function that would calculate only business dates between
current
data and a date field (Nxt Cpn Dt). I've used the function
dhCountWorkdaysA
in a query, which works well but if I put a number for the criteria i
have
the error "data type mismatch criteria expression. Here is part of the
sql
WHERE (((REPO_TABLE_1.[Nxt Cpn Dt])>Now()) AND (([Nxt Cpn
Dt]-[Collateral
maturity date])<1) AND ((dhCountWorkdaysA([Nxt Cpn Dt],Now())-1)=5));

Plase advise

 

[Ken Snell \(MVP\)]

Take a look at the original function that is posted on The ACCESS Web:
http://www.mvps.org/access/datetime/date0012.htm

Note that the original function declares the third argument of the function
as a Variant data type. You have declared it as a Date type. Needs to be a
Variant type.
--

Ken Snell
<MS ACCESS MVP>

Hi Doug, Ken -

I'm having something like the same issue as Alex. When I run an
Access2000
query with a "dhAddWorkDaysA" function, I get the same error message "data
type mismatch in criteria expression". The code is posted below, but I
only
run into that error message when I add an Array for a holiday, it runs
fine
when I run something like "dhAddWorkDaysA(2,[Cal_Date])". I believe it's
something that I probably haven't declared, but I've changed a few things
(Variant and Date declarations) in the Module and still have had no luck.
Any help would be hugely appreciated! tia, vf

Module:

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Date) 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

Query:

SELECT tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#)) AS TicketNewDueDate
FROM tbl_CutoffDate_Nov11
GROUP BY tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
tbl_CutoffDate_Nov11.CutoffDate, tbl_CutoffDate_Nov11.TicketDueDate
ORDER BY dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#));

Again, thanks for your help!!!!

If [Nxt Cpn Dt] is null for any records, you calls to dhCountWorkdaysA
will
fail. You could change the declaration to Variants, rather than Dates,
and
check for Null inside the function, or you could pick a default date and
use
it in conjunction with the Nz function when you're calling the function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal dtmEnd
As
Date, _
Optional adtmDates As Variant = Empty) _
As Integer

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

intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)

intSubtract = intSubtract + _
CountHolidaysA(adtmDates, dtmStart, dtmEnd)

dhCountWorkdaysA = intDays - intSubtract
End If
End Function



:

Please post the code for the function that you wrote. Most likely, the
error
is caused by how you've declared the variables and arguments in the
function
such that they won't handle all the types of data that the function
might
be
given. Common problem is passing a Null value in an argument to a
variable
that is "dim'd" as any nonvariant variable type.

--

Ken Snell
<MS ACCESS MVP>

I've made a function that would calculate only business dates
between
current
data and a date field (Nxt Cpn Dt). I've used the function
dhCountWorkdaysA
in a query, which works well but if I put a number for the criteria
i
have
the error "data type mismatch criteria expression. Here is part of
the
sql
WHERE (((REPO_TABLE_1.[Nxt Cpn Dt])>Now()) AND (([Nxt Cpn
Dt]-[Collateral
maturity date])<1) AND ((dhCountWorkdaysA([Nxt Cpn
Dt],Now())-1)=5));

Plase advise

 

[Guest]

Hi Ken -

Thanx for the quick reply and I totally feel like a knucklehead here, but
I'm still coming up with the same error "Data type Mismatch..." even after
changing the third argument of the function (adtmDates) to a Variant data
type. I've even played around with that function and changed it as follows,
with no luck.

Optional adtmDates As Variant) As Date
Optional adtmDates As Variant) As Variant
Optional adtmDates As Date) As Date
Optional adtmDates As Date) As Variant

And I've added from other functions-
Optional adtmDates As Variant = Empty) As Date
and still the same error message.

Again, I'll include the code that is currently in my db and giving me the
error (it's below) and any other suggestions that you have, I'd really
appreciate them. And if you need any other information from me, please let
me know.


Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

Many thanx for your time and patience here,
marc

Take a look at the original function that is posted on The ACCESS Web:
http://www.mvps.org/access/datetime/date0012.htm

Note that the original function declares the third argument of the function
as a Variant data type. You have declared it as a Date type. Needs to be a
Variant type.
--

Ken Snell
<MS ACCESS MVP>

Hi Doug, Ken -

I'm having something like the same issue as Alex. When I run an
Access2000
query with a "dhAddWorkDaysA" function, I get the same error message "data
type mismatch in criteria expression". The code is posted below, but I
only
run into that error message when I add an Array for a holiday, it runs
fine
when I run something like "dhAddWorkDaysA(2,[Cal_Date])". I believe it's
something that I probably haven't declared, but I've changed a few things
(Variant and Date declarations) in the Module and still have had no luck.
Any help would be hugely appreciated! tia, vf

Module:

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Date) 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

Query:

SELECT tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#)) AS TicketNewDueDate
FROM tbl_CutoffDate_Nov11
GROUP BY tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
tbl_CutoffDate_Nov11.CutoffDate, tbl_CutoffDate_Nov11.TicketDueDate
ORDER BY dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#));

Again, thanks for your help!!!!

If [Nxt Cpn Dt] is null for any records, you calls to dhCountWorkdaysA
will
fail. You could change the declaration to Variants, rather than Dates,
and
check for Null inside the function, or you could pick a default date and
use
it in conjunction with the Nz function when you're calling the function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal dtmEnd
As
Date, _
Optional adtmDates As Variant = Empty) _
As Integer

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

intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)

intSubtract = intSubtract + _
CountHolidaysA(adtmDates, dtmStart, dtmEnd)

dhCountWorkdaysA = intDays - intSubtract
End If
End Function



:

Please post the code for the function that you wrote. Most likely, the
error
is caused by how you've declared the variables and arguments in the
function
such that they won't handle all the types of data that the function
might
be
given. Common problem is passing a Null value in an argument to a
variable
that is "dim'd" as any nonvariant variable type.

--

Ken Snell
<MS ACCESS MVP>

I've made a function that would calculate only business dates
between
current
data and a date field (Nxt Cpn Dt). I've used the function
dhCountWorkdaysA
in a query, which works well but if I put a number for the criteria
i
have
the error "data type mismatch criteria expression. Here is part of
the
sql
WHERE (((REPO_TABLE_1.[Nxt Cpn Dt])>Now()) AND (([Nxt Cpn
Dt]-[Collateral
maturity date])<1) AND ((dhCountWorkdaysA([Nxt Cpn
Dt],Now())-1)=5));

Plase advise

 

[Ken Snell \(MVP\)]

Be sure that the second argument in the dhNextWorkdayA function also is
declared as a Variant data type.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken -

Thanx for the quick reply and I totally feel like a knucklehead here, but
I'm still coming up with the same error "Data type Mismatch..." even after
changing the third argument of the function (adtmDates) to a Variant data
type. I've even played around with that function and changed it as
follows,
with no luck.

Optional adtmDates As Variant) As Date
Optional adtmDates As Variant) As Variant
Optional adtmDates As Date) As Date
Optional adtmDates As Date) As Variant

And I've added from other functions-
Optional adtmDates As Variant = Empty) As Date
and still the same error message.

Again, I'll include the code that is currently in my db and giving me the
error (it's below) and any other suggestions that you have, I'd really
appreciate them. And if you need any other information from me, please
let
me know.


Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

Many thanx for your time and patience here,
marc

Take a look at the original function that is posted on The ACCESS Web:
http://www.mvps.org/access/datetime/date0012.htm

Note that the original function declares the third argument of the
function
as a Variant data type. You have declared it as a Date type. Needs to be
a
Variant type.
--

Ken Snell
<MS ACCESS MVP>

Hi Doug, Ken -

I'm having something like the same issue as Alex. When I run an
Access2000
query with a "dhAddWorkDaysA" function, I get the same error message
"data
type mismatch in criteria expression". The code is posted below, but I
only
run into that error message when I add an Array for a holiday, it runs
fine
when I run something like "dhAddWorkDaysA(2,[Cal_Date])". I believe
it's
something that I probably haven't declared, but I've changed a few
things
(Variant and Date declarations) in the Module and still have had no
luck.
Any help would be hugely appreciated! tia, vf

Module:

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Date) 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

Query:

SELECT tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#)) AS TicketNewDueDate
FROM tbl_CutoffDate_Nov11
GROUP BY tbl_CutoffDate_Nov11.Station_ID,
tbl_CutoffDate_Nov11.Cal_Date,
tbl_CutoffDate_Nov11.CutoffDate, tbl_CutoffDate_Nov11.TicketDueDate
ORDER BY dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#));

Again, thanks for your help!!!!


:

If [Nxt Cpn Dt] is null for any records, you calls to dhCountWorkdaysA
will
fail. You could change the declaration to Variants, rather than Dates,
and
check for Null inside the function, or you could pick a default date
and
use
it in conjunction with the Nz function when you're calling the
function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal
dtmEnd
As
Date, _
Optional adtmDates As Variant = Empty) _
As Integer

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

intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)

intSubtract = intSubtract + _
CountHolidaysA(adtmDates, dtmStart, dtmEnd)

dhCountWorkdaysA = intDays - intSubtract
End If
End Function



:

Please post the code for the function that you wrote. Most likely,
the
error
is caused by how you've declared the variables and arguments in the
function
such that they won't handle all the types of data that the function
might
be
given. Common problem is passing a Null value in an argument to a
variable
that is "dim'd" as any nonvariant variable type.

--

Ken Snell
<MS ACCESS MVP>

I've made a function that would calculate only business dates
between
current
data and a date field (Nxt Cpn Dt). I've used the function
dhCountWorkdaysA
in a query, which works well but if I put a number for the
criteria
i
have
the error "data type mismatch criteria expression. Here is part
of
the
sql
WHERE (((REPO_TABLE_1.[Nxt Cpn Dt])>Now()) AND (([Nxt Cpn
Dt]-[Collateral
maturity date])<1) AND ((dhCountWorkdaysA([Nxt Cpn
Dt],Now())-1)=5));

Plase advise

 

[Guest]

Hi Ken -

Thanx again for the quick reply.

I tried changing the 2nd argument to Variant and that did not help either.
Still getting the same error message.

I look at the code and it's not that difficult to follow, and it works when
I do not include any Arrays (holidays). It just has to be something simple
that I'm overlooking.

Might you have any other suggestions?
marc

Be sure that the second argument in the dhNextWorkdayA function also is
declared as a Variant data type.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken -

Thanx for the quick reply and I totally feel like a knucklehead here, but
I'm still coming up with the same error "Data type Mismatch..." even after
changing the third argument of the function (adtmDates) to a Variant data
type. I've even played around with that function and changed it as
follows,
with no luck.

Optional adtmDates As Variant) As Date
Optional adtmDates As Variant) As Variant
Optional adtmDates As Date) As Date
Optional adtmDates As Date) As Variant

And I've added from other functions-
Optional adtmDates As Variant = Empty) As Date
and still the same error message.

Again, I'll include the code that is currently in my db and giving me the
error (it's below) and any other suggestions that you have, I'd really
appreciate them. And if you need any other information from me, please
let
me know.


Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

Many thanx for your time and patience here,
marc

Take a look at the original function that is posted on The ACCESS Web:
http://www.mvps.org/access/datetime/date0012.htm

Note that the original function declares the third argument of the
function
as a Variant data type. You have declared it as a Date type. Needs to be
a
Variant type.
--

Ken Snell
<MS ACCESS MVP>

Hi Doug, Ken -

I'm having something like the same issue as Alex. When I run an
Access2000
query with a "dhAddWorkDaysA" function, I get the same error message
"data
type mismatch in criteria expression". The code is posted below, but I
only
run into that error message when I add an Array for a holiday, it runs
fine
when I run something like "dhAddWorkDaysA(2,[Cal_Date])". I believe
it's
something that I probably haven't declared, but I've changed a few
things
(Variant and Date declarations) in the Module and still have had no
luck.
Any help would be hugely appreciated! tia, vf

Module:

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Date) 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

Query:

SELECT tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#)) AS TicketNewDueDate
FROM tbl_CutoffDate_Nov11
GROUP BY tbl_CutoffDate_Nov11.Station_ID,
tbl_CutoffDate_Nov11.Cal_Date,
tbl_CutoffDate_Nov11.CutoffDate, tbl_CutoffDate_Nov11.TicketDueDate
ORDER BY dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#));

Again, thanks for your help!!!!


:

If [Nxt Cpn Dt] is null for any records, you calls to dhCountWorkdaysA
will
fail. You could change the declaration to Variants, rather than Dates,
and
check for Null inside the function, or you could pick a default date
and
use
it in conjunction with the Nz function when you're calling the
function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal
dtmEnd
As
Date, _
Optional adtmDates As Variant = Empty) _
As Integer

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

intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)

intSubtract = intSubtract + _
CountHolidaysA(adtmDates, dtmStart, dtmEnd)

dhCountWorkdaysA = intDays - intSubtract
End If
End Function



:

Please post the code for the function that you wrote. Most likely,
the
error
is caused by how you've declared the variables and arguments in the
function
such that they won't handle all the types of data that the function
might
be
given. Common problem is passing a Null value in an argument to a
variable
that is "dim'd" as any nonvariant variable type.

--

Ken Snell
<MS ACCESS MVP>

I've made a function that would calculate only business dates
between
current
data and a date field (Nxt Cpn Dt). I've used the function
dhCountWorkdaysA
in a query, which works well but if I put a number for the
criteria
i
have
the error "data type mismatch criteria expression. Here is part
of
the
sql
WHERE (((REPO_TABLE_1.[Nxt Cpn Dt])>Now()) AND (([Nxt Cpn
Dt]-[Collateral
maturity date])<1) AND ((dhCountWorkdaysA([Nxt Cpn
Dt],Now())-1)=5));

Plase advise

 

[Ken Snell \(MVP\)]

Post the entire amount of VBA code that you're using, including all the
functions.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken -

Thanx again for the quick reply.

I tried changing the 2nd argument to Variant and that did not help either.
Still getting the same error message.

I look at the code and it's not that difficult to follow, and it works
when
I do not include any Arrays (holidays). It just has to be something
simple
that I'm overlooking.

Might you have any other suggestions?
marc

Be sure that the second argument in the dhNextWorkdayA function also is
declared as a Variant data type.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken -

Thanx for the quick reply and I totally feel like a knucklehead here,
but
I'm still coming up with the same error "Data type Mismatch..." even
after
changing the third argument of the function (adtmDates) to a Variant
data
type. I've even played around with that function and changed it as
follows,
with no luck.

Optional adtmDates As Variant) As Date
Optional adtmDates As Variant) As Variant
Optional adtmDates As Date) As Date
Optional adtmDates As Date) As Variant

And I've added from other functions-
Optional adtmDates As Variant = Empty) As Date
and still the same error message.

Again, I'll include the code that is currently in my db and giving me
the
error (it's below) and any other suggestions that you have, I'd really
appreciate them. And if you need any other information from me, please
let
me know.


Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

Many thanx for your time and patience here,
marc

:

Take a look at the original function that is posted on The ACCESS Web:
http://www.mvps.org/access/datetime/date0012.htm

Note that the original function declares the third argument of the
function
as a Variant data type. You have declared it as a Date type. Needs to
be
a
Variant type.
--

Ken Snell
<MS ACCESS MVP>

Hi Doug, Ken -

I'm having something like the same issue as Alex. When I run an
Access2000
query with a "dhAddWorkDaysA" function, I get the same error message
"data
type mismatch in criteria expression". The code is posted below,
but I
only
run into that error message when I add an Array for a holiday, it
runs
fine
when I run something like "dhAddWorkDaysA(2,[Cal_Date])". I believe
it's
something that I probably haven't declared, but I've changed a few
things
(Variant and Date declarations) in the Module and still have had no
luck.
Any help would be hugely appreciated! tia, vf

Module:

Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Date) 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

Query:

SELECT tbl_CutoffDate_Nov11.Station_ID,
tbl_CutoffDate_Nov11.Cal_Date,
dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#)) AS TicketNewDueDate
FROM tbl_CutoffDate_Nov11
GROUP BY tbl_CutoffDate_Nov11.Station_ID,
tbl_CutoffDate_Nov11.Cal_Date,
tbl_CutoffDate_Nov11.CutoffDate, tbl_CutoffDate_Nov11.TicketDueDate
ORDER BY dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#));

Again, thanks for your help!!!!


:

If [Nxt Cpn Dt] is null for any records, you calls to
dhCountWorkdaysA
will
fail. You could change the declaration to Variants, rather than
Dates,
and
check for Null inside the function, or you could pick a default
date
and
use
it in conjunction with the Nz function when you're calling the
function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Public Function dhCountWorkdaysA(ByVal dtmStart As Date, ByVal
dtmEnd
As
Date, _
Optional adtmDates As Variant = Empty) _
As Integer

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

intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)

intSubtract = intSubtract + _
CountHolidaysA(adtmDates, dtmStart, dtmEnd)

dhCountWorkdaysA = intDays - intSubtract
End If
End Function



:

Please post the code for the function that you wrote. Most
likely,
the
error
is caused by how you've declared the variables and arguments in
the
function
such that they won't handle all the types of data that the
function
might
be
given. Common problem is passing a Null value in an argument to
a
variable
that is "dim'd" as any nonvariant variable type.

--

Ken Snell
<MS ACCESS MVP>

I've made a function that would calculate only business dates
between
current
data and a date field (Nxt Cpn Dt). I've used the function
dhCountWorkdaysA
in a query, which works well but if I put a number for the
criteria
i
have
the error "data type mismatch criteria expression. Here is
part
of
the
sql
WHERE (((REPO_TABLE_1.[Nxt Cpn Dt])>Now()) AND (([Nxt Cpn
Dt]-[Collateral
maturity date])<1) AND ((dhCountWorkdaysA([Nxt Cpn
Dt],Now())-1)=5));

Plase advise

 

[Guest]

Thanx again Ken for spending time on this. Next time you're in the Boston
area, I owe you at least a beverage!!!

Like I've mentioned in previous emails, I've changed both the 2nd and 3rd
arguments from 'Variant' to 'Date' and back again, using all possible
combinations between both of them.

Such as -
1)Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date
2)Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
3)Optional dtmDate As Variant = 0, _
Optional adtmDates As Date) As Date
4)Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
5)Optional dtmDate As Variant = 0, _
Optional adtmDates As Date) As Variant
6)Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Variant
etc...

However, I left 'dtmDate' as Variant per your last suggestion.
And again, this code works when I do not account for Arrays (holidays).

The code below is exactly what I have in my db. It's from the link that you
sent me yesterday (mvps.org.../date0012.htm)
Many thanx for your time, marc

Module -

' ********* 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 Variant = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the specified date.
'
' 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.
'
' 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.

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

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

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

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

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

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

' 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


Query -

SELECT tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#)) AS TicketNewDueDate
FROM tbl_CutoffDate_Nov11
GROUP BY tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
tbl_CutoffDate_Nov11.CutoffDate, tbl_CutoffDate_Nov11.TicketDueDate
ORDER BY dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#));

Thanx again!!!

Post the entire amount of VBA code that you're using, including all the
functions.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken -

Thanx again for the quick reply.

I tried changing the 2nd argument to Variant and that did not help either.
Still getting the same error message.

I look at the code and it's not that difficult to follow, and it works
when
I do not include any Arrays (holidays). It just has to be something
simple
that I'm overlooking.

Might you have any other suggestions?
marc

Be sure that the second argument in the dhNextWorkdayA function also is
declared as a Variant data type.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken -

Thanx for the quick reply and I totally feel like a knucklehead here,
but
I'm still coming up with the same error "Data type Mismatch..." even
after
changing the third argument of the function (adtmDates) to a Variant
data
type. I've even played around with that function and changed it as
follows,
with no luck.

Optional adtmDates As Variant) As Date
Optional adtmDates As Variant) As Variant
Optional adtmDates As Date) As Date
Optional adtmDates As Date) As Variant

And I've added from other functions-
Optional adtmDates As Variant = Empty) As Date
and still the same error message.

Again, I'll include the code that is currently in my db and giving me
the
error (it's below) and any other suggestions that you have, I'd really
appreciate them. And if you need any other information from me, please
let
me know.


Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

Many thanx for your time and patience here,
marc

:

Take a look at the original function that is posted on The ACCESS Web:
http://www.mvps.org/access/datetime/date0012.htm

Note that the original function declares the third argument of the
function
as a Variant data type. You have declared it as a Date type. Needs to
be
a
Variant type.
--

Ken Snell
<MS ACCESS MVP>

Hi Doug, Ken -

I'm having something like the same issue as Alex. When I run an
Access2000
query with a "dhAddWorkDaysA" function, I get the same error message
"data
type mismatch in criteria expression". The code is posted below,
but I
only
run into that error message when I add an Array for a holiday, it
runs
fine
when I run something like "dhAddWorkDaysA(2,[Cal_Date])". I believe
it's
something that I probably haven't declared, but I've changed a few
things
(Variant and Date declarations) in the Module and still have had no
luck.
Any help would be hugely appreciated! tia,

 

[Ken Snell \(MVP\)]

Is it possible for the field [Cal_Date] to contain a Null value? If yes,
then this part of the code in the first function will fail:

dtmTemp = dtmDate

because dmTemp is dim'd as a Date, which cannot hold a Null value.

However, based on your initial post that says the code fails only when you
use an Array for the third argument, that likely isn't the problem.

In a VBA procedure, I've tried the code from the website in a 2003 database
(which should be same as 2000 for this purpose) and it works fine with no
array, with a single value (not as an array), with an array consisting of
one value, and with an array consisting of two values. Same result when I
used the code from your post.

However, when I used the function in a query, I get the mismatch error. That
suggests to me that the query expression service doesn't support the Array
function. Did a quick Google search, and my suspicion was confirmed. See
this post from 2002 (watch for line-wrapping -- it's a single URL):
http://groups.google.com/group/micr...eries/tree/browse_frm/thread/dccf412ca38934a8

To do what you seek, you'll need to create a user-defined function that your
query can call and that returns the value from the original function (the
above thread shows how to do that).
--

Ken Snell
<MS ACCESS MVP>

Thanx again Ken for spending time on this. Next time you're in the Boston
area, I owe you at least a beverage!!!

Like I've mentioned in previous emails, I've changed both the 2nd and 3rd
arguments from 'Variant' to 'Date' and back again, using all possible
combinations between both of them.

Such as -
1)Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date
2)Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
3)Optional dtmDate As Variant = 0, _
Optional adtmDates As Date) As Date
4)Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
5)Optional dtmDate As Variant = 0, _
Optional adtmDates As Date) As Variant
6)Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Variant
etc...

However, I left 'dtmDate' as Variant per your last suggestion.
And again, this code works when I do not account for Arrays (holidays).

The code below is exactly what I have in my db. It's from the link that
you
sent me yesterday (mvps.org.../date0012.htm)
Many thanx for your time, marc

Module -

' ********* 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 Variant = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the specified date.
'
' 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.
'
' 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.

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

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

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

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

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

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

' 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


Query -

SELECT tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#)) AS TicketNewDueDate
FROM tbl_CutoffDate_Nov11
GROUP BY tbl_CutoffDate_Nov11.Station_ID, tbl_CutoffDate_Nov11.Cal_Date,
tbl_CutoffDate_Nov11.CutoffDate, tbl_CutoffDate_Nov11.TicketDueDate
ORDER BY dhAddWorkDaysA(2,[Cal_Date],Array(#9/4/2006#));

Thanx again!!!

< snipped >

 

[Guest]

Hi Ken -

1) There are no null values in my date fields. That's one of the things
that I checked early, but never passed it on to you.
2) Thanx for sending me that link. It looks like someone else has had the
same issue.

Even tho I get the concept of a 'wrapper', a couple of final questions,
where does this 'wrapper' go? Does it literally go around the main function
or does it go somewhere in the middle? I couldn't find any good examples
online, so maybe you could provide one? You're probably able to tell that my
VB isn't the best. And, do I still keep the arguments the same? Such as,
'adtmDates' as Variants and 'dtmDate' as Date when using this wrapper?

Here's what I think the wrapper function should look like with the main
function beneath it, but when I pasted them in and ran the same query, I
still came up with the same error "data type mismatch".


Wrapper:
Public Function MyCountWorkDays(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date

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

Main:
Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

MANY THANX again for your time and patience!!!!

Is it possible for the field [Cal_Date] to contain a Null value? If yes,
then this part of the code in the first function will fail:

dtmTemp = dtmDate

because dmTemp is dim'd as a Date, which cannot hold a Null value.

However, based on your initial post that says the code fails only when you
use an Array for the third argument, that likely isn't the problem.

In a VBA procedure, I've tried the code from the website in a 2003 database
(which should be same as 2000 for this purpose) and it works fine with no
array, with a single value (not as an array), with an array consisting of
one value, and with an array consisting of two values. Same result when I
used the code from your post.

However, when I used the function in a query, I get the mismatch error. That
suggests to me that the query expression service doesn't support the Array
function. Did a quick Google search, and my suspicion was confirmed. See
this post from 2002 (watch for line-wrapping -- it's a single URL):
http://groups.google.com/group/micr...eries/tree/browse_frm/thread/dccf412ca38934a8

To do what you seek, you'll need to create a user-defined function that your
query can call and that returns the value from the original function (the
above thread shows how to do that).
--

Ken Snell
<MS ACCESS MVP>

Thanx again Ken for spending time on this. Next time you're in the Boston
area, I owe you at least a beverage!!!

Like I've mentioned in previous emails, I've changed both the 2nd and 3rd
arguments from 'Variant' to 'Date' and back again, using all possible
combinations between both of them.

Such as -
1)Optional dtmDate As Variant = 0, _
Optional adtmDates As Variant) As Date
2)Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
3)Optional dtmDate As Variant = 0, _
Optional adtmDates As Date) As Date
4)Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date
5)Optional dtmDate As Variant = 0, _
Optional adtmDates As Date) As Variant
6)Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Variant
etc...

However, I left 'dtmDate' as Variant per your last suggestion.
And again, this code works when I do not account for Arrays (holidays).

The code below is exactly what I have in my db. It's from the link that
you
sent me yesterday (mvps.org.../date0012.htm)
Many thanx for your time, marc

Module -

' ********* 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 Variant = 0, _
Optional adtmDates As Variant) As Date
' Add the specified number of work days to the specified date.
'
' 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.
'
' 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.

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

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

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

' 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

 

[Ken Snell \(MVP\)]

Your wrapper function looks fine. However, you will not be able to pass an
Array to the wrapper function either. You'll need to either put the actual
Array values in the wrapper function (hard-code them there), or pass some
type of concatenated string to the wrapper function that the wrapper
function can parse and turn into an Array to pass to the original function.

Does your setup allow the use of hard-coded values for the array? Or do you
need to be able to pass different values in the Array for each query's run?
If the latter is correct, how would the query know what those values are
that are to be used? We can assist in writing a more complex wrapper
function that can parse values into an Array, but need to know how the query
would get the values so that we can identify the best way to accomplish
this.

Hi Ken -

1) There are no null values in my date fields. That's one of the things
that I checked early, but never passed it on to you.
2) Thanx for sending me that link. It looks like someone else has had the
same issue.

Even tho I get the concept of a 'wrapper', a couple of final questions,
where does this 'wrapper' go? Does it literally go around the main
function
or does it go somewhere in the middle? I couldn't find any good examples
online, so maybe you could provide one? You're probably able to tell that
my
VB isn't the best. And, do I still keep the arguments the same? Such as,
'adtmDates' as Variants and 'dtmDate' as Date when using this wrapper?

Here's what I think the wrapper function should look like with the main
function beneath it, but when I pasted them in and ran the same query, I
still came up with the same error "data type mismatch".


Wrapper:
Public Function MyCountWorkDays(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date

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

Main:
Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

MANY THANX again for your time and patience!!!!

Is it possible for the field [Cal_Date] to contain a Null value? If yes,
then this part of the code in the first function will fail:

dtmTemp = dtmDate

because dmTemp is dim'd as a Date, which cannot hold a Null value.

However, based on your initial post that says the code fails only when
you
use an Array for the third argument, that likely isn't the problem.

In a VBA procedure, I've tried the code from the website in a 2003
database
(which should be same as 2000 for this purpose) and it works fine with no
array, with a single value (not as an array), with an array consisting of
one value, and with an array consisting of two values. Same result when I
used the code from your post.

However, when I used the function in a query, I get the mismatch error.
That
suggests to me that the query expression service doesn't support the
Array
function. Did a quick Google search, and my suspicion was confirmed. See
this post from 2002 (watch for line-wrapping -- it's a single URL):
http://groups.google.com/group/micr...eries/tree/browse_frm/thread/dccf412ca38934a8

To do what you seek, you'll need to create a user-defined function that
your
query can call and that returns the value from the original function (the
above thread shows how to do that).

 

[Guest]

Hi -

Ideally, what I'd like to do is have a separate table of holidays that the
array could draw from. I have a table created, but haven't been able to go
any further into this as I'm at my current roadblock. From there, I’d be
able to run the query (like I’ve done without the array value and not get the
error message) and get the correct dates.

The values for the holidays won't change. They will always be the same
values for the query that I need to run. There are only about 35-40 dates,
as I only need to go out to 2008, then this db will be no longer needed.

So, would it be easiest to ‘hardcode’ them into the array or introduce my
table of dates? If it’s faster to hardcode them in, could you please provide
an example? I think I know how it should be, but with my luck, I’ll miss a
comma or something like that.

And again, thanx for providing a whole bunch of assistance for me!!!
marc

Your wrapper function looks fine. However, you will not be able to pass an
Array to the wrapper function either. You'll need to either put the actual
Array values in the wrapper function (hard-code them there), or pass some
type of concatenated string to the wrapper function that the wrapper
function can parse and turn into an Array to pass to the original function.

Does your setup allow the use of hard-coded values for the array? Or do you
need to be able to pass different values in the Array for each query's run?
If the latter is correct, how would the query know what those values are
that are to be used? We can assist in writing a more complex wrapper
function that can parse values into an Array, but need to know how the query
would get the values so that we can identify the best way to accomplish
this.

Hi Ken -

1) There are no null values in my date fields. That's one of the things
that I checked early, but never passed it on to you.
2) Thanx for sending me that link. It looks like someone else has had the
same issue.

Even tho I get the concept of a 'wrapper', a couple of final questions,
where does this 'wrapper' go? Does it literally go around the main
function
or does it go somewhere in the middle? I couldn't find any good examples
online, so maybe you could provide one? You're probably able to tell that
my
VB isn't the best. And, do I still keep the arguments the same? Such as,
'adtmDates' as Variants and 'dtmDate' as Date when using this wrapper?

Here's what I think the wrapper function should look like with the main
function beneath it, but when I pasted them in and ran the same query, I
still came up with the same error "data type mismatch".


Wrapper:
Public Function MyCountWorkDays(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date

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

Main:
Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

MANY THANX again for your time and patience!!!!

Is it possible for the field [Cal_Date] to contain a Null value? If yes,
then this part of the code in the first function will fail:

dtmTemp = dtmDate

because dmTemp is dim'd as a Date, which cannot hold a Null value.

However, based on your initial post that says the code fails only when
you
use an Array for the third argument, that likely isn't the problem.

In a VBA procedure, I've tried the code from the website in a 2003
database
(which should be same as 2000 for this purpose) and it works fine with no
array, with a single value (not as an array), with an array consisting of
one value, and with an array consisting of two values. Same result when I
used the code from your post.

However, when I used the function in a query, I get the mismatch error.
That
suggests to me that the query expression service doesn't support the
Array
function. Did a quick Google search, and my suspicion was confirmed. See
this post from 2002 (watch for line-wrapping -- it's a single URL):
http://groups.google.com/group/micr...eries/tree/browse_frm/thread/dccf412ca38934a8

To do what you seek, you'll need to create a user-defined function that
your
query can call and that returns the value from the original function (the
above thread shows how to do that).

 

[Ken Snell \(MVP\)]

The function that you're using will properly apply the holidays that are
within the "date range" identified by the number of workdays to add and the
starting date, so using a table to store the holidays will be acceptable for
your needs.

What we'd do is use the wrapper function to go get the list of holidays and
then send them to the original function as an array.

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


--

Ken Snell
<MS ACCESS MVP>

Hi -

Ideally, what I'd like to do is have a separate table of holidays that the
array could draw from. I have a table created, but haven't been able to
go
any further into this as I'm at my current roadblock. From there, I'd be
able to run the query (like I've done without the array value and not get
the
error message) and get the correct dates.

The values for the holidays won't change. They will always be the same
values for the query that I need to run. There are only about 35-40
dates,
as I only need to go out to 2008, then this db will be no longer needed.

So, would it be easiest to 'hardcode' them into the array or introduce my
table of dates? If it's faster to hardcode them in, could you please
provide
an example? I think I know how it should be, but with my luck, I'll miss
a
comma or something like that.

And again, thanx for providing a whole bunch of assistance for me!!!
marc

Your wrapper function looks fine. However, you will not be able to pass
an
Array to the wrapper function either. You'll need to either put the
actual
Array values in the wrapper function (hard-code them there), or pass some
type of concatenated string to the wrapper function that the wrapper
function can parse and turn into an Array to pass to the original
function.

Does your setup allow the use of hard-coded values for the array? Or do
you
need to be able to pass different values in the Array for each query's
run?
If the latter is correct, how would the query know what those values are
that are to be used? We can assist in writing a more complex wrapper
function that can parse values into an Array, but need to know how the
query
would get the values so that we can identify the best way to accomplish
this.

Hi Ken -

1) There are no null values in my date fields. That's one of the
things
that I checked early, but never passed it on to you.
2) Thanx for sending me that link. It looks like someone else has had
the
same issue.

Even tho I get the concept of a 'wrapper', a couple of final questions,
where does this 'wrapper' go? Does it literally go around the main
function
or does it go somewhere in the middle? I couldn't find any good
examples
online, so maybe you could provide one? You're probably able to tell
that
my
VB isn't the best. And, do I still keep the arguments the same? Such
as,
'adtmDates' as Variants and 'dtmDate' as Date when using this wrapper?

Here's what I think the wrapper function should look like with the main
function beneath it, but when I pasted them in and ran the same query,
I
still came up with the same error "data type mismatch".


Wrapper:
Public Function MyCountWorkDays(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date

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

Main:
Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

MANY THANX again for your time and patience!!!!


:

Is it possible for the field [Cal_Date] to contain a Null value? If
yes,
then this part of the code in the first function will fail:

dtmTemp = dtmDate

because dmTemp is dim'd as a Date, which cannot hold a Null value.

However, based on your initial post that says the code fails only when
you
use an Array for the third argument, that likely isn't the problem.

In a VBA procedure, I've tried the code from the website in a 2003
database
(which should be same as 2000 for this purpose) and it works fine with
no
array, with a single value (not as an array), with an array consisting
of
one value, and with an array consisting of two values. Same result
when I
used the code from your post.

However, when I used the function in a query, I get the mismatch
error.
That
suggests to me that the query expression service doesn't support the
Array
function. Did a quick Google search, and my suspicion was confirmed.
See
this post from 2002 (watch for line-wrapping -- it's a single URL):
http://groups.google.com/group/micr...eries/tree/browse_frm/thread/dccf412ca38934a8

To do what you seek, you'll need to create a user-defined function
that
your
query can call and that returns the value from the original function
(the
above thread shows how to do that).

 

[Guest]

Ken -

THANX SO MUCH!!!
I've used the code that you've provided me and I'm finally getting the
correct values (dates).

Thanx again for all of your time and effort,
marc

The function that you're using will properly apply the holidays that are
within the "date range" identified by the number of workdays to add and the
starting date, so using a table to store the holidays will be acceptable for
your needs.

What we'd do is use the wrapper function to go get the list of holidays and
then send them to the original function as an array.

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


--

Ken Snell
<MS ACCESS MVP>

Hi -

Ideally, what I'd like to do is have a separate table of holidays that the
array could draw from. I have a table created, but haven't been able to
go
any further into this as I'm at my current roadblock. From there, I'd be
able to run the query (like I've done without the array value and not get
the
error message) and get the correct dates.

The values for the holidays won't change. They will always be the same
values for the query that I need to run. There are only about 35-40
dates,
as I only need to go out to 2008, then this db will be no longer needed.

So, would it be easiest to 'hardcode' them into the array or introduce my
table of dates? If it's faster to hardcode them in, could you please
provide
an example? I think I know how it should be, but with my luck, I'll miss
a
comma or something like that.

And again, thanx for providing a whole bunch of assistance for me!!!
marc

Your wrapper function looks fine. However, you will not be able to pass
an
Array to the wrapper function either. You'll need to either put the
actual
Array values in the wrapper function (hard-code them there), or pass some
type of concatenated string to the wrapper function that the wrapper
function can parse and turn into an Array to pass to the original
function.

Does your setup allow the use of hard-coded values for the array? Or do
you
need to be able to pass different values in the Array for each query's
run?
If the latter is correct, how would the query know what those values are
that are to be used? We can assist in writing a more complex wrapper
function that can parse values into an Array, but need to know how the
query
would get the values so that we can identify the best way to accomplish
this.


Hi Ken -

1) There are no null values in my date fields. That's one of the
things
that I checked early, but never passed it on to you.
2) Thanx for sending me that link. It looks like someone else has had
the
same issue.

Even tho I get the concept of a 'wrapper', a couple of final questions,
where does this 'wrapper' go? Does it literally go around the main
function
or does it go somewhere in the middle? I couldn't find any good
examples
online, so maybe you could provide one? You're probably able to tell
that
my
VB isn't the best. And, do I still keep the arguments the same? Such
as,
'adtmDates' as Variants and 'dtmDate' as Date when using this wrapper?

Here's what I think the wrapper function should look like with the main
function beneath it, but when I pasted them in and ran the same query,
I
still came up with the same error "data type mismatch".


Wrapper:
Public Function MyCountWorkDays(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As Date

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

Main:
Public Function dhAddWorkDaysA(lngDays As Long, _
Optional dtmDate As Date = 0, _
Optional adtmDates As Variant) As 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

MANY THANX again for your time and patience!!!!


:

Is it possible for the field [Cal_Date] to contain a Null value? If
yes,
then this part of the code in the first function will fail:

dtmTemp = dtmDate

because dmTemp is dim'd as a Date, which cannot hold a Null value.

However, based on your initial post that says the code fails only when
you
use an Array for the third argument, that likely isn't the problem.

In a VBA procedure, I've tried the code from the website in a 2003
database
(which should be same as 2000 for this purpose) and it works fine with
no
array, with a single value (not as an array), with an array consisting
of
one value, and with an array consisting of two values. Same result
when I
used the code from your post.

However, when I used the function in a query, I get the mismatch
error.
That
suggests to me that the query expression service doesn't support the
Array
function. Did a quick Google search, and my suspicion was confirmed.
See
this post from 2002 (watch for line-wrapping -- it's a single URL):
http://groups.google.com/group/micr...eries/tree/browse_frm/thread/dccf412ca38934a8

To do what you seek, you'll need to create a user-defined function
that
your
query can call and that returns the value from the original function
(the
above thread shows how to do that).

 

[Ken Snell \(MVP\)]

You're welcome.