[RESOLVED] Search by Month Problem

[anhn]

This is my version:

Function DaysOfMonth(iMonth As Integer, iYear As Integer) As Integer
   '-- note that Feb may have 28 or 29 days that depends on year
   DaysOfMonth = Day(DateSerial(iYear, iMonth + 1, 0))
End Function

Function DaysOfThisMonth() As Integer
   DaysOfThisMonth = Day(DateSerial(Year(Date), Month(Date) + 1, 0))
End Function

Function DaysOfLastMonth() As Integer
   DaysOfLastMonth = Day(Date - Day(Date))
End Function

I don't think we need to know number of days in a month here:

   strSQL = "SELECT * FROM TABLE WHERE "
   Select Case ComboBox.Text
       Case "Today"
            strSQL = strSQL & "FIELD_DATE ='" & Date & "'"
       Case "Yesterday"
            strSQL = strSQL & "FIELD_DATE ='" & (Date - 1) & "'"
       Case "This Week" '--???
            strSQL = strSQL & "FIELD_DATE BETWEEN '" & (Date - 6) & "' AND '" & Date & "'"
       Case "This Month"
            '-- actually we compare the last days of previous months here
            strSQL = strSQL & "(FIELD_DATE - DAY(FIELD_DATE)) = (DATE - DAY(DATE))"
            '-- or
            'strSQL = strSQL & "YEAR(FIELD_DATE) = YEAR(DATE) AND MONTH(FIELD_DATE) = MONTH(DATE)"
       Case "This Year"
            strSQL = strSQL & "YEAR(FIELD_DATE) = YEAR(DATE)"
            '--or:
            'strSQL = strSQL & "YEAR(FIELD_DATE) = " & Year(Date)
   End Select