Microsft Access Date in Form and SQL Statement.

这个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 啦. 应该还有更规矩的方法,不过暂时就这样吧.