Access & VBA 今日のトリビアCDate関数で0.99999421296296292506を変換すると日付になる時がある

1日は何秒か

1日は何秒か
$$1 \times 24 \times 60 \times 60 = 86,400$$

小数で表すには最低何桁必要か

整数位を日付とすると、時間は小数点以下で表される。
1秒を表すには正確ではないにせよ、最低何桁必要なのか。
これは86400が5桁であることから、5桁だとわかる。
実際に1日を86,400で割ってみよう。
$$1 \div 86400 = 0.000011574074074$$
5桁目に初めて1が現れる。このことから、1秒を表すには、やはり最低5桁は必要だとわかりる。

23:59:59を十進数で表現する

この値は

1. 1秒の値を積み上げて23:59:59を求める。
2. 1から1秒の値を引いて 23:59:59を求める。

という考え方ができるだろう。ここでは2を採用する。
$$1 - 0.000011574074074 = -0.9999884259259259$$

公式の説明がおかしい

ここまでの知識を前提に、公式の解説をみてみる。

その1

日付と時刻のフィールドを書式設定する

Access では、日付と時刻の書式設定に関連する特定のレベルのデータの入力規則が自動的に提供されます。 たとえば、32.11.2018 などの無効な日付を入力すると、メッセージが表示され、新しい値を入力するか、日付/時刻データ型のフィールドをテキスト データ型に変換することができます。 有効な日付値の範囲は、-657,434 (A.D. 100 年 1 月 1 日) から 2,958,465 (9999 年 12 月 31 日 A.D.) です。 有効な時刻値は、0 から 0.9999 または 23:59:59 の範囲です。

この説明はおかしいことがわかる。0.9999は4桁しかない。これでは1秒を表せず、さらに23:59:59を表すには足りない。
$$0.9999-0.9999884259259259=0.0000884259259259$$
実際にAccess、Excel等を立ち上げ、VBEのイミディエイトウィンドウでみてみる。

?Cdate(0.9999)
23:59:51 

なんと8秒も足りないことになる。
そこで、5桁目を増やす。
それでも無理なので6桁目までふやす。

?cdate(0.999988)
23:59:59

このように、実は公式の説明は違っている。ところがもう一つの公式の説明がある。

その2

拡張した日付/時刻データ型の使用

日付/時刻データ型と日付/時刻拡張データ型を比較する
次の表は、2 つのデータ型の重要な違いをまとめたものです。

属性	日付/時刻型	日付/時刻拡張型
最小値	100-01-01 00:00:00	0001-01-01 00:00:00
最大値	9999-12-31 23:59:59.999	9999-12-31 23:59:59.9999999
精度	0.001 秒	1 ナノ秒
サイズ	倍精度浮動小数点	42 バイトのエンコードされた文字列

なんとここでは0.001秒の精度となっている。とても0.9999のような4桁で収まらない。
もう一度最初から考えてみよう。
まず1秒を表すには6桁必要だった。
0.001秒の精度を実現するなら、さらに3桁、つまり小数点9桁まで必要になる。
日付拡張型も文字列というのがポイントで、あまり計算には向いていない。

値を増やすと・・・

これはOS、Office、バージョンや機種にもよると思われる。、

?cdate(csng(0.999994))
23:59:59
?cdate(0.999994)
23:59:59

0.999995

?cdate(csng(0.999995))
1899/12/31 
?cdate(0.999995)
1899/12/31 

と日付になる。

どこまで細かくできるか

?cdate(cdate(0.99999421296296292505))

このように20桁まで識別した。末尾の5を6に変えると1899/12/31に変わる。
これなら0.001秒の精度を持っているという説明は本当なのか？
ただ、これはAccess2021相当のバージョンだろう。これでは有効数字が15桁を超えているからである。

推理 0.99999421296296292506は0となるから。

CDATEで時間を変換すると24時になると0に戻る。
1899/12/31 はVBAでは0日となっている。
よって24時になって0になり、日付に変わると言うことだろう。
問題なのは84600にならなくても0になっているという点である。これは23:59:59までは正確に計算しているという意味らしい。
また24時で0になるため時間の積算ができないというのもあるが、

$84,600 \times 0.99999421296296292506 = 84,599.510416666663460076$
$84,600 \times 0.99999421296296292505 = 84,599.51041666666345923$

計算してみると上のようになった。つまり0.5秒刻みまでは正確になるということらしい。
84，599.5を超えると0になるので、これ以上は細かくできないからだ。
本来なら84,399.9999までは分解できるはず。

分数ならどうか

$1\div86399.99999999999 = 1\div86399.99999999998$

?1/86399.99999999999 = 1/86399.99999999998
True

小数点10位、つまり15桁までは分解している。

?cdate(1/86399.9999999999) = cdate(1/86399.9999999998)
False

このように分数表示にすればCdate関数も0.001秒単位まで判定しているように見える。

しかし

それではCDateで変換してみる

Sub cdatetest()
Debug.Print CDec(1 / 86400 * 86399.999)
Debug.Print CDate(CDec(1 / 86400 * 86399.999))
End Sub

0.999999988425926 
1899/12/31 

?cdate(1/86399.9999999999)
0:00:01 

やはりCdateに入れるとダメである。
本来ここまで判別できそうだが。

0.001秒を表示できるか

Accessのクエリ

この秒以下に関するカスタム形式はない。
0.001秒の精度を誇る割に、それを表示する手段は用意されていない。
自分でやるしかない。
秒単位（1日=86400）になっている時間を時（24時間超えアリ）分、秒、小数点以下にする。
ただし返り値は文字列型である。

Type CustomTime
    Year As Integer
    Month As Integer
    DayOfeek  As Integer
    Day  As Long
    Hour As Long
    Minute As Integer
    Second As Integer
    Milliseconds As String
End Type
Function fnSecToIntegrationHoursUnit(val) As String
' Neet Type Constractor
' Second Unit Time to Acculate Hours minutes seconds and
' Ex 86,399.99999 -> 23:59:59.9833
' Note:Test Version Result Not Accuracy
Const DSec = 86400
Dim i, i2
If IsNumeric(val) = False Then fnSecToIntegrationHoursUnit = 0: Exit Function
i = val
Dim iHour As Long
Dim iMinue As Long
Dim DTval As CustomTime
If i > DSec Then
DTval.Day = Int(i / DSec)
i2 = i - (DTval.Day * DSec)
Else
i2 = i
End If
If Int(i2 / (60# * 60#)) >= 0 Then
DTval.Hour = Int(i2 / (3600))
i2 = i2 - (DTval.Hour * 3600)
Else
fnSecToIntegrationHoursUnit = DTval.Day * 24 + DTval.Hour & ":" & Format(DTval.Minute, "00") & ":" & Format(DTval.Second, "00") & "." & Format(DTval.Milliseconds, "00000")
Exit Function
End If
If i2 >= 0 Then
DTval.Minute = Int(CDec(i2 / 60))
i2 = i2 - (DTval.Minute * 60)
Else
fnSecToIntegrationHoursUnit = DTval.Day * 24 + DTval.Hour & ":" & Format(DTval.Minute, "00") & ":" & Format(DTval.Second, "00") & "." & Format(DTval.Milliseconds, "00000")
Exit Function
End If
If i2 >= 0 Then
DTval.Second = Int(i2)
i2 = CDec(i2 - DTval.Second)
Else
fnSecToIntegrationHoursUnit = DTval.Day * 24 + DTval.Hour & ":" & Format(DTval.Minute, "00") & ":" & Format(DTval.Second, "00") & "." & Format(CDec(DTval.Milliseconds), "00000")
Exit Function
End If
If i2 >= 0 Then
DTval.Milliseconds = Replace(CStr(CDec(i2)), "0.", "", 1, -1)
Else
fnSecToIntegrationHoursUnit = DTval.Day * 24 + DTval.Hour & ":" & Format(DTval.Minute, "00") & ":" & Format(DTval.Second, "00") & "." & DTval.Milliseconds
Exit Function
End If
fnSecToIntegrationHoursUnit = DTval.Day * 24 + DTval.Hour & ":" & Format(DTval.Minute, "00") & ":" & Format(DTval.Second, "00") & "." & DTval.Milliseconds ' & DTval.Milliseconds
End Function

Excel(2021相当以降)はWorksheet関数で解決できそうだが欠点がありVBAでは使えない

=TEXT(TIMEVALUE("23:59:59.999"),"HH:mm:ss.000")
Excelの場合、Worksheet関数のTIMEVALUE関数3桁までは変換できる。
このときText関数は.000と書いている。カスタム書式は0`だということになる。
しかし、このWorksheetFunctionはVBAでは使用できない。
またVBAのTimevalue関数とも違っている。
更にこの関数は23:59:59まで。これを超えるとエラーになる。
0.99988426 とは23:59:59ジャストなのである。

TIMEVALUE 関数

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 ここでは、Microsoft Excel の TIMEVALUE 関数の書式および使用法について説明します。

説明

文字列で表された時刻を小数に変換します。 この小数は 0 ～ 0.99988426 の範囲内の値であり、0:00:0 (午前 00 時) から 23:59:59 (午後 11 時 59 分 59 秒) までの時刻を表します。

書式

TIMEVALUE(Time_text)
TIMEVALUE 関数の書式には、次の引数があります。
Time_text    必ず指定します。 任意の時刻形式の時刻を表Microsoft Excel文字列。たとえば、時刻を表す引用符内の "6:45 PM" や "18:45" のテキスト文字列などです。

解説

時刻文字列に含まれる日付の情報は無視されます。
時刻は、日付を構成する値として小数で表されます。たとえば、12:00 PM は半日なので、0.5 と表されます。

TimeValue 関数

時刻が含まれる バリアント (Variant) 型 (日付 (Date) 型) の値を返します。

構文

TimeValue(time)
time 必須　引数は、通常、0:00:00 (12:00:00 A.M.) から 23:59:59 (11:59:59 P.M.) の時間を表す文字列式です。 ただし、time には、その範囲の時刻を表す任意の 式を指定することもできます。 time に Null が含まれている場合は、Null が返されます。

注釈

有効な時刻は、12 時間または 24 時間のクロックを使用して入力できます。 たとえば、両方とも "2:24PM" "14:24" 有効な 時間 引数です。
time 引数に日付情報が含まれていても、TimeValue では返されません。 ただし、time に無効な日付情報が含まれている場合は、エラーが発生します。

Accessのフィールドは対応していないので新しいフィールド型が追加

日付と時刻のフィールドを書式設定する

ユーザー設定の書式文字
ユーザー設定の書式を作成するには、プレースホルダーと区切り文字として次の文字を使用します。 Windows の地域設定で指定された日時設定と矛盾するカスタム書式は無視されます。 詳細については、「Windows の地域設定を変更して、一部のデータ型の 外観を変更する」を参照してください。
注意   日付/時刻拡張データ型の nanosecond 部分にカスタム書式文字はありません。

新しいフィールド型

拡張した日付/時刻データ型の使用
:::note warning
警告 Access で式を作成し、日付/時刻拡張データ型に基づいて日付/時刻関数を使用すると、計算の精度が失われるか、結果に他の問題が発生する可能性があります。 Microsoft はこの問題を認識しており、今後のリリースでは、式と関数をより適切にサポートする予定です。 回避策として、パススルー クエリを作成して、同等の SQL Server 式と日付/時刻関数を使用できます。 詳細については、「Access SQL と SQL Server TSQL の比較」を参照してください。
:::

ただし例によって使っている人はいない

これはSQL-Serverから持ってきたような機能であるが、そもそもミリやナノは演算誤差から来ているのであって、実際は必要性が低い。
次に、使う人は演算誤差を知っているので、ミリ秒は別のフィールドに設ける。100分の1秒のデータを扱いたい .Net
実際に必要な人が使わないのだから実例があまりない。

1899/12/31にしないことはできるか

テキスト型にはなるが、Format関数を使い、Format(Cdate(value),"HH:mm:ss")とすると良い。また、Accessのクエリではフィールドの書式を指定すると良い。

まとめ

Cdate関数は0.99999421296296292505までは23:59:59に変換できるが、これを超えると1899/12/31に変わるときがある。このため00:00:00には戻るがFormat関数で書式を指定した方が良い。
Accessのクエリ及びVBAはミリ秒に精度はあっても標準では対応していない。
Excel2021、Access2021のバージョンは対応しているが完全ではない。
とくに23:59:59のあとが危ない。
結局のところ、関数を自分で作るしかない。
これも演算誤差が絡む。

関連

100分の1秒のデータを扱いたい .Net
(https://learn.microsoft.com/ja-jp/dotnet/standard/base-types/how-to-display-milliseconds-in-date-and-time-values)
.NETの場合ミリセカントのカスタム書式はfff
【ACCESS/EXCEL/VBA】現在時刻ミリ秒まで取得を行う
これは現在時刻の取得
VBAで現在日時をミリ秒単位で取得する
SQL Server Native Clientの拡張日時機能を使用する (OLE DB)