[AccessD] Losing 3ms on date insert?

Tue Nov 27 05:32:07 CST 2018

Gustav, 

Thanks for the correction - 

And as per my earlier "Thanks" to Stuart,
Avoiding the Date & time storage/manipulation process within VBA was - way back when, AFAIK the best approach if you wanted more accuracy than seconds.

 Also thanks for maintaining the online docs that expand on, or at least make details of MS workings more obvious to us simple techies.

JimB

-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Gustav Brock
Sent: Tuesday, November 27, 2018 10:18 AM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Losing 3ms on date insert?

Hi James

I think you are mixing some things up.

VBA uses the same data types throughout the Office applications, thus dates and their numeric values are identical.

Milliseconds can be held within the entire range of the data type. But (refer to the link provided by Stuart) when displayed natively in Access, milliseconds are rounded to the nearest second.

As the resolution around numeric zero (1899-12-30 00:00:00) is much higher than at the extreme values, also microseconds and nanoseconds - even ticks - can be held, though within still tighter limits, of course:

    ' 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#

That said, you can't apply calculations on subsecond values reliably using the date values only, Double as they are behind the scene, because of the resulting floating point errors. Thus, you must convert to Decimal (as demonstrated in my previously posted Millisecond function) to avoid these errors.

As to your listed format strings, these don't work:

    oooo means monthname in the local format 
    aaaa  means weekday in local format

The first, I have already got pulled from the docs, but I missed the second. That's done now:

    https://github.com/MicrosoftDocs/VBA-Docs/pull/496

Note, that anyone with a GitHub account can contribute to the docs, now they are hosted at GitHub.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD <accessd-bounces at databaseadvisors.com> På vegne af James Button via AccessD
Sendt: 27. november 2018 01:35
Til: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com>
Cc: James Button <jamesbutton at blueyonder.co.uk>
Emne: Re: [AccessD] Losing 3ms on date insert?

Firstly - Gravitate how? - well turn your screen on it's side to avoid that effect of gravitation
(snigger) 

However some of the following may be also associated with the problem - 

AFAIK 

VBA times are stored with less accuracy than Excel So it could be that the value is being truncated within the transmission from app to app 

IN VBA a date is an 8 bit floating point value as in  integer day and fractional hh:mm:ss  

Note   NO MILLISECONDS

but with VBA you get dates before December 30, 1899 using negative integers Standards (Ha! - ha ha ha ) for PC hardware is usually that numbers are stored to 15 significant digits  with about 300 "0"'s between the decimal point and that (up to 16) digit string.

In VBA there is CURRENCY datatype  that stores a value of up to 15 digits before the decimal point and up to 4 after it . 
Decimal is even more fun - as in different from Excel as Decimal allows up to 28 decimal places with the decimal place anywhere within those 28
BUT you have to use the CDEC function to convert the value into a 12byte storage block that is actually held under the Variant DIIM structure!

Also remember Boolean conversion from/to numeric equivalent is different in VBA and Excel.
In VBA there are
Integer from -32,768 up to +32,767 
Long which is -2,147,483,648 up to +2,147,483,647
Double -1.79769313486231E308 up to +1.79769313486232E308
And - for those with 64 bit OS and applications 
There is the LongLong VBA data type for 18, well, almost 19 digit numbers from -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.

Also re accuracy of hundreds of digits  
Way back before PC's, there were  machines that did not have storage arranged in 'words' of 4 8 or 16 bytes with each byte being used to hold an 8 bit character, 
But the systems memory was in individual 7 bit characters with extra bits associated with each character (1 or 2 of them) used to define the end of a 'word' or an 'item' or a 'record'
So addition and subtraction  was done on a 'character' mode working along the digits until a word ending bit was found, so a number could be almost all of memory (H200 systems)
There were also systems with 36 and systems with 48 bit words - just to really annoy those doing conversions to newer (and different manufacturer's) hardware.
Even more fun when a manufacturer moved their hardware from 7 bit data storage to 9 bit (e.g. mag-tapes) so the replacement hardware would not run the drives that read the old data storage media 
Hey - seen a 5¼ floppy disk recently, let alone an 8" or 11" one ?
OK - how about a 3" one?

And - NOT MANY PPEOPLE KNOW THIS 
Within VBA - Date formats 
c         means date (if value >=1) and then hh:mm:ss if a fractional part
oooo means monthname in the local format 
aaaa  means weekday in local format
w means weekday (Sunday is day 1)
ww is week of the year 
m is month, or if in {} or following h then minutes
n is minutes  - so you don't need to use m
q means quarter of the year
y means day of the year

and text  
one or more @ characters means the text string at this place in the string  and only as many characters as there are @'s
one or more & characters means the character at this place in the string  and only as many characters as there are &'s 
The output will be the exact number of & or @ in the string with the source characters dropped where format string includes spaces in a string of @ or &
spaces in an @ string meaning a space in the output and a space in an & string means closeup the result.

Prefix markers in the format string can be 
> means output uppercase
<means output lowercase
! means match to @ or & string from the left of the source rather than from the right
; as a suffix means that the following characters are to be used if the entry is void/null  or zero 
You can also use " bounded literals with @ or & or unbounded characters each preceded with a \ so the interpreter knows they are not formatting control 

And for those confused about log tables - the answer is as many as you want to pay for, 
Or if generating them yourself, It's up to you, your calculations, and your calculator's capability 
Or - the size of your slipstick and how good your vision was for aligning the marked values ! 

JimB 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com