searching for a function or SQL statement

  • Thread starter Thread starter Elise
  • Start date Start date
E

Elise

Hi all, I have a need to find out how many of a
particular day are in a date range. Once I find that
info, I will be able to calculate each day by another day
field. But I'm stumped on the first part. First, the
user will be supplying the date range - a different date
range every time. Say, 01/01/04 to 01/31/04 (could be
any range, a day, a week, a month, 2 1/2 months,
whatever). Once the user supplies the date range, I need
Access to update a table with 7 fields in it: Monday,
Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
It would update it with the # of occurences of each day
during that date range. The answer, for this specific
month, is: Mon=4, Tue=4, Wed=4, Thu=5, Fri=5, Sat=5,
Sun=4 (I looked on a calendar). I can't for the life of
me figure out why this would be so hard to do, but I
cannot come up with a formula for this. Any help, or
even just being pointed in the right direction of where
to look, would be immensely appreciated.

Thanks a million in advance!
Elise
 
Hi Elise,

I question your desire to store the results in different fields in the same table. This doesn't
seem like good database design to me....

However, here is a quick procedure I worked up which will present the results in a message box.
There may very well be a better or more efficient method of getting the same results. You can
take it from there and write the results to fields in a table, using recordset code, if you
really need to store the results of this calculation. The date inputs are not "bullet proof".
You might want to implement the logic that Doug Steele wrote about in a recent issue of Smart
Access:

http://members.rogers.com/douglas.j.steele/SmartAccess.html

September 2003: Access developer needs date (241 KB)
A look at problems dealing with date values.


Tom
_______________________________________

Option Compare Database
Option Explicit

Function CountDays()

Dim dteStartDate As Date, dteEndingDate As Date
Dim intSundays As Integer, intMondays As Integer
Dim intTuesdays As Integer, intWednesdays As Integer
Dim intThursdays As Integer, intFridays As Integer
Dim intSaturdays As Integer, dteTestDate As Date
Dim intNumDays As Integer

dteStartDate = InputBox("Enter Starting Date: ")
dteEndingDate = InputBox("Enter Ending Date: ")

' set initial value of dteTestDate
dteTestDate = dteEndingDate

intNumDays = DateDiff("d", dteStartDate, dteEndingDate)

Do Until intNumDays = 0
Select Case Weekday(dteTestDate)
Case 1
intSundays = intSundays + 1
Case 2
intMondays = intMondays + 1
Case 3
intTuesdays = intTuesdays + 1
Case 4
intWednesdays = intWednesdays + 1
Case 5
intThursdays = intThursdays + 1
Case 6
intFridays = intFridays + 1
Case 7
intSaturdays = intSaturdays + 1
End Select

intNumDays = intNumDays - 1
dteTestDate = dteTestDate - 1

Loop

MsgBox "Mon=" & intMondays & ", Tue=" & intTuesdays & _
", Wed=" & intWednesdays & ", Thu=" & intThursdays & _
", Fri=" & intFridays & ", Sat=" & intSaturdays & _
", Sun=" & intSundays, vbInformation, "And the answer is..."

End Function
_____________________________________


Hi all, I have a need to find out how many of a
particular day are in a date range. Once I find that
info, I will be able to calculate each day by another day
field. But I'm stumped on the first part. First, the
user will be supplying the date range - a different date
range every time. Say, 01/01/04 to 01/31/04 (could be
any range, a day, a week, a month, 2 1/2 months,
whatever). Once the user supplies the date range, I need
Access to update a table with 7 fields in it: Monday,
Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
It would update it with the # of occurences of each day
during that date range. The answer, for this specific
month, is: Mon=4, Tue=4, Wed=4, Thu=5, Fri=5, Sat=5,
Sun=4 (I looked on a calendar). I can't for the life of
me figure out why this would be so hard to do, but I
cannot come up with a formula for this. Any help, or
even just being pointed in the right direction of where
to look, would be immensely appreciated.

Thanks a million in advance!
Elise
 
Tom

It may be more efficient (especially for large number of days) to simply
find the number of complete weeks and then traverse the remainder (<7) to
find intMondays, intTuesdays, ...
 
Hi Van,

You're right. That would probably take some extra debugging work to get it right. I wrote this
procedure for Elise in about 10 minutes, so I wasn't convinced at all that I had used the most
efficient method....

Tom
___________________________________


Tom

It may be more efficient (especially for large number of days) to simply
find the number of complete weeks and then traverse the remainder (<7) to
find intMondays, intTuesdays, ...
 
Back
Top