Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: MS-Access
hi everyone
please I want to help me in finding function return number weekend in month with access database according to calendar in computer
for example if month is june the function will return 5
if month is july the funtion will return 4 and so on
thank you
Posted 21-Jun-12 4:26am
firaso395
Comments
Sandeep Mewara at 21-Jun-12 9:51am
   
What have you tried so for?
firaso at 21-Jun-12 12:11pm
   
I tried the following
varWeekendDays = (DateDiff(Interval:="ww", _
date1:=startDate, _
date2:=endDate) _
* ncNumberOfWeekendDays) _
+ IIf(DatePart(Interval:="w", _
Date:=startDate) = vbSunday, 1, 0) _
+ IIf(DatePart(Interval:="w", _
Date:=endDate) = vbSaturday, 1, 0)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Test it:
Option Compare Database
Option Explicit
 
Sub Test()
Dim dCurDate As Date, dStartDate As Date, dEndDate As Date
 
dStartDate = CDate("2012-10-16")
dEndDate = CDate("2013-04-15")
 
dCurDate = dStartDate
Do While dCurDate < dEndDate
    MsgBox Format(dCurDate, "MMMM yyyy") & " has " & GetCountOfWeeks(dCurDate) & " weeks!", vbInformation, "Message..."
    dCurDate = DateAdd("M", 1, dCurDate)
Loop
 
End Sub
 

Function GetCountOfWeeks(dInitialDate As Date) As Integer
Dim retVal As Integer
Dim dCurDate As Date, dStartDate As Date, dEndDate As Date
 
retVal = 0
dStartDate = GetFirstDayInMonth(dInitialDate)
dCurDate = dStartDate
dEndDate = GetLastDayInMonth(dInitialDate)
Do While dCurDate < dEndDate
    If Weekday(dCurDate) = vbSunday Then retVal = retVal + 1
    dCurDate = DateAdd("d", 1, dCurDate)
Loop
GetCountOfWeeks = retVal
 
End Function
 

Function GetFirstDayInMonth(dDate As Date) As Date
    GetFirstDayInMonth = DateSerial(Year(dDate), Month(dDate), 1)
End Function
 
Function GetLastDayInMonth(dDate As Date) As Date
    GetLastDayInMonth = DateSerial(Year(dDate), Month(dDate) + 1, 1 - 1)
End Function
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 15 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100