date/time computation in an expression column

  • Thread starter Thread starter Reney
  • Start date Start date
R

Reney

I am using Access in my project. In one of the forms, I am calling two
tables, and two of the columns have date/time type, namely "ClockIn" and
"ClockOut". I created a dataset and filled the dataset already. But I need
to add another column which should calculate the difference between these
two columns. I don't know how to write code in the expression to accomplish
this date difference calculation. Any help would be appreciated. Here is the
code:

Dim conn As New OleDbConnection(connectionString)
Dim sql As String = "SELECT TimeLog.dayOfWeek As [Dates],
TimeLog.employeeID AS [UserName], " _
& "Employee.firstName, Employee.lastName, " _
& "TimeLog.clockStart AS [ClockIn], TimeLog.clockEnd As [ClockOut],
" _
& "Employee.payRate " _
& "FROM Employee INNER JOIN TimeLog ON Employee.employeeID =
TimeLog.employeeID " _
& "WHERE TimeLog.employeeID = " & "'" & userName & "' " _
& "AND TimeLog.dayOfWeek >= " & "#" & weekOneStartDay & "# " _
& "ORDER BY TimeLog.dayOfWeek"

Try
conn.Open()
Dim da As New OleDbDataAdapter(sql, conn)
Dim ds As New DataSet()
da.Fill(ds, "EmployeeTimeLog")
da.Dispose()

If ds.Tables.Count = 0 Then
MessageBox.Show("Invalid username", "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)
Else

Dim dt As DataTable = ds.Tables(0)

'*********************************************************
'This will be the calculated column. It should take the hourdifference
between ClockOut and ClockIn columns in the dataset.
Dim dcHours As DataColumn = New DataColumn("Hours")
dcHours.DataType = System.Type.GetType("System.DateTime")
dt.Columns.Add(dcHours)

dcHours.Expression = "clockOut - clockIn" ' How could I write an
expression here to take the hour difference???
'***********************************************************
dgrTimeLog.DataSource = dt
End If

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
dgrTimeLog.Visible = True
End Try

If you could reccomend me any other solution to reach the goal, that'd be
appreciated also.
Thanks in advance,
Yener
 
Reney,

You need to use the DateDiff function to compute the hours between
the two columns. Unfortunately the datacolumn expression does not support
it. To get the difference add the expression to the select statement. Here
is an example.

Select OrderID, OrderDate, ShippedDate, DATEDIFF(hh, OrderDate,ShippedDate)
as Hrs from Orders"

Here is a link to more info on DateDiff.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_datediff.asp

Ken
 
Ken

Thanks a lot for the reccomendation and link you have provided. It was very
helpful. The problem now is, it gives me the error : "No Value given for one
or more required parameters"
This should be due to an error in SQL statement that I am using. I was
wondering maybe the Jet engine for access does not support datediff
function? Do you have any knowledge about it?

Dim sql As String = "SELECT dayOfWeek As [Dates], TimeLog.employeeID AS
[UserName], " _
& "firstName, lastName, " _
& "clockStart AS [ClockIn], clockEnd As [ClockOut], " _
& "payRate, DATEDIFF (dd, clockStart, clockEnd) AS [Hours] " _
& "FROM Employee INNER JOIN TimeLog ON Employee.employeeID =
TimeLog.employeeID " _
& "WHERE TimeLog.employeeID = " & "'" & userName & "' " _
& "AND dayOfWeek >= " & "#" & weekOneStartDay & "# " _
& "ORDER BY dayOfWeek"

That was the SQL Statement that I used in the program.
Reney


Ken Tucker said:
Reney,

You need to use the DateDiff function to compute the hours between
the two columns. Unfortunately the datacolumn expression does not support
it. To get the difference add the expression to the select statement. Here
is an example.

Select OrderID, OrderDate, ShippedDate, DATEDIFF(hh, OrderDate,ShippedDate)
as Hrs from Orders"

Here is a link to more info on DateDiff.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_datediff.asp

Ken

---------------

Reney said:
I am using Access in my project. In one of the forms, I am calling two
tables, and two of the columns have date/time type, namely "ClockIn" and
"ClockOut". I created a dataset and filled the dataset already. But I need
to add another column which should calculate the difference between these
two columns. I don't know how to write code in the expression to accomplish
this date difference calculation. Any help would be appreciated. Here is the
code:

Dim conn As New OleDbConnection(connectionString)
Dim sql As String = "SELECT TimeLog.dayOfWeek As [Dates],
TimeLog.employeeID AS [UserName], " _
& "Employee.firstName, Employee.lastName, " _
& "TimeLog.clockStart AS [ClockIn], TimeLog.clockEnd As [ClockOut],
" _
& "Employee.payRate " _
& "FROM Employee INNER JOIN TimeLog ON Employee.employeeID =
TimeLog.employeeID " _
& "WHERE TimeLog.employeeID = " & "'" & userName & "' " _
& "AND TimeLog.dayOfWeek >= " & "#" & weekOneStartDay & "# " _
& "ORDER BY TimeLog.dayOfWeek"

Try
conn.Open()
Dim da As New OleDbDataAdapter(sql, conn)
Dim ds As New DataSet()
da.Fill(ds, "EmployeeTimeLog")
da.Dispose()

If ds.Tables.Count = 0 Then
MessageBox.Show("Invalid username", "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error)
Else

Dim dt As DataTable = ds.Tables(0)

'*********************************************************
'This will be the calculated column. It should take the hourdifference
between ClockOut and ClockIn columns in the dataset.
Dim dcHours As DataColumn = New DataColumn("Hours")
dcHours.DataType = System.Type.GetType("System.DateTime")
dt.Columns.Add(dcHours)

dcHours.Expression = "clockOut - clockIn" ' How could I write an
expression here to take the hour difference???
'***********************************************************
dgrTimeLog.DataSource = dt
End If

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
dgrTimeLog.Visible = True
End Try

If you could reccomend me any other solution to reach the goal, that'd be
appreciated also.
Thanks in advance,
Yener
Click to expand...
Click to expand...
 
Hi Cindy,

You were exactly right. I tried it with Access, put the first argument in
quotes, and there it goes.. I overcomed this little but huge problem for me
with your helps, thanks a lot..

Have a great day,
Reney

Cindy Meister -WordMVP- said:
Hi Reney,

The way to find that out would be to start Access and try to
use it in a Query expression in the UI... In this case,
you could simply compute it outside the SQL and use the result
in the SQL?

HOWEVER:
1) You need "quotes" around the first argument (where you have
dd), otherwise Access/Jet will try to interpret this as a
field name

2) "dd" is not valid, perhaps you want "d"? Or maybe "h"?
This should be due to an error in SQL statement that I am using. I was
wondering maybe the Jet engine for access does not support datediff
function?

& "payRate, DATEDIFF (dd, clockStart, clockEnd) AS [Hours] "
Click to expand...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail :-)
Click to expand...
 
Back
Top