Solved

# Excel VBA Time and Date Calculations reading a SQL database

Posted on 2013-05-20
260 Views
Hello Experts,

I'd like to total the numbers of employee hours worked per day. Below is my attempt but my SQL statement is not working. Thanks

``````Function HrsWorkedAll(BegDate1, EndDate1) As Variant

Dim strSQL As String
Dim i As Integer
Dim Total As Variant
Total = 0
i = 0

conSQL.Open "Server=THEDUB;DRIVER=SQL

strSQL = "Select Sum(TimeDiff(TimeClock.TimeIn, TimeClock.TimeOut) AS sum_units" & _
" WHERE DateValue(TimeClock.TimeIn) >= (" & BegDate1 & ")" & _
" AND DateValue(TimeClock.TimeOut) <  (" & EndDate1 & ")"

If Not rs.EOF Then
HrsWorkedAll = rs.GetRows(-1, 1, "sum_units")(0, 0)
Else
HrsWorkedAll = 0
End If
conSQL.Close
``````
0
Question by:bikeski
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 48

Expert Comment

ID: 39183253
what dbms are you using? i.e. what are you executing that sql against?
0

LVL 35

Assisted Solution

[ fanpages ] earned 150 total points
ID: 39183523
Hi,

Should the contents of the SQL statement string variable include a FROM clause?

Presently:
`````` strSQL = "Select Sum(TimeDiff(TimeClock.TimeIn, TimeClock.TimeOut) AS sum_units" & _
" WHERE DateValue(TimeClock.TimeIn) >= (" & BegDate1 & ")" & _
" AND DateValue(TimeClock.TimeOut) <  (" & EndDate1 & ")"
``````

Suggestion:
`````` strSQL = "Select Sum(TimeDiff(TimeClock.TimeIn, TimeClock.TimeOut) AS sum_units" & _
" FROM TimeClock" & _
" WHERE DateValue(TimeClock.TimeIn) >= (" & BegDate1 & ")" & _
" AND DateValue(TimeClock.TimeOut) <  (" & EndDate1 & ")"
``````

BFN,

fp.
0

Author Comment

ID: 39185727
Your right, I was missing the From statement. TimeDiff and DateValue are not functions, so I changed the SQL statement as listed below. Now I'm getting an error: "operand data type datetime is an invalid for sum operation"

`````` SQLHrsWorkedAll = "Select Sum(TimeClock.TimeOut - TimeClock.TimeIn) AS sum_units" & _
" FROM TimeClock" & _
" WHERE TimeClock.TimeIn >= (" & BegDate1 & ")" & _
" AND TimeClock.TimeOut <  (" & EndDate1 & ")"
``````

Looks like I need to convert the datetime field to an summable data type. What do you suggest?

Thanks,
Ron
0

LVL 35

Expert Comment

ID: 39185774
Hi Ron,

What is the calculation, or the resultant value, you are trying to derive?

The number of minutes that the individual was "Clocked In"?

If your source database has support for the TO_CHAR() function, perhaps you can use that & then use the Access CDate() function.

Alternatively, perhaps your can manipulate the [TimeClock] table columns as string (text) values, taking elements of the string to form a "DD/MM/YYYY" (or, in preference, a "DD-MMM-YYYY") format that, again, can be passed to the CDate() function.

PS. PortletPaul asked (above) what database management system you are using.

Is the source database an Oracle server?

BFN,

fp.
0

LVL 35

Expert Comment

ID: 39185784
PPS. In case the content on this web page is helpful:

"Date / Time Arithmetic with Oracle 9/10"
0

LVL 17

Accepted Solution

andrewssd3 earned 350 total points
ID: 39185820
You seem to be using SQL Server, so you should be able to use DATEDIFF:
`````` SQLHrsWorkedAll = "Select Sum(DATEDIFF(minute, TimeClock.TimeOut, TimeClock.TimeIn)) AS sum_units" & _
" FROM TimeClock" & _
" WHERE TimeClock.TimeIn >= (" & BegDate1 & ")" & _
" AND TimeClock.TimeOut <  (" & EndDate1 & ")"
``````
DATEDIFF only returns integer values, so the above will be a whole number of minutes.  If you need greater resolution you can use second or even millisecond (I don't want to work for you in this case), then divide as appropriate to get a decimal value.  See T-SQL Reference
0

Author Comment

ID: 39185823
We are using MS SQL Server 2005
0

Author Closing Comment

ID: 39185920
Thanks BFN & andrewssd3, both answers helped.
0

LVL 35

Expert Comment

ID: 39185985
You're welcome.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month2 days, 21 hours left to enroll