Using date function result as criteria in a query?

[sirkenj]

I'm having some problems with inputing criteria on a date calculation function. I have a query with the expression:
DaysOut: DeltaDays(Now(),[SuspenseDate])

This displays the number of days between today and [SuspenseDate] excluding weekends, and holidays. The function works great, but I get a data type mismatch if I try entering <=3 as the criteria for this column.

Any help you can offer will be greatly appreciated.

The DeltaDays function (from the FAQ) is included below, as I'm suspecting the data type can be modified in this.

*********************************************
Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As Database
Dim rstHolidays As Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset(&quot;tblHolidays&quot;, dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, &quot;Short Date&quot

For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (WeekDay(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = &quot;[HoliDate] = &quot; & NumSgn & MyDate & NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd(&quot;d&quot;, 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function
***************************************************8
Thanks!!!

 

[jdegeorge]

Are you sure that [SuspenseDate] is a date field? If so, there shouldn't be any reason for this not to work. I use a very similar code as the one you're using here and I've never had a problem.


Jim DeGeorge

 

[sirkenj]

The code works great, I can't use the results of the code (which are calculated in a query) to filter the records though. If I put a number in as criteria, I get a &quot;data type mismatch in criteria expression&quot;

To my understanding (correct me if I'm wrong), the first line of the module is placing the output of the proceedure in the format of an integer. I was under the impression this could be used to place criteria under????

 

[jdegeorge]

sirkenj

If you're using the function described above in a query, you would type this is the query grid: DeltaDays(Now(),[SuspenseDate]).

The assumption is that [SuspenseDate] is a date field in the table or query upon which the query is based.

Is this what you're doing? If not, past your query's SQL in a response.

Jim DeGeorge

 

[sirkenj]

That's exactly what I'm doing. The Field reads:
DaysOuteltaDays(Now(),[SuspenseDate])

That part of it is working fine though. I put that in the query grid, and in datasheet view, the difference in days is calculated correctly. The problem arises when I try to place <=3 in the Criteria. This is when I get a data type mismatch. This is the only criteria in the query, and the query is based on only one table...no relationships, no other queries.

I can't figure out why it's doing this. Without any criteria in the query it runs fine, it's the <=3 (or even just 3 for that matter) which causes the data type mismatch.

 

[jdegeorge]

I get it. It runs without any criteria.

Try using Date() instead of Now(). Now() gives you 09/11/2003 12:20:00. Date() just gives you 09/11/2003. Could be the time aspect of the Now() format that's throwing this off.

Jim DeGeorge

 

[sirkenj]

No, I thought of that earlier. The calculation is working, it's the criteria that's throwing it.

My SQL:

SELECT Taskings.*,DeltaDays(Date(),[SuspenseDate]) AS DaysOut
FROM Taskings
WHERE (((DeltaDays(Date(),[SuspenseDate]))<=3));

 

[jdegeorge]

Why so many parenthesis? This should be all you need:

DeltaDays(Date(),[SuspenseDate])<=3

Jim DeGeorge

 

[sirkenj]

I designed this in the query grid inputting it exactly as you typed it above. When I change to SQL view, it automatically puts the other parenthesis in. If I take them away, I get the &quot;Missing ),], or Item in query expression...&quot;

 

[jdegeorge]

Okay. I use some similar code that I got a few years ago. It's very simiar to what you're using.

Open a new module and insert these 2 functions:

Function ElapsedBDays(ByVal startdate As Variant, ByVal EndDate As Variant) As Integer

' Pass parameters ByVal since we modify them, possibly, during the execution of this code.
' This function just counts up the number of weekdays, and then subtracts off the number of
' holidays (from the Holidays table) that fall within the passed in timeframe.
' -- Ken Getz (76137, 3650)

Dim Days As Integer
Dim WeekDays As Integer
Dim Holidays As Integer
Dim Temp As Variant

' Make sure both parameters can be regarded as dates.
' If not, return NULL.
If Not IsDate(startdate) Or Not IsDate(EndDate) Then
ElapsedBDays = 0
Exit Function
End If

' If the dates were passed in in string format, convert them to dates now.
If VarType(startdate) = V_STRING Then startdate = DateValue(startdate)

If VarType(EndDate) = V_STRING Then EndDate = DateValue(EndDate)

If EndDate < startdate Then
' Swap them!
Temp = EndDate
EndDate = startdate
startdate = Temp
End If

' Get the starting day count (figuring that if the two days passed in
' are the same, we'll return 1).
'
' If you think that entering two dates both of which are the same should return
' a 0, then remove the &quot;+1&quot; below.
Days = DateDiff(&quot;d&quot;, startdate, EndDate) + 1

' Get starting date to be a week day.
While (DatePart(&quot;w&quot;, startdate) - 1) Mod 6 = 0
startdate = DateAdd(&quot;d&quot;, startdate, 1)
Days = Days - 1
Wend

' Get ending date to be a week day.
While (DatePart(&quot;w&quot;, EndDate) - 1) Mod 6 = 0
EndDate = DateAdd(&quot;d&quot;, EndDate, -1)
Days = Days - 1
Wend

' Subtract off weekend days. We do this by figuring out which ordinal week of the year
' each date is in, and multiplying the difference by two (since 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.
WeekDays = Days - Abs(DateDiff(&quot;ww&quot;, EndDate, startdate) * 2)

' Look through the data table for dates that fall in our allotted range.
Holidays = DCount(&quot;HoliDate&quot;, &quot;tblHolidays&quot;, &quot;(Date Between &quot; & USAFormat(startdate) & &quot; And &quot; & USAFormat(EndDate) & &quot AND (DatePart('w', [Date])-1) Mod 6 <> 0&quot

' The answer to our quest is all the weekdays, minus any holidays we found in the table.
ElapsedBDays = WeekDays - Holidays - 1

End Function

Function USAFormat(varDate As Variant) As Variant

USAFormat = &quot;#&quot; & DatePart(&quot;m&quot;, varDate) & &quot;/&quot; & DatePart(&quot;d&quot;, varDate) & &quot;/&quot; & DatePart(&quot;yyyy&quot;, varDate) & &quot;#&quot;

End Function


Then, call the function ElapsedBDays([SuspenseDate],Date()) and see what happens.

Jim DeGeorge

 

[sirkenj]

I compile that code fine, then get a runtime error on:

Holidays = DCount(&quot;HoliDate&quot;, &quot;tblHolidays&quot;, &quot;(Date Between &quot; & USAFormat(startdate) & &quot; And &quot; & USAFormat(EndDate) & &quot AND (DatePart('w', [Date])-1) Mod 6 <> 0&quot

The code I'm using is doing exactly what it should...I am only getting an error when adding the criteria....

The SuspenseDate field is a date field, I suspect the code would not work if it were not.

Thanks for your help though.

 

[jdegeorge]

Is your holiday table named &quot;tblHolidays&quot; and the holiday date field named &quot;HoliDate&quot;? Also, is HoliDate a date field?

Sorry about suggesting my code over yours. It's just that I know that mine has worked for years and I couldn't figure yours out.

The only time you get a datatype mismatch error is when you compare numbers to text. Is there ANY way that something in your Holiday table is text? If so, then that's a problem. You might want to try <= &quot;3&quot; if that's the case. I'm grabbing at straws here, however. SOrry.

Jim DeGeorge