Friday, January 20, 2012

Date Stuff – Useful Examples

This post is the third in a 3-part series on Date functions in Access. If you have not read the first two posts, it might be worth while to do so.

  1. Date Stuff – Functions
  2. Date Stuff – Using the functions
  3. Date Stuff – Useful Examples (this post)

To see working examples, please see the following sample: DateStuff.mdb on my website: www.RogersAccessLibrary.com.

Useful Examples

As with most things in Access, there are multiple ways to do most each of the things below, so I'll list at least a couple of methods.  There may be more.

First Day of the Current Month

  • DateSerial(DatePart("yyyy", Date()), DatePart("m", Date()),1)
  • DateAdd("d",1-DatePart("d",Date()),Date())
  • CDate(CStr(DatePart("m",Date())) & "/1")

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofMonth(vdate As Date)
FirstofMonth = DateAdd("d",1-DatePart("d",Date()),Date())
End Function

Last Day of the Current Month

  • DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())+1,1)-1
  • DateAdd("m",1,CDate(CStr(DatePart("m",Date())) & "/1"))-1

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastofMonth(vdate As Date)
LastofMonth =DateAdd("m",1,CDate(CStr(DatePart("m",Date())) & "/1"))-1
End Function

First Day of the Previous Month

  • DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)
  • DateAdd("m",-1,CDate(CStr(DatePart("m",Date())) & "/1"))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofPrevMonth(vdate As Date)
FirstofPrevMonth=DateAdd("d",1-DatePart("d",Date()),Date()) – 1
End Function

Last Day of the Previous Month

  • DateSerial(DatePart("yyyy", Date()), DatePart("m",Date()),1)-1
  • DateAdd("d",1-DatePart("d",Date()),Date()) - 1
  • CDate(CStr(DatePart("m",Date())) & "/1") – 1

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastofPrevMonth(vdate As Date)
LastofPrevMonth=DateAdd("d",1-DatePart("d",Date()),Date())–1
End Function

First Day of the Next Month

  • DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())+1,1)
  • DateAdd("m",1,CDate(CStr(DatePart("m",Date())) & "/1"))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofNextMonth(vdate As Date)
FirstofNextMonth = DateAdd("m",1,CDate(CStr(DatePart("m",Date()))& "/1"))
End Function

Last Day of the Next Month

  • DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())+2,1)-1
  • DateAdd("m",1,CDate(CStr(DatePart("m",Date())+1) & "/1")-1)

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastofNextMonth(vdate As Date)
LastofNextMonth = DateAdd("m",1,CDate(CStr(DatePart("m",Date())+1) & "/1")-1)
End Function

First Day of This Week

  • DateDiff("d",Weekday(Date()-1,1),Date())
  • Date()-Weekday(Date())+1

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofWeek(vdate As Date)
FirstofWeek= DateDiff("d",Weekday(Date()-1,1),Date()) 
End Function

Last Day of This Week

  • DateDiff("d",Weekday(Date()-1,1),Date())+6
  • Date()+(7-Weekday(Date()))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastOfWeek(vdate As Date)
LastOfWeek= DateDiff("d",Weekday(Date()-1,1),Date())+6
End Function

First Work Day of This Week

  • DateDiff("d",Weekday(Date()-1,2),Date())
  • Date()-Weekday(Date())+2

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstWorkdayOfWeek(vdate As Date)
FirstWorkdayOfWeek= DateDiff("d",Weekday(Date()-1,2),Date())
End Function

Last Work Day of This Week

  • DateDiff("d",Weekday(Date()-1,2),Date())+4
  • Date()+(6-Weekday(Date()))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastWorkdayOfWeek(vdate As Date)
LastWorkdayOfWeek= DateDiff("d",Weekday(Date()-1,2),Date())+4 
End Function

Number of Week Days

This function will calculate the number of week days between any two dates.

Function NumberOfWeekdays(begindate As Date,
                 EndDate As Date,
                
bolInclusive As Boolean) As Integer

Dim intCounter As Integer
Dim intMovingDate As Date
intCounter = 0
If bolInclusive Then
    intMovingDate = begindate
    EndDate = EndDate + 1
Else
    intMovingDate = begindate + 1
End If
Do While intMovingDate < EndDate
    If WeekDay(intMovingDate) <> 1 And WeekDay(intMovingDate) <> 7 Then
        intCounter = intCounter + 1
    End If
        intMovingDate = intMovingDate + 1
    Loop
NumberOfWeekdays = intCounter
End Function

Calling the function. (The third argument determines whether you want to include the begin and end dates in the weekday count.)

Sub testweekdays()
'uses the FirstOfMonth function above. Literal dates like #1/1/2012# can also be used.
Dim intdays As Integer
    intdays = NumberOfWeekdays(FirstofMonth(Date), Date, True)
    MsgBox intdays
End Sub

image

 

 

 

 

5 comments:

Andrew Richards said...

Interesting stuff....

As a matter of interest, do you find that you hit issues with regard to US dates versus UK dates? For example, if a user enters 01/02/2012 into a form, how do you ensure that your code and / or queries parse it correctly as 1 Feb, or 2 Jan? Particularly when ADO and SQL come into play too?

Interested in how you handle this...

Andrew

Roger's Access Blog said...

@Andrew,

That's a really good question, and it's a great subject for my next blog post.

vicki chisholm said...

Thanks for the info on date functions. I have to calculate the time between two dates. Both dates are formatted with the time. I'm getting some funky data. Any suggestions? Thanks!!!!!

Anonymous said...

I am trying to work out how I can list the actual dates between two dates. Here's my issue: date_in and date_out. I need to be able to list the acutal dates between those two fields - ie if date_in.value =#30.11.2012# and date_out.value = #3.12.2012#.

I know how to calculate the number of days, hours etc but I cannot work out how to list the dates programatically.

Roger's Access Blog said...

This is not the best venue to answer questions. I'd suggest asking this question on the web forum www.utteraccess.com