Viewing Totals/GroupBy creates Data Mismatch

N

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.
 
G

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
Click to expand...
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
Click to expand...
Does your error go away?

gary

NewbieSupreme said:
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.
Click to expand...
 
G

Gary Walter

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

WorkingDays = intCount
rs.Close
DB.Close

Exit_WorkingDays:
Click to expand...

Set rs = Nothing
Set DB = Nothing
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
Click to expand...


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

Gary Walter said:
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
Click to expand...
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
Click to expand...
Does your error go away?

gary

NewbieSupreme said:
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.
Click to expand...
Click to expand...
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom Function 6
Function "Workingdays" query error date 6
Calculating Rolling Usage 1
#Error in Results 4
data type mismatch error 3464 1
Calculate businness Days 1
weekends and holidays! 5
How to use a function in a query 1

Top