Solved

Excel VBA Time and Date Calculations reading a SQL database

Posted on 2013-05-20
9
233 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Hiding column macro 10 28
Help with Adding text from a form to a worksheet 5 37
Fixing a embedded format 7 29
Applying Background Image to All Sheets in Excel 1 17
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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

864 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now