I have a start and end time. How can I calculate the total "time" (Start - end)?

Hi jstm-ga,

There are three major steps required in achieving the result you are looking for:

1. Convert your String representations of times into Dates (the fact
that there is no actual date is irrelevant)
2. Calculate the duration between the two Date values (start and end times)
3. Manipulate the output Date by converting it to a String and
removing the extraneous information

An example implementation for achieving the above is shown in the
following routine:

--------Begin Code Snippet---------------

Sub Timer()

'Declaring needed variables
Dim start As Date
Dim finish As Date
Dim duration As Date
Dim outStr As String
Dim length As Integer

'Converting time values to dates
start = CDate("4:35:09 AM")
finish = CDate("5:45:26 AM")

'Find duration
duration = finish - start
outStr = CStr(duration)

'Convert to string an trim AM/PM indicator
length = InStr(outStr, "AM")
outStr = Mid(CStr(duration), 1, length - 2)
MsgBox (outStr) 'Now use outStr as desired

End Sub

--------End Code Snippet---------------

Hopefully that is helpful in determining a time duration.

Cheers!

answerguru-ga

---

Request for Answer Clarification by jstm-ga on 12 Mar 2005 17:21 PST

Ok. How do I relate this to the real world. The first time is in text
box "txtstartoperscan" and the second time is in "txtstartoperrun".
When I click button "cmdscan4" I would like the total minutes to show
inside text box "txtminutes"?

---

Clarification of Answer by answerguru-ga on 12 Mar 2005 17:53 PST

Hi again,

You need to make two adjustments:

1. Assign to textbox values to the start and finish date variables as follows:

'Converting time values to dates
start = CDate(txtstartoperscan)
finish = CDate(txtstartoperrun)

2. Assign the calculated string to the result textbox (I also removed
the message box here):

'Convert to string an trim AM/PM indicator
length = InStr(outStr, "AM")
outStr = Mid(CStr(duration), 1, length - 2)
txtminutes = outStr

That should do it - thanks for using Google Answers :)

answerguru-ga

---

Request for Answer Clarification by jstm-ga on 12 Mar 2005 18:12 PST

I get a total of something like this "3:41". (This is 3 mintutes and 41 seconds)

How can I drop the :41 and only record the "3"?

---

Clarification of Answer by answerguru-ga on 12 Mar 2005 18:19 PST

To get rid of the ":41" as in your example, you need to change the
following statement:

outStr = Mid(CStr(duration), 1, length - 2)

to this:

outStr = Mid(CStr(duration), 1, length - 5)

This will always take 3 additional characters off the end of the string.

answerguru-ga

Thanks for your help!

If the datatype of your Start and End values is DateTime, then just
use the DateDiff function, documented here:

[ http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp
]

If not, then convert your Start and End values to DateTime using the
CDate function, documented here:

[ http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
]

and then use the DateDiff function.

I'm only working with time. I am trying to track machine downtime by
using two text boxes that capture start and end time.

When I click the first button textbox1 = time(), and when I click the
second button textbox2 = time().

Now I have something that looks like this "4:35:09 PM" & "5:45:26 PM". 

I would like to get the total time down by substracting the two times
which should equal approx. 1:10:17. Ok, and even better yet I would
like the total time in minutes "87" minutes. PLEASE HELP!

I?d try to minimize the amount of code you have to write and leverage
SQL Server capabilities to do the hard work for you.

=====================================

Create an Access database with one table called tTest, having two fields:

field1	text(50)
field2	text(50)

=====================================

Create a table in your SQL Server database called tTest by running the
following SQL command:

create table tTest (field1 varchar(50), field2 varchar(50))

and then insert some test rows into your SQL Server table with these SQL commands:

insert into tTest(field1, field2) values ('hello', 'world')
insert into tTest(field1, field2) values ('hello', 'mom')
insert into tTest(field1, field2) values ('look', 'no hands')

=====================================

The following script will set up a linked server in your SQL Server,
which will allow you to run a query that references your Access
database.

-- Drop the linked server if it already exists.
if exists(select * from master.dbo.sysservers where srvname = 'MyAccessDb')
	exec sp_dropserver 'MyAccessDb', 'droplogins'

-- Add a linked server to SQL Server.
-- This allows you to reference your
-- Access datbase from queries you
-- run on the SQL Server.
-- Replace @datasrc with the path of your Access database,
-- such as "c:\databases\manf.mdb" or "\\servername\sharename\manf.mdb"
exec sp_addlinkedserver 
   @server = 'MyAccessDb', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'c:\test\db1.mdb'
GO

-- Associate the linked server with an
-- Access login.  If you have not configured
-- security within Access by customizing
-- your MDA file, then leave @rmtuser
-- and @rmtpassword as is.
sp_addlinkedsrvlogin @rmtsrvname = 'MyAccessDb',
    @useself = false,
    @rmtuser = 'admin',
    @rmtpassword = null

=====================================

Now you can execute queries in SQL Server?s Query Analyzer that
reference your Access database, like this:

select * from MyAccessDb...tTest

=====================================

So now all you need to do is execute a SQL insert statement like this:

insert into MyAccessDb...tTest (
	Field1,
	Field2 )
select	Field1,
	Field2
from	tTest

=====================================

In VB, it will look more like this:

Private Sub Command1_Click()
    Dim cn As Connection
    Set cn = New Connection
    cn.ConnectionString =
"UID=sa;PWD=yourpassword;DATABASE=YourSqlDatabase;SERVER=YourServerName;DRIVER={SQL
SERVER}"
    cn.Open
    cn.Execute "insert into MyAccessDb...tTest (Field1,Field2) select
Field1, Field2 from tTest"
End Sub

Good luck.

Oops, I posted this to the wrong question!  This was a comment on how
to insert your SQL Server data into your Access table.

I got the information but you put it in the wrong question...

I already have the SQL table set up and the Access table which are
both identical in field names, etc. In other words the Access table is
a mirror of the table inside SQL server.

Given that, where to I begin reading (start) with your answer? (I'm a
little confused)

I'd use code like this instead:

Private Sub cmdscan4_Click()
    ShowElapseTime
End Sub

Private Sub ShowElapseTime()
    Dim secs, mins As Long
    Dim total As String
    secs = DateDiff("s", CDate(txtstartoperscan.Text), CDate(txtstartoperrun.Text))
    total = ""
    mins = Fix(secs / 60)
    secs = secs Mod 60
    total = mins
    total = total & ":"
    If secs < 10 Then total = total & "0"
    total = total & secs
    txtminutes.Text = total
End Sub

Timer is a built in VB datatype, so I think a sub called Timer() is
maybe not such a good name to use.  I pasted the code you were given
into a VB form and could not get it to work.  Also, just in case your
downtime spans over midnight, I'd use :

txtstartoperscan.Text = Now()     -and-     txtstartoperrun.Text = Now()

to set the time fields.

THANKS Willcodeforfood-ga!!!! This worked great!

Just add the linked server as I demonstrated.

Then get a query like this query working in SQL Query Analyzer:

insert into MyAccessDb...[Raw Data1] (
	Field1,
	Field2 )
select	Field1,
	Field2
from	[Raw Data]

Replace Field1, Field2, etc. with your field names.

Make sure you can run the query in Query Analyzer.  If not post the
SQL error here and I'll take a look at it.

Then put the query into the VB code like this:

Private Sub Command1_Click()
    Dim cn As Connection
    Set cn = New Connection
    cn.ConnectionString = "your connection string goes here"
    cn.Open
    cn.Execute "this is the query you just wrote, but all in one long line"
End Sub

Oh, by the way, you need to add a reference to your VB project to get
this code to run.  Choose Project | References... from the menu.  In
the dialog that appears, scroll down and add Microsoft ActiveX Objects
Library 2.7 (or whatever is the highest number you have) by checking
it.

If you need to clear out the existing data in Access and then bring in
a fresh copy from SQL Server, adjust the VB code like this:

Private Sub Command1_Click()
    Dim cn As Connection
    Set cn = New Connection
    cn.ConnectionString = "your connection string goes here"
    cn.CommandTimeout = 600  ' allow commands to run for 10 minutes
    cn.Open
    cn.Execute "delete from MyAccessDb...[Raw Data1]"
    cn.Execute "insert into MyAccessDb...[Raw Data1] (Field1,Field2)
select Field1, Field2 from [Raw Data]"
End Sub

Will..

I'm a little confused on how to set up the linked server? 

Do I need to put in the following code: if exists(select * from
master.dbo.sysservers where srvname = 'MyAccessDb') exec sp_dropserver
'MyAccessDb', 'droplogins'

Just run two commands in SQL Query Analyzer to set up the linked server:

exec sp_addlinkedserver 
   @server = 'MyAccessDb', 
   @provider = 'Microsoft.Jet.OLEDB.4.0', 
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'c:\test\db1.mdb'
GO

exec sp_addlinkedsrvlogin @rmtsrvname = 'MyAccessDb',
    @useself = false,
    @rmtuser = 'admin',
    @rmtpassword = null
GO


NOTE:

You need to change 'c:\test\db1.mdb' to the path of your Access
database.  If the Access datbase is on another computer besides the
SQL Server, then use something more like this:

@datasrc = '\\computername\sharename\db1.mdb'

Here is what I put in:

exec sp_addlinkedserver 
   @server = MADISONVILLEKRO
   @provider = Microsoft.Jet.OLEDB.4.0
   @srvproduct = OLE DB Provider for Jet
   @datasrc = P:\[Production Reporting System]\[Production Reporting System].mdb
GO

exec sp_addlinkedsrvlogin @rmtsrvname = MADISONVILLEKRO
    @useself = false
    @rmtuser = admin
    @rmtpassword = null
GO


Here is the Error Message:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@provider'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@useself'.

Try this:

exec sp_addlinkedserver 
   @server = 'MADISONVILLEKRO',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'P:\Production Reporting System\Production Reporting System.mdb'
GO

exec sp_addlinkedsrvlogin @rmtsrvname = 'MADISONVILLEKRO',
    @useself = false,
    @rmtuser = 'admin',
    @rmtpassword = null
GO

This will probably work, but may not.  Test it by running this query
in Query Analyzer:

select * from MADISONVILLEKRO...[Raw Data1]

If it doesn't work then run this command in Query Analyzer:

exec sp_dropserver 'MADISONVILLEKRO', 'droplogins'

You might get an error because SQL Server does not "see" your P: drive
mapping.  If not, then you need to look up your P: drive mapping. 
Open the My Computer icon and look at how it is mapped.  If the P:
drive is mapped to, for example:

somefolder on 'SOMESERVER' (P:)

Run this command to link the server to your Access database:

exec sp_addlinkedserver 
   @server = 'MADISONVILLEKRO',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = '\\SOMESERVER\somefolder\Production Reporting
System\Production Reporting System.mdb'
GO

exec sp_addlinkedsrvlogin @rmtsrvname = 'MADISONVILLEKRO',
    @useself = false,
    @rmtuser = 'admin',
    @rmtpassword = null
GO

Of course you'll need to replace "SOMESERVER" with your server's name
and "somefolder" with the share folder that the P: drive is mapped to.

Here is the error message now:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message: 'P:\Production Reporting
System\Production Reporting System.mdb' is not a valid path.  Make
sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]

I looked on My Computer and here is what it looks like: "Public on
'10.4.40.61' (P:)".

Should I put in "\\Public on '10.4.40.61'(P:)\Production Reporting
System\Production Reporting System.mdb"

?

I can't see to get passed the "@datasrc ="

I looked under map network drive and it looks like this "P: \\10.4.40.61\public".

?

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message: 'P:\10.4.40.61\public\Production
Reporting System\Production Reporting System.mdb' is not a valid path.
 Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]

Was working on your charting problem...

First, drop the old linked server:

exec sp_dropserver 'MADISONVILLEKRO', 'droplogins'

=====================================================

Now, try this:

exec sp_addlinkedserver 
   @server = 'MADISONVILLEKRO',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = '\\10.4.40.61\public\Production Reporting
System\Production Reporting System.mdb'
GO

exec sp_addlinkedsrvlogin @rmtsrvname = 'MADISONVILLEKRO',
    @useself = false,
    @rmtuser = 'admin',
    @rmtpassword = null
GO

-- NOTE:  Put the @datasrc all on one line, it wraps here because the pages
--        are just not wide enough for this sort of thing.

=====================================================

Then test with:

select * from MADISONVILLEKRO...[Raw Data1]

If you still have trouble, make sure to set the permissions on the [
\\10.4.40.61\public ] folder so that everyone can read files in the
folder, at least while you get this working.  This will ensure that
the Windows account that SQL Server uses to do its work has read
permissions on the Access database file.

Or you can set the SQL Server service to start using your own personal
Windows account rather than the built in system account.  Do this in
the Control Panel, Administrative Tools, Services applet.  Then stop
and restart the SQL Server service.  That way, SQL Server will have
permission to read any file you can, namely the Access database.