Count calendar days per week in a Month given a date

[manos39]

I d like some help please
i have an example query:

SELECT TblWorkdays.WorkdaysID, TblWorkdays.Workday
FROM TblWorkdays;

given the "Workday" like Format([Workday];"yyyy-mm-ww"),
i would like to count just the number of days there are in week like:

mon tue wed thu fri sat Sun =result
1 2 3 4 5 6 6
7 8 9 10 11 12 13 7
14 14 16 17 18 19 20 7
21 22 23 24 25 26 27 7
28 29 30 31 4


so in a new field, to have the result

WorkdaysID Workday N of days
1 2011/1/1 6
2 2011/1/2 7
3 2011/1/5 4


is it possible?

 

[dhookom]

Is Workday truly a date field?
Doesn't the 1st week of the 1st month of 2011 have either 1 or 7 days depending on how the first week is determined?

How do you define week?

There are 7 date values using Format(#YourDateHere#,"yyyy-mm-ww") that will result in 2011-01-2.


Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

Some confusion w/ the presentation?

Needs be to define "workdays". seems more like all the days in the "week" within the month?

Some of this would be relatively simplistic if you had fields in your calendar (WorkDay) identifying the various parts of the calendar. in particular, identify the 'week' of the year for each date (for this exercise). Theere is a thread in Tek-Tips (MS Access) both discussing the concept of the calendar table and referencing an article discussing the merits of having MANY different values which the author reviews. I found, in particular, that getting sets of contigious dates was much easier.

For your limited purpose (here), simply adding the week of the month (or year) would be sufficient. A real issue may be the deffinition of the start of the week, as there are varriances in this (see the article).

Give the additional column, getting the number of days in the month is a simple count by the week over the range (of the month)?



MichaelRed

 

[manos39]

Yes workday is a date field but it is formated to show the week number of the year with expresion: Workday: Format([Workday];"yyyy-mm-ww")so given the day of a workday 31/1/2011 it returns 2011-01-6
am i helpful?
To MichaelRed i think you undrestand the problem but i cannot quite come to the point of how to fix this problem. Can y help?

 

[dhookom]

To get the number of days in a week with the same month, you can create a function like

Public Function GetDaysInWeekMth(datDate As Date) As Integer
    Dim intDayCount As Integer
    Dim datStart As Date
    Dim datEnd As Date
[Green]    'Get the first and last dates of the week[/Green]
    datStart = DateAdd("d", -Weekday(datDate) + 1, datDate)
    datEnd = DateAdd("d", 6, datStart)
[Green]    'make sure we are not in the previous month[/Green]
    If Month(datDate) <> Month(datStart) Then
        datStart = DateAdd("d", -Day(datDate), datDate) + 1
    End If
[Green]    'make sure we are not in the Next month[/Green]
    If Month(datDate) <> Month(datEnd) Then
        datEnd = DateSerial(Year(datDate), Month(datDate) + 1, 0)
    End If
    GetDaysInWeekMth = datEnd - datStart + 1
    
End Function

You can use this function in code, query, control source, etc like:

 GetDaysInWeekMth([Your Date Value Here])

Duane
Hook'D on Access
MS Access MVP

 

[manos39]

I m not familiar where to put code! please help me.

 

[dhookom]

1) Create a new standard module
2) copy the code from above into the module from
Public Function...
to
End Function
3) Make sure it looks similar to the above to avoid line wrapping errors
4) Save the module with the name "modDateFunctions"

You can then use the function almost anywhere you would use a builtin function.

Duane
Hook'D on Access
MS Access MVP

 

[manos39]

Thank you! i ll let y know when i succeed!!

 

[manos39]

Super!! thank you very much!