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" <vb2ae@bellsouth.net> wrote in message
news:%23YLTsLvbDHA.1748@TK2MSFTNGP12.phx.gbl...[color=blue]
> 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.[/color]
Here[color=blue]
> is an example.
>
> Select OrderID, OrderDate, ShippedDate, DATEDIFF(hh,[/color]
OrderDate,ShippedDate)[color=blue]
> as Hrs from Orders"
>
> Here is a link to more info on DateDiff.
>
>[/color]
http://msdn.microsoft.com/library/de...e_datediff.asp[color=blue]
>
> Ken
>
> ---------------
>
> "Reney" <yenery@yahoo.com> wrote in message
> news:kmZ3b.102$mG4.32@bignews5.bellsouth.net...[color=green]
> > 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[/color][/color]
need[color=blue][color=green]
> > to add another column which should calculate the difference between[/color][/color]
these[color=blue][color=green]
> > two columns. I don't know how to write code in the expression to[/color]
> accomplish[color=green]
> > this date difference calculation. Any help would be appreciated. Here is[/color]
> the[color=green]
> > 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[/color]
> [ClockOut],[color=green]
> > " _
> > & "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",[/color][/color]
MessageBoxButtons.OK,[color=blue][color=green]
> > 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[/color][/color]
be[color=blue][color=green]
> > appreciated also.
> > Thanks in advance,
> > Yener
> >
> >[/color]
>
>[/color]
