date/time computation in an expression column

[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

[Ken Tucker]

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/de...e_datediff.asp

Ken

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

"Reney" <ye****@yahoo.com> wrote in message
news:km**************@bignews5.bellsouth.net...

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]

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" <vb***@bellsouth.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

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/de...e_datediff.asp
Ken

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

"Reney" <ye****@yahoo.com> wrote in message
news:km**************@bignews5.bellsouth.net...

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]

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-" <Ci**********@swissonline.ch> wrote in message
news:VA.000082e1.0028b4f5@speedy...

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] "

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 :-)