[RESOLVED] Search by Month Problem

[Erroneous]

I have a bit of a problem in date ranges. Im using SQL Server 2k and VB6 frontend.

I have a form with Msflexgrid populated from database. My problem is searchin by Month.

Here is my code for the combobox. Please take a look at view by month coz im having error to that.

Select Case ComboBox.Text
        Case "Today"
        
              'VIEW TODAY'S RECORDS
              strSQL = "SELECT * FROM TABLE WHERE FIELD_DATE ='" & Date & "'"
              
        Case "Yesterday"
              'VIEW YESTERDAY'S RECORDS
              strSQL = "SELECT * FROM TABLE WHERE FIELD_DATE ='" & Date - 1 & "'"
        
        Case "This Week"
              'VIEW THIS WEEK'S RECORDS
              strSQL = "SELECT * FROM TABLE WHERE FIELD_DATE BETWEEN '" & _
                        Date - 7 & " ' AND '" & Date & "'"
              
        Case "This Month"
              'VIEW THIS MONTH'S RECORDS
              'IM GETTING AN ERROR HERE
              strSQL = "SELECT * FROM TABLE WHERE FIELD_DATE BETWEEN '" & _
                       CDate(Month(Date) & "/01/" & Year(Date)) & "' AND '" & _
                       CDate(Month(Date) & "/31/" & Year(Date)) & ""
              
        Case "This Year"
              'VIEW THIS YEAR'S RECORDS
              strSQL = "SELECT * FROM TABLE WHERE FIELD_DATE BETWEEN '" & _
                       CDate("1/01/" & Year(Date)) & "' AND '" & _
                       CDate("12/31/" & Year(Date)) & ""
    End Select

Is there any other approach to do this. I maybe doing this wrong. Thanks in anticipation.