Microsft Access Date in Form and SQL Statement.-优快云博客

本文介绍了一个Access程序中遇到的日期格式问题及解决方案。由于FORM中的日期格式为dd-mmm-yy，而在SQLSTATEMENT中需使用mm/dd/yyyy格式，作者通过自定义函数ConvertToSqlDate实现了日期格式的转换，确保了SQL查询的正确执行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

这个ACCESS 程序可以根据旅客入住日期和离开日期取出这段时间旅馆是什么季节,(繁忙季节价格就高点).

FORM里date format 是 6-May-06(dd-mmm-yy). 可进到SQL STATEMENT, 就把5月6号变成 6月5号了. 把月份和日期搞混了.
SQL STATEMENT 里面的日期必须写成06/05/06 (mm/dd/yyyy). 那我就得自己转换日期去合格格式,在送去SQL STATEMENT.

我原来的SQL STATEMENT,
Private Sub cmdRoomRate_Click()
    Dim dbConn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    rs.Open "SELECT TOP 1 RoomSeasonDates.SeasonID FROM RoomSeasonDates WHERE ( [RoomSeasonDates].    [StartDate] <#" & ([Forms]![frmEnquiry]![CheckInDate]) & "#)", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Me!txtSeasonIDCheckINDate = CStr(rs!SeasonID)
    rs.Close
End Sub

红色字是调用FORM上的TEXTBOX, 用户会输入 6-May-06.
我改写上面的代码成
Private Sub cmdRoomRate_Click()
Me!txtSeasonIDCheckINDate = ""
    Dim dbConn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    rs.Open "SELECT TOP 1 RoomSeasonDates.SeasonID FROM RoomSeasonDates WHERE ( [RoomSeasonDates].[StartDate] <" & ConvertToSqlDate([Forms]![frmEnquiry]![CheckInDate]) & ")", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Me!txtSeasonIDCheckINDate = CStr(rs!SeasonID)
    rs.Close
End Sub

Private Function ConvertToSqlDate(ByVal strDate As String) As String
              Dim sqlDate As String
             sqlDate = CStr(Format(CDate(strDate), "mm/dd/yyyy"))
             ConvertToSqlDate = "#" + sqlDate + "#"
End Function

现在OK 啦. 应该还有更规矩的方法,不过暂时就这样吧.