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
