Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA Time and Date Calculations reading a SQL database

Posted on 2013-05-20
9
Medium Priority
?
289 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 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

783 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