Workday Function Using a User Created Table; Limiting the records in the Query

[Jim Krois]

Hello,
I am pretty new to writing VBA but I have created a
user defined function that, after you enter two dates,
goes to a Workday Table that has the specific Workdays
for those two dates. I then subtract the two workdays
and arrive at the total workdays between the two dates.
I know there is a function in Access for this but I
created my own workday table so I could define certain
dates during the normal work week as non workdays
(Memorial day, Christmas, etc.). The function works well
in my query. The problem I have is that when I try to
limit the query to a certain set of workdays, >=25
let's say, that the function returns a meassage that
says "Data type mismatch in criteria expression". I am
trying to limit it (>=25)in the Criteria section of my
query.

Here is my VBA code. Some of the code below gives me
return values like 111 if the start date is greater than
the end date or 999 if the date is not in my table.

Thanks for your help.

Option Compare Database
Option Explicit


Public Function GetWD(StartDate As Long, EndDate As Long)
As Long
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim strsql1 As String
Dim strsql2 As String


If StartDate > EndDate Then
GetWD = 111
Else
If StartDate > #12/31/2004# Then
GetWD = 999
Else
If StartDate < #1/1/2001# Then
GetWD = 999
Else
If EndDate > #12/31/2004# Then
GetWD = 999
Else
If EndDate < #1/1/2001# Then
GetWD = 999
Else
strsql1 = "SELECT * FROM tblworkday WHERE
tblworkday.dates = "
strsql2 = "SELECT * FROM tblworkday WHERE
tblworkday.dates = "
Set rst1 = CurrentDb.OpenRecordset(strsql1 & StartDate)
Set rst2 = CurrentDb.OpenRecordset(strsql2 & EndDate)
GetWD = rst2!wdno - rst1!wdno
GetWD_Exit:

End If
End If
End If
End If
End If

Exit Function
End Function

 

[chris]

Assuming the datatype of the field tblworkday.dates
is datetime, your criteria should be ...

Set rst1 = CurrentDb.OpenRecordset(strsql1 & "#" &
format$(StartDate, "mmm dd yyyy") & "#;", dbopensnapshot)