[RESOLVED] Search by Month Problem
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.
vb Code:
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. ;)
Re: Search by Month Problem
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
Re: Search by Month Problem
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"
Re: Search by Month Problem
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.
Re: Search by Month Problem
Thanks Doodle for pointing out the problem. My approach was wrong. Please read post #4. thanks!
Re: Search by Month Problem
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?
Re: Search by Month Problem
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
Code:
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...
Re: Search by Month Problem
Something like this perhaps
Code:
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
Re: Search by Month Problem
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 :lol:
however the function that i have is a shorter version... :)
Re: Search by Month Problem
Certainly simpler than mine !! :D
Re: Search by Month Problem
Wow great help guys! I'll keep you posted. I don't know the DateSerial Function yet let me check.
Re: Search by Month Problem
Quote:
Originally Posted by Erroneous
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
Re: Search by Month Problem
Ok guys I tested it and it works flawless.
Here is the code. The DateSerial does the trick.
NoOfDays(Month(Date))
vb Code:
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!
Re: [RESOLVED] Search by Month Problem
This is my version:
Code:
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:
Code:
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