<div>
A date/time stamp does not have that resolution. &nbsp; What are you trying to accomplish?<br />
<br />
Jim.
</div>


<div>
I think that milliseconds cannot be formated...you probably have to format them on your own
</div>


<div>
Hi John I would like to know how to do that but my help file does not give that info.<br />
<br />
Hi Jim Ignore Dates after Type B but I cannot move more than one record between Type A and Type B<br />
<a href="https://filedb.experts-exchange.com/incoming/2019/12_w51/1439088/A1.PNG" target="_blank" title="A1.PNG (9 KB)"><img alt="A1.PNG" class="file-block lazyload" style="max-width: 452px;" data-src="https://filedb.experts-exchange.com/incoming/2019/12_w51/1439088/A1.PNG" /></a><br />
Moving records to different rows in a query. &nbsp;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.<br />
<br />
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:<br />
<br />
Public Function UpdateDates()<br />
&nbsp; &nbsp; Dim rs &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;As DAO.Recordset<br />
&nbsp; &nbsp; Dim LastDate &nbsp; &nbsp;As Date<br />
&nbsp; &nbsp; <br />
&nbsp; &nbsp; Set rs = Me.RecordsetClone<br />
&nbsp; &nbsp; <br />
&nbsp; &nbsp; If rs.RecordCount &gt;&nbsp;0 Then<br />
&nbsp; &nbsp; &nbsp; &nbsp; While Not rs.EOF<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; If IsNull(rs!YourDateField.Va<wbr />lue) Then<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rs.Edit<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rs!YourDateField.Value = DateAdd(&quot;h&quot;, 1, LastDate)<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rs.Update<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; End If<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LastDate = rs!YourDateField.Value<br />
&nbsp; &nbsp; &nbsp; &nbsp; Wend<br />
&nbsp; &nbsp; &nbsp; &nbsp; rs.MoveNext<br />
&nbsp; &nbsp; End If<br />
&nbsp; &nbsp; rs.Close &nbsp; &nbsp;<br />
End Function<br />
<br />
Although, I don't need isnull as I will be updating all dates anyway. I have to somehow figure?<br />
1. The form is an unbound pop up form so I don't know how the above will find the query that need updating<br />
2. How to leave the top date as is and only update the rest, Although for this purpose &quot;Now()&quot; would do as the first record.<br />
Her's my query:<br />
<br />
SELECT &nbsp;ItemQuery.ItemDateCreated<br />
FROM ItemQuery<br />
ORDER BY ItemQuery.ItemDateCreated;<wbr /><br />
<br />
<br />
Why do I make my life so difficult? Been on this for 4 days!!!!!
</div>


A1.PNG

<div>
You'd be better off to add a column called &quot;DisplayOrder&quot; as a long, then sort off that. &nbsp;Is modifying the table possible? &nbsp; If not, can you introduce a temp table with two columnes; Copy of the PK field and the DisplayOrder. &nbsp; Then join to the main table in a query and base the report off that.<br />
<br />
Jim.
</div>


<div>
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?<br />
<br />
If I can figure it out that is
</div>


<div>
I just realised Jim If I had a number field as you suggest I could continue to split that for some time.<br />
<br />
Cheers
</div>


<div>
<blockquote>
A date/time stamp does not have that resolution.
</blockquote>
<br />
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.<br />
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):<br />
<br />
<a href="http://www.devx.com/dbzone/Article/39046" rel="ugc">MS Access Can Handle Millisecond Time Values--Really</a><br />
<br />
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.<br />
<br />
That noted, <b>puzzling with milliseconds is not the solution</b> here. <br />
What you need is a method to adjust the order the records. A method simple to implement is described in my article:<br />
<br />
<a href="https://www.experts-exchange.com/articles/33069/Sequential-Rows-in-Microsoft-Access.html" rel="ugc">Sequential Rows in Microsoft Access</a><br />
<br />
Browse for the paragraph:<br />
<br />

<h2>3. Priority Numbers</h2>
<br />
Please note, that a full demo is for download.
</div>


<div>
<blockquote>
I just realised Jim If I had a number field as you suggest I could continue to split that for some time.
</blockquote>
<br />
No need to split....just reorder the field. &nbsp; This is what you typically see in these situations (like an invoice or PO).<br />
<br />
Jim.
</div>


<div>
@gustav,<br />
<br />

<blockquote>
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.
</blockquote>
<br />
&nbsp;I didn't download your sample DB, but your article starts off talking about SQL. &nbsp; 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). &nbsp; &nbsp;<br />
<br />
&nbsp; I didn't look at what you did, but I would not work with milliseconds in a date/time field. &nbsp; Nothing in VBA or Access really supports it. &nbsp;Everything stops with seconds. &nbsp; Date/Times are also stored as a double float and decimal precision is not guaranteed, which I'm sure your aware of. &nbsp; 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.<br />
<br />
&nbsp;To sum it up, finding a way of going around that just seems to be asking for trouble.<br />
<br />
Jim.
</div>


Derek Brown

<div>
Well you can do the other way around but using the predefined Access Date/Time datatype won't get you milliseconds<br />
I create a small sample app to check it.<br />
I have a simple form with 2 buttons<br />
1 button fills a table with 50 records of random Now <br />
1 button updates a field to Cdbl(Date) of the field that holds Now.<br />
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<br />
Here is my sample :<br />
<a href="https://filedb.experts-exchange.com/incoming/2019/12_w51/1439134/Clipboard01.jpg" target="_blank" title="Clipboard01.jpg (100 KB)"><img alt="Clipboard01.jpg" class="file-block lazyload" style="max-width: 410px;" data-src="https://filedb.experts-exchange.com/incoming/2019/12_w51/1439134/Clipboard01.jpg" /></a>So probably you could store millisecond accuracy but you have to define your own &quot;datatype&quot;<br />
<a href="https://filedb.experts-exchange.com/incoming/2019/12_w51/1439133/MilliSeconds.accdb" target="_blank" class="file-inline" title="MilliSeconds.accdb (328 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>MilliSeconds.accdb</a>
</div>


MilliSeconds.accdb

Clipboard01.jpg

<div>
Well whatever the case, it's not required for the question at hand.<br />
<br />
Jim.
</div>


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

<pre><code class="code-1 nolinks" id="code-20-42998234-1"><span>HalfSecond = TimeSerial(0, 0, 1) / 2</span>
<span>OneMillisecond = TimeSerial(0, 0, 1) / 1000</span>
</code></pre>
custom functions are needed as described in my article.<br />
<br />
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:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42998234-2"><span>    ' Interval with minimum one microsecond resolution.</span>
<span>    Public Const MaxMicrosecondDateValue    As Date = #5/18/1927#</span>
<span>    Public Const MinMicrosecondDateValue    As Date = #8/13/1872#</span>
<span>    ' Interval with minimum one nanosecond resolution.</span>
<span>    Public Const MaxNanosecondDateValue As Date = #1/9/1900#</span>
<span>    Public Const MinNanosecondDateValue As Date = #12/20/1899#</span>
<span>    ' Interval with minimum one tick resolution.</span>
<span>    Public Const MaxTickDateValue       As Date = #2:24:00 AM#</span>
<span>    Public Const MinTickDateValue       As Date = -#2:24:00 AM#</span>
</code></pre>
Still, as noted, splitting seconds is in no way the solution here.
</div>


<div>
<div class="content wysiwyg-content">
What is the format in a query grid that will show milliseconds?
</div>
</div>


What is the format in a query grid that will show milliseconds?

format milliseconds?

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.