Date Range Question

  • Thread starter Thread starter Guest
  • Start date Start date
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, thanks to some additional searching and some brilliant postsfrom
Allen Browne, I've managed to get the search working for Month/Year ranges.

Here's the new 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
Dim startDate As Date
Dim endDate As Date
Const conDateFormat = "\#mm\/dd\/yyyy\#"

startDate = DateSerial(fromYear, fromMonth, 1)
endDate = DateSerial(toYear, toMonth + 1, 1 - 1)

' 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 " & Format(startDate, conDateFormat) & " AND " & Format(endDate,
conDateFormat) & " "

' if there are IS a previous condition, continue SQL statement with
"AND"
Else
returnSQLStmt = "AND " & tableName & "." & columnName & "
BETWEEN " & Format(startDate, conDateFormat) & " AND " & Format(endDate,
conDateFormat) & " "
End If
End If

ConstructSQLStmtForMonthRange2 = returnSQLStmt
End Function

Onward, to the weeks! Stupid weeks...

Jay
 
Alright, I think I can solve the problem the same way for weeks as I did for
month, but I need one piece of the puzzle.

I don't know how to get a date from only the week and year.

I think I could get the first day and month of the "from week" and the last
day and month from the "to week" and then just calculate the date range that
way.

So, does anyone know how to get the first and last day and month of a
particular week in a certain year?

I have no idea. Again, any help is appreciated. Still searching...

Thanks,
Jay
 
Did you try the simple yet? That's FromWeek=9 (for example) and ToWeek=24,
in Access'ese,

FirstDate=DateAdd("ww",FromWeek,#1/1/2006#)
LastDate=DateAdd("ww",ToWeek-FromWeek+1,FirstDate)

If you want to start from the previous Sunday, insert a second line as
follows:

FirstDate=FirstDate-WeekDay(FirstDate)+1

HTH
Alright, I think I can solve the problem the same way for weeks as I did for
month, but I need one piece of the puzzle.

I don't know how to get a date from only the week and year.

I think I could get the first day and month of the "from week" and the last
day and month from the "to week" and then just calculate the date range that
way.

So, does anyone know how to get the first and last day and month of a
particular week in a certain year?

I have no idea. Again, any help is appreciated. Still searching...

Thanks,
Jay
Alright, thanks to some additional searching and some brilliant postsfrom
Allen Browne, I've managed to get the search working for Month/Year ranges.
Click to expand...
[quoted text clipped - 110 lines]
Click to expand...
 
Have I ever told you you're my hero? No? You're my hero!

I didn't even think of doing it that way...

I had to make some minor changes, but the result is:
startDate = DateAdd("ww", fromWeek, DateSerial(fromYear, 1, 1))
startDate = startDate - WeekDay(startDate) + 1
endDate = DateAdd("ww", toWeek - fromWeek + 1, startDate)
endDate = endDate - WeekDay(endDate)

Now the week is considered Sun - Sat.

Thanks very much!

Jay

OfficeDev18 via AccessMonster.com said:
Did you try the simple yet? That's FromWeek=9 (for example) and ToWeek=24,
in Access'ese,

FirstDate=DateAdd("ww",FromWeek,#1/1/2006#)
LastDate=DateAdd("ww",ToWeek-FromWeek+1,FirstDate)

If you want to start from the previous Sunday, insert a second line as
follows:

FirstDate=FirstDate-WeekDay(FirstDate)+1

HTH
Alright, I think I can solve the problem the same way for weeks as I did for
month, but I need one piece of the puzzle.

I don't know how to get a date from only the week and year.

I think I could get the first day and month of the "from week" and the last
day and month from the "to week" and then just calculate the date range that
way.

So, does anyone know how to get the first and last day and month of a
particular week in a certain year?

I have no idea. Again, any help is appreciated. Still searching...

Thanks,
Jay
Alright, thanks to some additional searching and some brilliant postsfrom
Allen Browne, I've managed to get the search working for Month/Year ranges.
Click to expand...
[quoted text clipped - 110 lines]
Thanks,
Jay
Click to expand...
Click to expand...
Click to expand...
 
You're very welcome, Jay.
Have I ever told you you're my hero? No? You're my hero!

I didn't even think of doing it that way...

I had to make some minor changes, but the result is:
startDate = DateAdd("ww", fromWeek, DateSerial(fromYear, 1, 1))
startDate = startDate - WeekDay(startDate) + 1
endDate = DateAdd("ww", toWeek - fromWeek + 1, startDate)
endDate = endDate - WeekDay(endDate)

Now the week is considered Sun - Sat.

Thanks very much!

Jay
Did you try the simple yet? That's FromWeek=9 (for example) and ToWeek=24,
in Access'ese,
Click to expand...
[quoted text clipped - 31 lines]
Click to expand...
 
Back
Top