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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:kmZ3b.102$(E-Mail Removed)...
> > 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
> >
> >
>
>
Similar Threads
