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