毫秒时间:按日期筛选形式 [英] Millisecond time: Filter form by date
问题描述
我想实现毫秒时间戳在访问十三分之二千零一十使用此方法; 的MS Access可以处理毫秒时间值 - - 真的在查看更多:
毫秒值由查询;
SELECT DateValueMsec([DateTimeMs])作为DateOnly FROM
- 提供一个日期只控制从一个文本框的形式进行排序。
在DateOnly编程应用的任何过滤器yeilds 0的结果。
私人小组BuildFilter()
昏暗strFilter的作为字符串
昏暗的CTL作为控制
strFilter的=
添加选定值的字符串
对于每个CTL在Me.FormHeader.Controls
随着CTL
如果.ControlType = acTextBox或者.ControlType = acComboBox然后
如果NZ(.value的)LT;> 然后
如果INSTR(.name和日期)<> 0然后
如果NZ(起始日期)<> 和NZ(结束日期)<> 还有INSTR(strFilter的,DateOnly)= 0然后
strFilter的= strFilter的&放大器; [DateOnly] BETWEEN#&放大器; Me.StartDate.Value和放大器; #与#与& Me.EndDate.Value和放大器; # 和
elseif的NZ(起始日期)<> 还有INSTR(strFilter的,DateOnly)= 0然后
strFilter的= strFilter的&放大器; [DateOnly]≥=#与& DateValueMsec(Me.StartDate.Value)及# 和
strFilter的= strFilter的&放大器; [DateOnly]≥=#与& Me.StartDate.Value和放大器; # 和
elseif的NZ(结束日期)<> 还有INSTR(strFilter的,DateOnly)= 0然后
strFilter的= strFilter的&放大器; [DateOnly]< =#&放大器; Me.EndDate.Value和放大器; # 和
结束如果
elseif的INSTR(.name和ID)<> 0然后
strFilter的= strFilter的&放大器; [&放大器; 。名称和放大器; ] =&放大器; .value的&放大器; 和
其他
strFilter的= strFilter的&放大器; [&放大器; 。名称和放大器; ] ='&放大器; .value的&放大器; ' 和
结束如果
结束如果
结束如果
结束与
接下来CTL
修剪尾随
strFilter的= TrimR(strFilter的,5)
Debug.Print strFilter的
随着Me.subfrmzzAuditTrailDisplay
.Form.Filter = strFilter的
.Form.FilterOn = TRUE
结束与
结束小组
答案!从@pathDongle
时间存储为毫秒UTC;
!DateTimeMS = GetTimeUTC()
和通过恢复;
公共功能UTCtoTimeLocal(dSysUTC为日期)作为日期
昏暗SYSTIME作为SYSTEMTIME
昏暗DST只要
昏暗的奥兹作为TIME_ZONE_INFORMATION
DST = GetTimeZoneInformation(TZI)
UTCtoTimeLocal = dSysUTC - TimeSerial的(0,tzi.Bias,0)+ IIF(DST = 2,TimeSerial的(1,0,0),0)
端功能
查询;
SELECT tblzzAuditTrail.DateTimeMS,FormatDate(UTCtoTimeLocal([DateTimeMS]))AS DateTimeLocal
这可以被过滤在为一个字符串。
私人小组BuildFilter()
昏暗strFilter的作为字符串
昏暗的CTL作为控制
strFilter的=
添加选定值的字符串
对于每个CTL在Me.FormHeader.Controls
随着CTL
如果.ControlType = acTextBox或者.ControlType = acComboBox然后
如果NZ(.value的)LT;> 然后
如果INSTR(.name和日期)<> 0然后
如果NZ(起始日期)<> 和NZ(结束日期)<> 还有INSTR(strFilter的,DateTimeLocal)= 0然后
strFilter的= strFilter的&放大器; [DateTimeLocal] BETWEEN'&放大器; FormatDate(Me.StartDate.Value)及AND与& FormatDate(Me.EndDate.Value)及' 和
elseif的NZ(起始日期)<> 还有INSTR(strFilter的,DateTimeLocal)= 0然后
strFilter的= strFilter的&放大器; [DateTimeLocal]≥与& FormatDate(Me.StartDate.Value)及' 和
elseif的NZ(结束日期)<> 还有INSTR(strFilter的,DateTimeLocal)= 0然后
strFilter的= strFilter的&放大器; [DateTimeLocal]< ='&放大器; FormatDate(Me.EndDate.Value)及' 和
结束如果
elseif的INSTR(.name和ID)<> 0然后
strFilter的= strFilter的&放大器; [&放大器; 。名称和放大器; ] =&放大器; .value的&放大器; 和
其他
strFilter的= strFilter的&放大器; [&放大器; 。名称和放大器; ] ='&放大器; .value的&放大器; ' 和
结束如果
结束如果
结束如果
结束与
接下来CTL
修剪尾随而
strFilter的= TrimR(strFilter的,5)
Debug.Print strFilter的
随着Me.subfrmzzAuditTrailDisplay
.Form.Filter = strFilter的
.Form.FilterOn = TRUE
结束与
结束小组
结果筛选器字符串;
[用户名] = 2,[DateTimeLocal] 06间/ 01/2015年00:00:00.000'和'07 / 01/2015年00:00:00.000
根据我的其他问题;
<一个href="http://stackoverflow.com/questions/27816493/millisecond-time-msec2-incorrect-return">millisecond-time-msec2-incorrect-return
I am trying to implement millisecond timestamping in Access 2010/13 using this method; MS Access Can Handle Millisecond Time Values--Really - See more at:
The Millisecond value is queried by;
SELECT DateValueMsec([DateTimeMs]) AS DateOnly FROM
- to provide a date only control to sort the form from a textbox.
Any filter applied programmatically on DateOnly yeilds 0 results.
Private Sub BuildFilter()
Dim strFilter As String
Dim ctl As Control
strFilter = ""
'add selected values to string
For Each ctl In Me.FormHeader.Controls
With ctl
If .ControlType = acTextBox Or .ControlType = acComboBox Then
If Nz(.Value) <> "" Then
If InStr(.Name, "Date") <> 0 Then
If Nz(StartDate) <> "" And Nz(EndDate) <> "" And InStr(strFilter, "DateOnly") = 0 Then
strFilter = strFilter & "[DateOnly] BETWEEN #" & Me.StartDate.Value & "# AND #" & Me.EndDate.Value & "# AND "
ElseIf Nz(StartDate) <> "" And InStr(strFilter, "DateOnly") = 0 Then
strFilter = strFilter & "[DateOnly] >= #" & DateValueMsec(Me.StartDate.Value) & "# AND "
' strFilter = strFilter & "[DateOnly] >= #" & Me.StartDate.Value & "# AND "
ElseIf Nz(EndDate) <> "" And InStr(strFilter, "DateOnly") = 0 Then
strFilter = strFilter & "[DateOnly] <= #" & Me.EndDate.Value & "# AND "
End If
ElseIf InStr(.Name, "ID") <> 0 Then
strFilter = strFilter & "[" & .Name & "] = " & .Value & " AND "
Else
strFilter = strFilter & "[" & .Name & "] = '" & .Value & "' AND "
End If
End If
End If
End With
Next ctl
'trim trailing
strFilter = TrimR(strFilter, 5)
Debug.Print strFilter
With Me.subfrmzzAuditTrailDisplay
.Form.Filter = strFilter
.Form.FilterOn = True
End With
End Sub
Answer! From @pathDongle
Time is stored as Millisecond UTC;
!DateTimeMS = GetTimeUTC()
And restored by;
Public Function UTCtoTimeLocal(dSysUTC As Date) As Date
'Dim sysTime As SYSTEMTIME
Dim DST As Long
Dim tzi As TIME_ZONE_INFORMATION
DST = GetTimeZoneInformation(tzi)
UTCtoTimeLocal = dSysUTC - TimeSerial(0, tzi.Bias, 0) + IIf(DST = 2, TimeSerial(1, 0, 0), 0)
End Function
Query;
SELECT tblzzAuditTrail.DateTimeMS, FormatDate(UTCtoTimeLocal([DateTimeMS])) AS DateTimeLocal
Which can be filtered on as a String.
Private Sub BuildFilter()
Dim strFilter As String
Dim ctl As Control
strFilter = ""
'add selected values to string
For Each ctl In Me.FormHeader.Controls
With ctl
If .ControlType = acTextBox Or .ControlType = acComboBox Then
If Nz(.Value) <> "" Then
If InStr(.Name, "Date") <> 0 Then
If Nz(StartDate) <> "" And Nz(EndDate) <> "" And InStr(strFilter, "DateTimeLocal") = 0 Then
strFilter = strFilter & "[DateTimeLocal] BETWEEN '" & FormatDate(Me.StartDate.Value) & "' AND '" & FormatDate(Me.EndDate.Value) & "' AND "
ElseIf Nz(StartDate) <> "" And InStr(strFilter, "DateTimeLocal") = 0 Then
strFilter = strFilter & "[DateTimeLocal] > '" & FormatDate(Me.StartDate.Value) & "' AND "
ElseIf Nz(EndDate) <> "" And InStr(strFilter, "DateTimeLocal") = 0 Then
strFilter = strFilter & "[DateTimeLocal] <= '" & FormatDate(Me.EndDate.Value) & "' AND "
End If
ElseIf InStr(.Name, "ID") <> 0 Then
strFilter = strFilter & "[" & .Name & "] = " & .Value & " AND "
Else
strFilter = strFilter & "[" & .Name & "] = '" & .Value & "' AND "
End If
End If
End If
End With
Next ctl
'trim trailing And
strFilter = TrimR(strFilter, 5)
Debug.Print strFilter
With Me.subfrmzzAuditTrailDisplay
.Form.Filter = strFilter
.Form.FilterOn = True
End With
End Sub
Resulting Filter String;
[UserID] = 2 AND [DateTimeLocal] BETWEEN '06/01/2015 00:00:00.000' AND '07/01/2015 00:00:00.000'
As per my other question;
millisecond-time-msec2-incorrect-return
这篇关于毫秒时间:按日期筛选形式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!