?
Solved

Excel VBA Time and Date Calculations reading a SQL database

Posted on 2013-05-20
9
Medium Priority
?
281 Views
Last Modified: 2013-05-21
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 conSQL As ADODB.Connection
  Dim strSQL As String
  Dim i As Integer
  Dim Total As Variant
  Dim rs As ADODB.Recordset
  Total = 0
  i = 0

  Set conSQL = New ADODB.Connection
  conSQL.Open "Server=THEDUB;DRIVER=SQL    
  Server;Database=WRC;Password=*****;User ID=sales"
   
  strSQL = "Select Sum(TimeDiff(TimeClock.TimeIn, TimeClock.TimeOut) AS sum_units" & _
  " WHERE DateValue(TimeClock.TimeIn) >= (" & BegDate1 & ")" & _
  " AND DateValue(TimeClock.TimeOut) <  (" & EndDate1 & ")"
  Set rs = New ADODB.Recordset
  rs.Open strSQL, conSQL, adOpenStatic, adLockOptimistic

  If Not rs.EOF Then
    HrsWorkedAll = rs.GetRows(-1, 1, "sum_units")(0, 0)
  Else
    HrsWorkedAll = 0
  End If
  conSQL.Close

Open in new window

0
Comment
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
  • Learn & ask questions
9 Comments
 
LVL 49

Expert Comment

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

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 600 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 & ")"

Open in new window


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

Open in new window


BFN,

fp.
0
 

Author Comment

by:bikeski
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 & ")"

Open in new window


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

Thanks,
Ron
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Expert Comment

by:[ fanpages ]
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

by:[ fanpages ]
ID: 39185784
PPS. In case the content on this web page is helpful:

"Date / Time Arithmetic with Oracle 9/10"
[ http://www.akadia.com/services/ora_date_time.html ]
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 1400 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 & ")"

Open in new window

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

by:bikeski
ID: 39185823
We are using MS SQL Server 2005
0
 

Author Closing Comment

by:bikeski
ID: 39185920
Thanks BFN & andrewssd3, both answers helped.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39185985
You're welcome.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question