G
Guest
Hi all,
Alright, I've found lots of examples regarding how to find dates within a
date range, but only if the date range is a date. Let me explain.
I have a group of textboxes (with spinners) that allow the user to select a
week or month and year range.
So they could search for everything between months 1-4/2005 or weeks
48-51/2005.
But I can't find a way to get those values formatted properly to allow
comparison. I've tried Format(...) and # but nothing seems to work.
So can anyone point out a way to convert 4 values (from/to Month/Week, and
from/to Year) into a format that I can use to check the date range.
I don't need a specific day within the month/week, that's why I didn't just
use a date picker control.
The function I'm using is below. I've created a separate one for the weeks,
but it looks the same, except for where I format the input. And I'm
basically stuck on the same aspect of the code in both functions.
My previous solution was working great until I tried a search spanning a
year. That's when I noticed I needed a different way to check the date
range, and where I'm stuck.
THE FUNCTION:
Public Function ConstructSQLStmtForMonthRange2(flagNoPreviousCondition As
Boolean, _
tableName As String, columnName As String, _
fromMonth As Integer, toMonth As Integer, _
fromYear As Integer, toYear As Integer, _
allDates As Boolean) As String
Dim returnSQLStmt As String ' String representing the SQL statement
that will be returned
' if the user did NOT check the 'All Dates' box
If allDates = False Then
' if there is NO previous condition, start SQL statement with "WHERE"
If flagNoPreviousCondition = True Then
returnSQLStmt = "WHERE " & tableName & "." & columnName & "
BETWEEN " & fromMonth & fromYear & " AND " & toMonth * toYear & " "
' if there are IS a previous condition, continue SQL statement with
"AND"
Else
returnSQLStmt = "AND " & tableName & "." & columnName & "
BETWEEN " & fromMonth & fromYear & " AND " & toMonth & toYear & " "
End If
End If
ConstructSQLStmtForMonthRange = returnSQLStmt
End Function
The specific code I need to change is:
"WHERE " & tableName & "." & columnName & " BETWEEN " & fromMonth & fromYear
& " AND " & toMonth * toYear & " "
The stuff after BETWEEN needs a proper way to be formatted, but I'm at a loss.
Any help is appreciated! And if I've done something stupid, point it out.
Thanks,
Jay
Alright, I've found lots of examples regarding how to find dates within a
date range, but only if the date range is a date. Let me explain.
I have a group of textboxes (with spinners) that allow the user to select a
week or month and year range.
So they could search for everything between months 1-4/2005 or weeks
48-51/2005.
But I can't find a way to get those values formatted properly to allow
comparison. I've tried Format(...) and # but nothing seems to work.
So can anyone point out a way to convert 4 values (from/to Month/Week, and
from/to Year) into a format that I can use to check the date range.
I don't need a specific day within the month/week, that's why I didn't just
use a date picker control.
The function I'm using is below. I've created a separate one for the weeks,
but it looks the same, except for where I format the input. And I'm
basically stuck on the same aspect of the code in both functions.
My previous solution was working great until I tried a search spanning a
year. That's when I noticed I needed a different way to check the date
range, and where I'm stuck.
THE FUNCTION:
Public Function ConstructSQLStmtForMonthRange2(flagNoPreviousCondition As
Boolean, _
tableName As String, columnName As String, _
fromMonth As Integer, toMonth As Integer, _
fromYear As Integer, toYear As Integer, _
allDates As Boolean) As String
Dim returnSQLStmt As String ' String representing the SQL statement
that will be returned
' if the user did NOT check the 'All Dates' box
If allDates = False Then
' if there is NO previous condition, start SQL statement with "WHERE"
If flagNoPreviousCondition = True Then
returnSQLStmt = "WHERE " & tableName & "." & columnName & "
BETWEEN " & fromMonth & fromYear & " AND " & toMonth * toYear & " "
' if there are IS a previous condition, continue SQL statement with
"AND"
Else
returnSQLStmt = "AND " & tableName & "." & columnName & "
BETWEEN " & fromMonth & fromYear & " AND " & toMonth & toYear & " "
End If
End If
ConstructSQLStmtForMonthRange = returnSQLStmt
End Function
The specific code I need to change is:
"WHERE " & tableName & "." & columnName & " BETWEEN " & fromMonth & fromYear
& " AND " & toMonth * toYear & " "
The stuff after BETWEEN needs a proper way to be formatted, but I'm at a loss.
Any help is appreciated! And if I've done something stupid, point it out.
Thanks,
Jay