I"ve moved the SQL from the Access Query to a VBA function that returns the resulting recordset to a worksheet for testing. This works as a SQL call using adCmdText, but not as when trying to use the command object against a storedprocdure. (Is that not for Access, but rather SQL Server?)

I'd like to just pull the resulting aggregate value of total minutes from the tbl design as described in earlier post. With that number I could do the math needed for my other purposes.

Any Ideas?

If not, I think I'll just create a template sheet and use excel formulas to calculate the result for me after dumping the recordset with just the time pairs. That would eliminate all the IIF's in the SQL. SQL would get much simpler.

This is part of a project where Excel is the front end to an Access DB, so it's gotta go there at some point anyway. I just like to learn new ways of doing things more efficiently.

Thanks for any insights,

Shred

[VBA]Public Function getTotalWorkedTime(lempID As Long, dtFrom As Date, dtTo As Date) As Boolean
'---------------------------------------------------------------------------------------
' Procedure : getTotalWorkedTime
' Author : Shred Dude
' Date : August 16, 2011 - 19:47
' Purpose : Pull recordset for total time worked b/t two dates for a given empID
'---------------------------------------------------------------------------------------
'
Const sPROCEDURE As String = "getTotalWorkedTime"

Dim bResult As Boolean
Dim sSQL As String

Dim rsData As ADOdb.Recordset
Dim cmAccess As ADOdb.Command
Dim objParams As ADOdb.Parameters

Dim lAffected As Long

On Error GoTo errHandler

'Modified SQL from qryEmployeeTimeBetweenTwoDates
sSQL = "PARAMETERS lEmpID Long, dtFrom DateTime, dtTo DateTime; " & _
"SELECT T.EmpID, T.dtWork, T.TimeIn1, T.TimeOut1, T.TimeIn2, T.TimeOut2, " & _
"(DateDiff(""n"", [TimeIn1], [TimeOut1])) + IIf(Not (IsNull([TimeIn2]) Or IsNull([TimeOut2])), IIf([TimeOut2] > 0, DateDiff(""n"", [TimeIn2], [TimeOut2]), DateDiff(""n"", [TimeIn2], DateAdd(""d"", 1, [TimeOut2]))), 0) AS TotalMinutes, " & _
"[TotalMinutes]/60 AS TotalHours " & _
"FROM tblDailyTimeSheets AS T " & _
"WHERE (((T.EmpID)=[lempid]) AND ((T.dtWork) Between [dtfrom] And [dtto]) AND (Not (T.TimeIn1) Is Null)) " & _
"ORDER BY T.dtWork;"

'Confirm connection...
If Not mcnConnect.State = adStateOpen Then mcnConnect.Open

' Create the Command object.
Set cmAccess = New ADOdb.Command

With cmAccess
.ActiveConnection = mcnConnect
.CommandText = sSQL
.CommandType = adCmdText

' ''Not Working with Access Database...
' .CommandText = "qryEmployeeTimeBetweenDates"
' .CommandType = adCmdStoredProc

' Create and append the parameters.
Set objParams = .Parameters

objParams.Append .CreateParameter("EmpID", adInteger, adParamInput, 0, lempID)
objParams.Append .CreateParameter("dtFrom", adDate, adParamInput, 10, dtFrom)
objParams.Append .CreateParameter("dtTo", adDate, adParamInput, 10, dtTo)

'Execute the Query
Set rsData = .Execute(lAffected)

End With

'Dump data to a worksheet for testing...
If gwkbUI Is Nothing Then HookUI

With gwkbUI.Sheets(sSheetTabName(gwkbUI, "wksTest"))
.Activate
With .[ptrTitle]
.CurrentRegion.ClearContents
.CopyFromRecordset rsData
.Offset(0, 2).Resize(.CurrentRegion.Rows.Count, 4).NumberFormat = "[$-409]h:mm AM/PM;@"
.CurrentRegion.Columns.AutoFit
End With
End With


errExit:
getTotalWorkedTime = bResult

'Release Objects

DestroyADORecordset rsData
Set objParams = Nothing
Set cmAccess = Nothing

'Restore Application Settings
'XL_RestoreDefaults

Exit Function

errHandler:

bResult = False

If bCentralErrorHandler(msMODULE, sPROCEDURE, , False) Then
'if in Debug Mode go here...
Stop
Resume
Else
Resume errExit
End If

End Function
[/VBA]