Data mismatch

[Guest]

Ok I'm new at writing functions so be patient with me...I created this
function but when I try to run a query on the data it brings up is gives me a
data mismatch...Any suggestions?

Public Function Work(EndDate As String, StartDate As String, DayType As
String)
Dim EndDate1, StartDate1 As Date

EndDate1 = CDate(EndDate)
StartDate1 = CDate(StartDate)

Work = 0

If DayType = "Calendar" Then
Work = Abs(DateDiff("d", EndDate1, StartDate1))
Else
If Weekday(StartDate1) = 2 Then
If Weekday(StartDate1) = Weekday(EndDate1) Then 'Monday
Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5
End If

If Weekday(EndDate1) = 3 Then 'Tuesday
Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5 + 1
End If

If Weekday(EndDate1) = 4 Then 'Wednesday
Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5 + 2
End If

If Weekday(EndDate1) = 5 Then 'Thursday
Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5 + 3
End If

If Weekday(EndDate1) = 6 Then 'Friday
Work = Abs(DateDiff("w", EndDate1, StartDate1)) * 5 + 4
End If
End If
etc etc

 

[Ken Snell \(MVP\)]

You must explicitly declare the type of each variable, and it's best if you
also do the same for the function:


Public Function Work(EndDate As String, StartDate As String, DayType As
String) As Long
Dim EndDate1 As Date, StartDate1 As Date

 

[Guest]

I did that but no luck...

You must explicitly declare the type of each variable, and it's best if you
also do the same for the function:


Public Function Work(EndDate As String, StartDate As String, DayType As
String) As Long
Dim EndDate1 As Date, StartDate1 As Date

 

[Guest]

You should be able to shorten the function considerably. The following is
adapted from one I put together some years ago:

Public Function Work( _
EndDate As Variant, _
StartDate As Variant, _
Optional strDayType As String = "Work") As Variant

Dim lngDaysDiff As Long, lngWeekendDays As Long
Dim intPubHols As Integer

If IsNull(EndDate) Or IsNull(StartDate) Then
Exit Function
End If

If strDayType <> "Work" Then
Work = DateDiff("d", StartDate, EndDate)
Else
' if first date is Sat or Sun start on following Monday
Select Case Weekday(StartDate, vbMonday)
Case vbSaturday
StartDate = StartDate + 2
Case vbSunday
StartDate = StartDate + 1
End Select

' if last date is Sat or Sun finish on following Monday
Select Case Weekday(EndDate, vbMonday)
Case vbSaturday
EndDate = EndDate + 2
Case vbSunday
EndDate = EndDate + 1
End Select

' get total date difference in days
lngDaysDiff = DateDiff("d", StartDate, EndDate)

' get date difference in weeks and multiply by 2
' to get number of weekend days
lngWeekendDays = DateDiff("ww", StartDate, EndDate, vbMonday) * 2

' subtract number of weekend days from total date difference
' to return number of working days
Work = lngDaysDiff - lngWeekendDays
End If

End Function

By way of explanation:

1. The function and the date parameters are declared as variants. This
would allow for the function to be called in a query for instance where some
rows might not have values in one or both of the date columns in the table.
This might explain your error as your function expects the dates as strings,
which can't be Null, though I'd have thought you'd get an 'invalid use of
Null' error in that case. With the above the function expects variants,
which can be Null, and would return Null if either or both dates are Null.

2. The DayType parameter is made optional and given a default value of
"Work". This means that if this is omitted the function would automatically
count only weekdays. If any other value is passed into the function it would
count all days. So:

Work(#05/03/2007#,#04/03/2007#) returns 22

whereas:

Work(#05/03/2007#,#04/03/2007#, "Calendar") returns 30

3. I hope that the comments in the code will be sufficient to explain how
it calculates the number of days. Basically, if counting work days, it first
makes sure that the count starts and ends with a weekday and gets the total
date difference; then it works out the number of weekends involved by
getting the difference in weeks, and multiplies by 2 to get the number of
weekend days, which are subtracted from the total date difference to give the
date difference in weekdays only.

4. It does not take account of public holidays. In fact the original
function on which the above is based has the option to do this or not, the
public holidays being stored in a table. It also has a Country parameter so
that it can use different public holidays for different countries. Here the
four constituent countries of the UK, along with the Republic of Ireland,
each have different public holidays, and as more countries are becoming
members of the European Union there can also be a more regular need to take
account of differences throughout Europe.

Ken Sheridan
Stafford, England

 

[Guest]

Thank you!! I have been obsessing over this for two days!