format milliseconds?

Derek Brown
Derek Brown used Ask the Experts™
on
What is the format in a query grid that will show milliseconds?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
A date/time stamp does not have that resolution.   What are you trying to accomplish?

Jim.
John TsioumprisSoftware & Systems Engineer

Commented:
I think that milliseconds cannot be formated...you probably have to format them on your own

Author

Commented:
Hi John I would like to know how to do that but my help file does not give that info.

Hi Jim Ignore Dates after Type B but I cannot move more than one record between Type A and Type B
A1.PNG
Moving records to different rows in a query.  I'm dealing with clients that have data 10 years old. They want an option to change the order that records are seen on a report. At the moment they are listed in DateCreated order. So the first entry always appears first in a list and later orders are listed in subsequent DateCreated order. So the idea is, from 2 comboboxs on a form select from the first an order to move then second combo select the record where you want the record to move to fit just above. The record to move will be between the record selected and the one above. The problem will only arise when a number of records need to be placed above the same record in combo 2.

If I could use the millisecond or the double format then no problem but if two records are 8 seconds away from each other then I can only fit the first between the two would be 4 second apart the ext 2 seconds and finally the next 1 second. Then I cannot split a second and all stops there. With milliseconds I have another 6 opportunities to move records before I hit the same problem and if using double hundreds. It looks like my best option will be to update each row in the query's DateCreated field by using Gustav's:

Public Function UpdateDates()
    Dim rs          As DAO.Recordset
    Dim LastDate    As Date
   
    Set rs = Me.RecordsetClone
   
    If rs.RecordCount > 0 Then
        While Not rs.EOF
            If IsNull(rs!YourDateField.Value) Then
                rs.Edit
                    rs!YourDateField.Value = DateAdd("h", 1, LastDate)
                rs.Update
            End If
            LastDate = rs!YourDateField.Value
        Wend
        rs.MoveNext
    End If
    rs.Close    
End Function

Although, I don't need isnull as I will be updating all dates anyway. I have to somehow figure?
1. The form is an unbound pop up form so I don't know how the above will find the query that need updating
2. How to leave the top date as is and only update the rest, Although for this purpose "Now()" would do as the first record.
Her's my query:

SELECT  ItemQuery.ItemDateCreated
FROM ItemQuery
ORDER BY ItemQuery.ItemDateCreated;


Why do I make my life so difficult? Been on this for 4 days!!!!!
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
You'd be better off to add a column called "DisplayOrder" as a long, then sort off that.  Is modifying the table possible?   If not, can you introduce a temp table with two columnes; Copy of the PK field and the DisplayOrder.   Then join to the main table in a query and base the report off that.

Jim.

Author

Commented:
That may be the best solution Jim. If I could get Gustav's solution to work it would be less disruptive because I would still have to re-order the TempTable every time a record gets moved. If I am going to do that then I could do the same with the date field presumably?

If I can figure it out that is

Author

Commented:
I just realised Jim If I had a number field as you suggest I could continue to split that for some time.

Cheers
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
A date/time stamp does not have that resolution.

A Date value can perfectly well hold a millisecond value with a resolution of 1ms or more, in fact for the entire range of the data type.
I even posted a decade ago on Devx an article on this subject which still is online. Jim, you must have seen this several times (actually, it's the first hit if you browse for: gustav milliseconds):

MS Access Can Handle Millisecond Time Values--Really

However, there is no default format option, nor any native functions of VBA to handle milliseconds, so - apart from that article and code - you are on your own.

That noted, puzzling with milliseconds is not the solution here.
What you need is a method to adjust the order the records. A method simple to implement is described in my article:

Sequential Rows in Microsoft Access

Browse for the paragraph:

3. Priority Numbers


Please note, that a full demo is for download.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
I just realised Jim If I had a number field as you suggest I could continue to split that for some time.

No need to split....just reorder the field.   This is what you typically see in these situations (like an invoice or PO).

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
@gustav,

A Date value can perfectly well hold a millisecond value with a resolution of 1ms or more, in fact for the entire range of the data type.

 I didn't download your sample DB, but your article starts off talking about SQL.   Also if you check the docs for the Access date/time type, it says that a valid time value only ranges from .0 (00:00:00) to .99999 (23:59:59).    

  I didn't look at what you did, but I would not work with milliseconds in a date/time field.   Nothing in VBA or Access really supports it.  Everything stops with seconds.   Date/Times are also stored as a double float and decimal precision is not guaranteed, which I'm sure your aware of.   The date/time type probably scales internally to get around that so it can provide accurate values, which is why going outside of the valid range is probably not a good idea.

 To sum it up, finding a way of going around that just seems to be asking for trouble.

Jim.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
The date/time type probably scales internally to get around that

No it doesn't. There are no issues, and it works perfectly well and to the point. In fact, I suspect the Date data type to originally have been designed to hold milliseconds, but the feature was abandoned to simplify handling date and time for the majority of the target group.

So, don't assume anything, indeed not trouble. Take my word for it, or read the article (two pages only) and play with the demo.
I can add, that I recently revamped the code completely to streamline it a bit and bring it to today's standards, but the core remains.
John TsioumprisSoftware & Systems Engineer

Commented:
Well you can do the other way around but using the predefined Access Date/Time datatype won't get you milliseconds
I create a small sample app to check it.
I have a simple form with 2 buttons
1 button fills a table with 50 records of random Now
1 button updates a field to Cdbl(Date) of the field that holds Now.
The internal accuracy is beyond milliseconds that's for sure...its a decimal number with 10 digits after the decimal separator but when you have Date/Time field you can't store/retrieve up to the millisecond
Here is my sample :
Clipboard01.jpgSo probably you could store millisecond accuracy but you have to define your own "datatype"
MilliSeconds.accdb
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Well whatever the case, it's not required for the question at hand.

Jim.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
John, you can't do it that way. Now will always return a value holding zero milliseconds.
Timer  is the only native function that returns split seconds but not with a resolution of one millisecond.
To create determined milliseconds beyond what simple math can do, for example:

HalfSecond = TimeSerial(0, 0, 1) / 2
OneMillisecond = TimeSerial(0, 0, 1) / 1000

Open in new window

custom functions are needed as described in my article.

The resolution in Date is exactly one millisecond at the very extreme values of data type Date. Thus, you can only go beyond one millisecond for very limited ranges of Date. These are exactly:

    ' Interval with minimum one microsecond resolution.
    Public Const MaxMicrosecondDateValue    As Date = #5/18/1927#
    Public Const MinMicrosecondDateValue    As Date = #8/13/1872#
    ' Interval with minimum one nanosecond resolution.
    Public Const MaxNanosecondDateValue As Date = #1/9/1900#
    Public Const MinNanosecondDateValue As Date = #12/20/1899#
    ' Interval with minimum one tick resolution.
    Public Const MaxTickDateValue       As Date = #2:24:00 AM#
    Public Const MinTickDateValue       As Date = -#2:24:00 AM#

Open in new window

Still, as noted, splitting seconds is in no way the solution here.

Author

Commented:
My thanks to you all
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial