473,554 Members | 3,071 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

date/time computation in an expression column

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 (connectionStri ng)
Dim sql As String = "SELECT TimeLog.dayOfWe ek As [Dates],
TimeLog.employe eID AS [UserName], " _
& "Employee.first Name, Employee.lastNa me, " _
& "TimeLog.clockS tart AS [ClockIn], TimeLog.clockEn d As [ClockOut],
" _
& "Employee.payRa te " _
& "FROM Employee INNER JOIN TimeLog ON Employee.employ eeID =
TimeLog.employe eID " _
& "WHERE TimeLog.employe eID = " & "'" & userName & "' " _
& "AND TimeLog.dayOfWe ek >= " & "#" & weekOneStartDay & "# " _
& "ORDER BY TimeLog.dayOfWe ek"

Try
conn.Open()
Dim da As New OleDbDataAdapte r(sql, conn)
Dim ds As New DataSet()
da.Fill(ds, "EmployeeTimeLo g")
da.Dispose()

If ds.Tables.Count = 0 Then
MessageBox.Show ("Invalid username", "Error", MessageBoxButto ns.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("Hou rs")
dcHours.DataTyp e = System.Type.Get Type("System.Da teTime")
dt.Columns.Add( dcHours)

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

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

If you could reccomend me any other solution to reach the goal, that'd be
appreciated also.
Thanks in advance,
Yener
Nov 20 '05 #1
3 7467
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,Shipp edDate)
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.c om> 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 (connectionStri ng)
Dim sql As String = "SELECT TimeLog.dayOfWe ek As [Dates],
TimeLog.employe eID AS [UserName], " _
& "Employee.first Name, Employee.lastNa me, " _
& "TimeLog.clockS tart AS [ClockIn], TimeLog.clockEn d As [ClockOut], " _
& "Employee.payRa te " _
& "FROM Employee INNER JOIN TimeLog ON Employee.employ eeID =
TimeLog.employe eID " _
& "WHERE TimeLog.employe eID = " & "'" & userName & "' " _
& "AND TimeLog.dayOfWe ek >= " & "#" & weekOneStartDay & "# " _
& "ORDER BY TimeLog.dayOfWe ek"

Try
conn.Open()
Dim da As New OleDbDataAdapte r(sql, conn)
Dim ds As New DataSet()
da.Fill(ds, "EmployeeTimeLo g")
da.Dispose()

If ds.Tables.Count = 0 Then
MessageBox.Show ("Invalid username", "Error", MessageBoxButto ns.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("Hou rs")
dcHours.DataTyp e = System.Type.Get Type("System.Da teTime")
dt.Columns.Add( dcHours)

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

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

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

Nov 20 '05 #2
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.employe eID AS
[UserName], " _
& "firstName, lastName, " _
& "clockStart AS [ClockIn], clockEnd As [ClockOut], " _
& "payRate, DATEDIFF (dd, clockStart, clockEnd) AS [Hours] " _
& "FROM Employee INNER JOIN TimeLog ON Employee.employ eeID =
TimeLog.employe eID " _
& "WHERE TimeLog.employe eID = " & "'" & userName & "' " _
& "AND dayOfWeek >= " & "#" & weekOneStartDay & "# " _
& "ORDER BY dayOfWeek"

That was the SQL Statement that I used in the program.
Reney
"Ken Tucker" <vb***@bellsout h.net> wrote in message
news:%2******** ********@TK2MSF TNGP12.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,Shipp edDate) 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.c om> 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 (connectionStri ng)
Dim sql As String = "SELECT TimeLog.dayOfWe ek As [Dates],
TimeLog.employe eID AS [UserName], " _
& "Employee.first Name, Employee.lastNa me, " _
& "TimeLog.clockS tart AS [ClockIn], TimeLog.clockEn d As

[ClockOut],
" _
& "Employee.payRa te " _
& "FROM Employee INNER JOIN TimeLog ON Employee.employ eeID =
TimeLog.employe eID " _
& "WHERE TimeLog.employe eID = " & "'" & userName & "' " _
& "AND TimeLog.dayOfWe ek >= " & "#" & weekOneStartDay & "# " _
& "ORDER BY TimeLog.dayOfWe ek"

Try
conn.Open()
Dim da As New OleDbDataAdapte r(sql, conn)
Dim ds As New DataSet()
da.Fill(ds, "EmployeeTimeLo g")
da.Dispose()

If ds.Tables.Count = 0 Then
MessageBox.Show ("Invalid username", "Error", MessageBoxButto ns.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("Hou rs")
dcHours.DataTyp e = System.Type.Get Type("System.Da teTime")
dt.Columns.Add( dcHours)

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

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

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


Nov 20 '05 #3
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**********@s wissonline.ch> wrote in message
news:VA.000082e 1.0028b4f5@spee dy...
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 :-)

Nov 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5203
by: androtech | last post by:
Hello, I'm looking for a function that returns a date range for a specified week number of the year. I'm not able to find functions like this anywhere. Any pointers/help would be much appreciated. TIA
7
682722
by: vnl | last post by:
I'm trying to run a SQL query but can't find any records when trying to select a certain date. Here's the sql: SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time = '01-SEP-02' I'm getting no results. The date_and_time field is formatted like this: 2002-SEP-02 00:01:04
7
32929
by: Marc Pelletier | last post by:
Hello, I have a table with a Day field, defined as smalldatetime. I am filling it from a CSharp application with the following code: DataRow r = dtStaDays.NewRow(); r= station_ID; r = sd.Date; r = rangeTide; etc.
4
10043
by: William Bradley | last post by:
I have two cells on a form. One of them is the "Production Date" and the other is the "Expiry Date". The "Expiry Date" is 183 days after the "Production Date." On an Excel spreadsheet, the "Expiry Date" is automatically entered, when the "Production Date" is entered. To do this the "Expiry Date" cell carries the following formula:...
10
2959
by: Kenneth | last post by:
I have a Query that consist of a lot of different sales data, and one of the colums are different date. The date goes from 1jan2003 til 31jan2003. in this Query I only want the salesdata for 1jan2003. How do I remove the dates , 2jan2003 til 31jan2003 without removing them from the table, from the Query? (Because I want to use the data for...
4
9612
by: yer darn tootin | last post by:
Does anyone know the sort expression for a column that's data has been returned in the format, eg '07 Jul 05'?? The sort expression {..:"dd mmm yy"} doesn't work ( if the column was returned as '07-Mar-05' the expression {..:dd-MMM-yy} works OK Second question, does anyone know hwo to return a date from SQL server in the format...
335
11543
by: extrudedaluminiu | last post by:
Hi, Is there any group in the manner of the C++ Boost group that works on the evolution of the C language? Or is there any group that performs an equivalent function? Thanks, -vs
17
1605
by: Mr.Kane | last post by:
Here's my dilemma. I am putting together a trend report using PivotCharts and so the query that I am trying to construct basically would look at the "Date_Entered" for a record and if the "day" portion of the Date is <= 15 (ie 1/1/2006 - 1/15/2006) it will populate a temp column with the actual month and year. However if the "day" portion...
2
2417
by: vaibhav | last post by:
Hi I am writing a wrapper over the XALAN XSLT engine, for transforming XML documents. My problem is that I need to pass date as a parameter ( with format mm/dd/yyyy hh.mm.ss) to the XSLT engine at run time (using the api setStylesheetParam(char* key, char* expression)). The first argument to setStylesheetParam is the key value and the...
0
7512
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8023
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7547
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6129
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5426
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3548
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3536
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1117
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
828
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.