Viewing Totals/GroupBy creates Data Mismatch

[NewbieSupreme]

I have a WorkingDays function that returns number of working days between 2
dates. The code for it follows (as you can see, it uses values entered into
a Holidays table to exclude them as well as Saturdays and Sundays):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays", dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


I have a query that has two dae fields: DateReceived and DateShipped. I
show these values, and also an expression which is the Working Days between:

Turnaround: WorkingDays([DateReceived],[DateShipped])

When I run the query, everything works fine. However, if I choose
View-->Totals, with all columns set to GroupBy in the Totals row, I suddenly
get a Data Type Mismatch error. Can anyone explain why this would be?
Please remember that it works fine as long as he Totals row is not active.

Thanks for any help.

 

[Gary Walter]

Hi,

It sure sounds like this is not
"the complete picture."

You must have a really efficient
company if everything received
has been shipped (meaning no offense).

Add the following 2 columns to your
query grid (just to check):

Field: IsDate([DateReceived]) IsDate([DateShipped])
Table:
Total: Where Where
Sort:
Criteria: -1 -1

or change function to something like:

Public Function WorkingDays(StartDate As Variant, EndDate As Variant) As
Integer
On Error GoTo Err_WorkingDays

If IsDate(StartDate) = False OR IsDate(EndDate) = False Then
WorkingDays = 0
Exit Function
End If

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Does your error go away?

gary

I have a WorkingDays function that returns number of working days between 2
dates. The code for it follows (as you can see, it uses values entered
into a Holidays table to exclude them as well as Saturdays and Sundays):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays",
dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


I have a query that has two dae fields: DateReceived and DateShipped. I
show these values, and also an expression which is the Working Days
between:

Turnaround: WorkingDays([DateReceived],[DateShipped])

When I run the query, everything works fine. However, if I choose
View-->Totals, with all columns set to GroupBy in the Totals row, I
suddenly get a Data Type Mismatch error. Can anyone explain why this
would be? Please remember that it works fine as long as he Totals row is
not active.

Thanks for any help.

 

[Gary Walter]

Also, it always a good practice
to close what you open.

Loop

WorkingDays = intCount
rs.Close
DB.Close

Exit_WorkingDays:

Set rs = Nothing
Set DB = Nothing

Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

Personally, I am comfortable using a variation of Dev's Work_Days
function found here:

http://www.mvps.org/access/datetime/date0006.htm

I use this function variation with
a table of holidays: tblHolidays
and a date field called HolidayDate

{replace "tblHolidays" with name of your holiday table
("Holidays") and field name "HolidayDate" with your
field name ("HolDate") in function below.

Function Work_DaysLessHol (BegDate As Variant,
EndDate As Variant) As
Integer
'Adapted from function Work_Days at
' http://www.mvps.org/access/datetime/date0006.htm
' Note that this function ** does ** account for
' holidays based on tblHolidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
Dim HolidayDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
'in following could maybe use WeekDay()
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
HolidayDays = DCount("*", "tblHolidays", "([HolidayDate] Between #" & _
Format(BegDate, "mm/dd/yyyy") & "# AND #" & _
Format(EndDate, "mm/dd/yyyy") & "#) AND " & _
"(Format([HolidayDate],'ddd') <> 'Sat') AND " & _
"(Format([HolidayDate],'ddd') <> 'Sun') ")
Work_DaysLessHol = (WholeWeeks * 5 + EndDays) - HolidayDays
If Work_DaysLessHol < 0 Then Work_DaysLessHol = 0

End Function

So, the actual number of working days between
open and closed dates =

Work_DaysLessHol ([Open_Date], [Closed_Date])

good luck,

gary

It sure sounds like this is not
"the complete picture."

You must have a really efficient
company if everything received
has been shipped (meaning no offense).

Add the following 2 columns to your
query grid (just to check):

Field: IsDate([DateReceived]) IsDate([DateShipped])
Table:
Total: Where Where
Sort:
Criteria: -1 -1

or change function to something like:

Public Function WorkingDays(StartDate As Variant, EndDate As Variant) As
Integer
On Error GoTo Err_WorkingDays

If IsDate(StartDate) = False OR IsDate(EndDate) = False Then
WorkingDays = 0
Exit Function
End If

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Does your error go away?

gary

I have a WorkingDays function that returns number of working days between
2 dates. The code for it follows (as you can see, it uses values entered
into a Holidays table to exclude them as well as Saturdays and Sundays):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As
Integer
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays",
dbOpenSnapshot)

StartDate = StartDate + 1
intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function


I have a query that has two dae fields: DateReceived and DateShipped. I
show these values, and also an expression which is the Working Days
between:

Turnaround: WorkingDays([DateReceived],[DateShipped])

When I run the query, everything works fine. However, if I choose
View-->Totals, with all columns set to GroupBy in the Totals row, I
suddenly get a Data Type Mismatch error. Can anyone explain why this
would be? Please remember that it works fine as long as he Totals row is
not active.

Thanks for any help.

 

[Gary Walter]

In the world of "practice what you preach":

Function Work_DaysLessHol (BegDate As Variant,
EndDate As Variant) As
Integer
'Adapted from function Work_Days at
' http://www.mvps.org/access/datetime/date0006.htm
' Note that this function ** does ** account for
' holidays based on tblHolidays.
On Error GoTo Err_Work_DaysLessHol

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
Dim HolidayDays As Integer

Work_DaysLessHol = 0
If IsDate(BegDate) = False OR IsDate(EndDate) = False Then
Exit Function
End If

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
'in following could maybe use WeekDay()
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
'****change table and field name here if different***
HolidayDays = Dcount("*", "tblHolidays", "([HolidayDate] Between #" & _
Format(BegDate, "mm/dd/yyyy") & "# AND #" & _
Format(EndDate, "mm/dd/yyyy") & "#) AND " & _
"(Format([HolidayDate],'ddd') <> 'Sat') AND " & _
"(Format([HolidayDate],'ddd') <> 'Sun') ")
Work_DaysLessHol = (WholeWeeks * 5 + EndDays) - HolidayDays
If Work_DaysLessHol < 0 Then Work_DaysLessHol = 0

Exit_Work_DaysLessHol:
Exit Function

Err_Work_DaysLessHol:
MsgBox Err.Description
Resume Exit_Work_DaysLessHol
End Function