将字符串转换为MS Access查询中的日期 [英] Convert String to Date in MS Access Query

问题描述

我试图从我的访问表中检索数据基于日期列，要求是显示大于某个值的一切

I am tyring to retrieve data from my access table based on Date column, requirement is to display everything greater than certain value

我试图强制转换我的值是使用Format&

I am trying to cast my value which is a string using Format & CDate function with date time type, it throws out as Overflow.

这里是查询：

Select * from Events
Where Events.[Date] > cDate(Format("20130423014854","yyyy-MM-dd hh:mm:ss"))

示例日期记录值来自表：2013-04-23 13：48：54.0

Sample Date Record Value from Table : 2013-04-23 13:48:54.0

事件。[Date]是访问中的日期/时间类型字段

Events.[Date] is a Date/Time type field in access

如何解决这个问题？

推荐答案

$ c>创建>模块并粘贴以下代码

In Access, click Create > Module and paste in the following code

Public Function ConvertMyStringToDateTime(strIn As String) As Date
ConvertMyStringToDateTime = CDate( _
        Mid(strIn, 1, 4) & "-" & Mid(strIn, 5, 2) & "-" & Mid(strIn, 7, 2) & " " & _
        Mid(strIn, 9, 2) & ":" & Mid(strIn, 11, 2) & ":" & Mid(strIn, 13, 2))
End Function

点击 Ctrl + kbd>并将模块保存为 modDateConversion 。

现在尝试使用像

Select * from Events
Where Events.[Date] > ConvertMyStringToDateTime("20130423014854")

---编辑---

避免用户定义的VBA函数的备选解决方案：

Alternative solution avoiding user-defined VBA function:

SELECT * FROM Events
WHERE Format(Events.[Date],'yyyyMMddHhNnSs') > '20130423014854'

这篇关于将字符串转换为MS Access查询中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！