[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.

[Siddharth Rout]

CDate(Month(Date) & "/31/" & Year(Date))

won't work for april as there isn't 31 days in april

try

msgbox CDate(Month(Date) & "/31/" & Year(Date))

and

msgbox CDate(Month(Date) & "/30/" & Year(Date))

to see the error

[Doogle]

April only has 30 days. You're assuming that all months have 31 days. CDate will fail to convert "31/04/2008" with a Type Mismatch"

[Erroneous]

Ok thanks koolsid for pointing out the problem. Is there a general approach to search by month using combobox?

In the form I have an option to use a datepicker and it works with no problem but I also want to incorporate the combobox coz it's much easier than datepickers.

[Erroneous]

Thanks Doodle for pointing out the problem. My approach was wrong. Please read post #4. thanks!

[Erroneous]

No can i do it by using "Today", "Yesterday", "This Week", "This Month", "This Year" on a combobox?

Seems like all of them are working except for MONTH coz date varies from 28,29,30 and 31. Is there any other workaround?

[Siddharth Rout]

Hi it has nothing to do with "Today", "Yesterday", "This Week", "This Month", "This Year" on a combobox. It is ultimately how you are finaly coding your strSQL

alternatively if you want to use that approach then what i can think of is when the user selects "This Month" then there will a code which will check the system time and then calculate total days in that month (say for example it stores it in "NoofDays") and then use it to finaly code the strSQL. say something like


CDate(Month(Date) & "/" & NoofDays & "/" & Year(Date))

Hope this is what you want...


EDIT : AN EXAMPLE
For example this function if placed in a module will give the total number of days in a month

Option Explicit 
 
Public Function NoofDays(myMonth As Long) As Long 
     
    NoofDays = Day(DateSerial(Year(Date), myMonth + 1, 1) - 1) 
     
End Function

Where myMonth is the desired month number.

Example

NoofDays(4) or NoofDays(2) etc...

[Doogle]

Something like this perhaps

Private Function DaysInThisMonth() As Integer
Dim daNow As Date
Dim daFirst As Date
Dim daNext As Date
Dim intDay As Integer
Dim intMonth As Integer
Dim intNextM As Integer
Dim intDiff As Integer
Dim intYear As Integer
Dim intYear1 As Integer
daNow = Now
intMonth = Month(daNow)
intDay = 1
intYear = Year(daNow)
If intMonth = 12 Then
    intNextM = 1
    intYear1 = intYear + 1
Else
    intNextM = intMonth + 1
    intYear1 = intYear
End If
daFirst = CDate(intDay & "/" & intMonth & "/" & intYear)
daNext = CDate(intDay & "/" & intNextM & "/" & intYear1)
DaysInThisMonth = Abs(DateDiff("d", daNext, daFirst))
End Function

[Siddharth Rout]

I have just edited my post with an example on how to calculate number of days in a month...

edit:
@doogle : you beat me to it

however the function that i have is a shorter version...

[Doogle]

Certainly simpler than mine !!

[Erroneous]

Wow great help guys! I'll keep you posted. I don't know the DateSerial Function yet let me check.

[Siddharth Rout]

I don't know the DateSerial Function yet let me check.

Does this help...

http://msdn2.microsoft.com/en-us/lib...0c(VS.80).aspx

[Erroneous]

Ok guys I tested it and it works flawless.

Here is the code. The DateSerial does the trick.

NoOfDays(Month(Date))

strSQL = "SELECT * FROM TABLE WHERE FIELD_DATE BETWEEN '" & _
                       CDate(Month(Date) & "/01/" & Year(Date)) & "' AND '" & _
                       CDate(Month(Date) & "/" & NoOfDays(Month(Date)) & "/" & Year(Date) & ""

Rep Added. Cheers!

[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