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 3 7466
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
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
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 :-) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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
|
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.
|
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:...
|
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...
| |
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...
|
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
|
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...
|
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...
|
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...
|
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...
| |
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...
|
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...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
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...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
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...
| |