Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 9th, 2007, 01:55 PM
Friend of Wrox
Join Date: May 2006
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default convert datatype


I have a table that logs a datetime every a machine starts or stops. I need to find the total downtime (stop time - start time). I'm looping through the table to get this, but I'm not sure how to convert the datetime into only time.
example: I need it to be 48:22:11 instead of 1/2/1900 22:11.

Thanks for any help,


Old August 10th, 2007, 06:45 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts


   I would play around with something like:

Dim dtDate As Long

dtDate = Now()

This yields the number of seconds since the starting date for date stamps, which I forget what that is. I could look it up in the Wrox collection. Anyway, you might then convert that number into the format that you want based on the proper arithmetic.

I know there is an easier way to do this in SQL Server, especially, but don't have access to the collection right now.

Play around with vbscript and echoing the value and see if you get any ideas. I will look around later if you are still stumped.

Did that help?

Old August 14th, 2007, 05:49 PM
Authorized User
Join Date: Apr 2007
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts

Convert(Varchar,CompletedDT,108) Would put the DT info into your 48:22:11 example

I also have two several machines where I compute differences between start and stop time which are returned to me by sql in the form of datetime, but I render the results to the user in hours. Here is how I do it:

Format$(DateDif"n",View_PdateLiveLines.FirstLayerD T,View_PdateLiveLines.LastLayerDT)/60,"##.0")),"") AS TotalRunningHours

This just uses the DateDiff function and returns the difference between start and stop time in minutes "n". Then, I divide by 60 to put it in hours and throw a formate around the whole thing in numeric decimals.

Let me know if this helps, I could look into some other areas of my code where I have done other similar functions.


Old August 15th, 2007, 02:15 PM
Friend of Wrox
Join Date: May 2006
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks guys.

I'm gonna try some of your suggestions and see what I come up with.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert datatype Text to image roopaksr ADO.NET 2 September 6th, 2008 03:02 PM
datatype umeshtheone SQL Server 2000 2 May 16th, 2007 11:56 AM
DataType Mismatch webgen ASP.NET 1.0 and 1.1 Professional 3 November 17th, 2005 12:35 PM
datatype ngang SQL Language 1 November 18th, 2003 09:55 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.