Solved

Excel VBA Time and Date Calculations reading a SQL database

Posted on 2013-05-20
9
221 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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

18 Experts available now in Live!

Get 1:1 Help Now