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
Solved

Excel VBA Time and Date Calculations reading a SQL database

Posted on 2013-05-20
9
247 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
9 Comments
 
LVL 48

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 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 & ")"

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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 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 & ")"

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 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