FAQ 181-261

[ChrisBurch]

I am attempting to use MichaelRed's DeltaDays() function, as set out in Tek-Tips FAQ 181-261, in a MS Access 97 DB. However, it is ignoring the holidays that I have set up in tblHolidays, and just gives me weekdays as a result.

I have stepped througth the code with watches, and everything appears to be working as it should, but the strCriteria is not getting any matches in .FindFirst. I have the HoliDate field set to 'Short Date' format.

Can anyone suggest possible causes, or areas I should check?

 

[dhouchin]

Hi Chris,

If you're using a date value in your criteria, use '#' delimiters. ex: #8/11/2001# If you use quote delimiters it will use a string comparison and your data will have to be in precisely the same format as your string critera.

Dennis

 

[ChrisBurch]

Thanks Dennis,

The # delimeters are present in the search string, and the table dates are in short date format. so I should get a match with .findfirst, but I don't....strange!

 

[dhouchin]

Have verified that your 'holidays' recordset is populated?

Just before your .findfirst statement, add:

recordsetname.movelast
debug.print recordsetname.recordcount

If you have records in the recordset, verify that the fieldname is what you specify in you criteria string.

If both of those check out ok, post some of your code including the actual value of the criteria string.

 

[ChrisBurch]

The additional code produced a count of 8, which is the correct number of records in tblHolidays.

When looping through the code, an example of 'strCriteria' is "[Holidate] = #7/08/2001#" (Australian short date format)

I pass two 'short dates' to deltaDays(). It seems to me that I have a problem with the format of holiDate in tblHolidays, but I just can't see what.

My full setup is:-

tblHolidays

holiDate holiName
25/12/2001 Christmas Day
26/12/2001 Boxing Day
1/01/2002 New Year's Day
6/11/2001 Melbourne Cup Day
2/07/2001 Test1
3/07/2001 Test2
7/08/2001 test4
6/08/2001 test3

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("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, "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 = "[Holidate] = " & 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("d", 1, MyDate)

Next Idx

DeltaDays = NumDays

End Function

 

[dhouchin]

Hi Chris,

I was able to get the code that you posted to work.
I even configured my system to use dd/mm/yyyy.

What dates are you using dates are you using for StartDate and EndDate?
Dennis

 

[ChrisBurch]

ITSABUG!! I set all weekdays in August and September to be Holidays. The function gives me 7 working days in August (month 8), and 8 working days in September (month 9).

I then changed my International settings to MM/dd/yyyy, and deltaDays() worked correctly.

I'm running Win 98 SE with Office Proffessional SR-2. Has any heard of a date bug, or patch, in this conbination?

Or can anyone suggest a workaround (I have to use dd/MM/yyy format for Short Date)

 

[ChrisBurch]

Workaround found!!!

From Micosoft article Q14095..."When you use Visual Basic for Applications to concatenate dates into a SQL string, you must use a standard US date format, regardless of the Regional Settings in Control Panel."

Instead of using strCriteria = "[holiDate] = " & NumSgn & MyDate & NumSgn

I used strCriteria = "[holiDate] = " & NumSgn & Month(MyDate) & DivSgn & Day(MyDate) & DivSgn & Year(MyDate) & NumSgn '(DivSgn = Chr(47)= /). This pushes the criteria through with US Short Date format (MM/dd/yyyy), and gets matches even though it is comparing against table entries in Australian Short Date format (dd/MM/yyyy).

Strange but true!!!!!!!!!