Date problem with function

[Krzysztof via AccessMonster.com]

Good Morning,

I am in a jam, and i cannot find out why this is not working correctly.

I have a set of functions, that untimately return a status. one function
finds the number of business days between two dates, the other takes that
number of bus days, and based on another set of values returns the status. I
am using several date fields for this operation, and i am getting one wierd
result.

Here is my code:

Public Function NbrWeekDays(ByVal InitialDate As Date, ByVal FinalDate As
Date) As Integer
On Error GoTo err_NWD

Dim totalDays, workingdays, i As Integer
Dim dateTemp As Date

totalDays = DateDiff("d", InitialDate, FinalDate)
workingdays = 0

{{{ Here is where my problem is }}}
{{{ dateTemp = InitialDate }}}

For i = 1 To totalDays
dateTemp = initaldate + i

Select Case WeekdayName(Weekday(dateTemp))

Case "Saturday"

Case "Sunday"
workingdays = workingdays + 0

Case Else:
workingdays = workingdays + 1

End Select

Next i

NbrWeekDays = workingdays

exit_NWD:
Exit Function

err_NWD:
ErrorMod.ErrHandler
Resume exit_NWD

End Function

Public Function MarkAsLate(ByVal hours As Integer, ByVal initDate As Date, _
ByVal fDate1 As Date, ByVal fDate2 As Date, ByVal ctc As Integer) As
String
On Error GoTo err_MAL

Dim bDays As Integer
Dim x As String

'first determine the number of business days between the two dates
If IsNull(hours) = True Then
bDays = Calcs.NbrWeekDays(initDate, fDate1)
Else
If IsNull(fDate2) = True Then
bDays = Calcs.NbrWeekDays(initDate, fDate1)
Else
bDays = Calcs.NbrWeekDays(initDate, fDate2)
End If
End If


'based on ctc, determine if the wo is late

Select Case ctc

Case 1, 4, 17, 34, 38, 40, 21 To 31
If bDays > 3 Then
x = "LATE"
Else
x = ""
End If

Case 9, 32, 33, 35, 36, 37
If bDays > 14 Then
x = "LATE"
Else
x = ""
End If

Case 3
If bDays > 1 Then
x = "LATE"
Else
x = ""
End If

Case 11, 18, 42
If bDays > 7 Then
x = "LATE"
Else
x = ""
End If

Case Else:
x = ""

End Select

MarkAsLate = x

exit_MAL:
Exit Function

err_MAL:
ErrorMod.ErrHandler
Resume exit_MAL

End Function

when i set the variable dateTemp=initialDate, in my watchws window, dateTemp
always says 1/1/1900 !?

it goes through everything ok, but it uses the dateTemp variable to tell me
what day fo the week it is, thus throwing off my business days, since it is
calculating based on the year 1900.

Is this a formatting error? Any ideas?

 

[Krzysztof via AccessMonster.com]

Upon further examination, the error resides with the staement inside the FOR
loop, not outside.

the statement dateTemp = Initialdate is fine, it is when the statment
dateTemp = initialDate + i, that is screws up the date.

 

[John Spencer]

Your variable is InitialDate not initaldate

dateTemp = initaldate + i should be
dateTemp = InitialDate + i


If this compiles ok then you are missing the Option Explicit at the beginning
of your code module.

You should ALWAYS have OPTION Explicit at the beginning of all your code
modules. It helps avoid errors like the above.

To make sure it is set for all NEW modules, select Tools: Options from the
menu when you are in VBA and Check Require Variable Declaration.

Also this line
Dim totalDays, workingdays, i As Integer
should read
Dim totalDays as Integer, workingdays as Integer, i As Integer

Your declaration typed totalDays and workingdays as variants and not integers.

Hope this helps

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Krzysztof via AccessMonster.com]

I have just found that i had spelled initial wrong, thus adding to my
frustration.
Now that i have fixed it, the function works great.

as far as the optoin explicit, i only submitted part of the whole module.

although i thought that you could group the variable together like in .net?
that would explain the "Variant/Date" under type in the watch window. Thanks
for that.

i think the .net is affecting programmers like spellcheck did for word. we
forget how to spell

Your variable is InitialDate not initaldate

dateTemp = initaldate + i should be
dateTemp = InitialDate + i

If this compiles ok then you are missing the Option Explicit at the beginning
of your code module.

You should ALWAYS have OPTION Explicit at the beginning of all your code
modules. It helps avoid errors like the above.

To make sure it is set for all NEW modules, select Tools: Options from the
menu when you are in VBA and Check Require Variable Declaration.

Also this line
Dim totalDays, workingdays, i As Integer
should read
Dim totalDays as Integer, workingdays as Integer, i As Integer

Your declaration typed totalDays and workingdays as variants and not integers.

Hope this helps

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Good Morning,

[quoted text clipped - 23 lines]

For i = 1 To totalDays
dateTemp = initaldate + i

==== SNIP ====

when i set the variable dateTemp=initialDate, in my watchws window, dateTemp
always says 1/1/1900 !?

[quoted text clipped - 4 lines]

Is this a formatting error? Any ideas?