Search The Web

Today's Headlines

Friday, November 5, 2010

Microsoft Access Tips & Tricks: Date Manipulations In Access

Access has several useful date-related functions that you can use to do several useful date calculations and manipulations. One of the questions that frequently arises is how to calculate things such as the end of the quarter given a date in that quarter. This post provides a function from which you can extract pieces that you need and use in your databases. More importantly, it provides you a good introduction to date manipulations so that you can use the code provided to write your own code to do other things that this code does not do.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields, finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, and conditional formatting.

Date manipulations in AccessTake a look at the screenshot of the form to the left. The user can enter a date in the textbox at the top of the form (or have the program automatically generate a random date to enter in the textbox, or enter today's date in the textbox). The code behind the form then fills out the dates in the rest of the boxes on the form.

The form itself probably does not have any place in any useful Access application. I added a picture of it to this post just to show you all the different date manipulations I could think of at short notice. The important part is the code behind the form. And that code is reproduced below.

The functions used in the code below are DateSerial(), DateAdd(), and DatePart(). These are very powerful functions that allow you to manipulate dates in multiple different ways. Learning to use them and take advantage of their power will enable you to be prepared for any date-related functionality your Access databases may require.
Option Compare Database
Option Explicit

Const maxDate = 2958100 'This represents 12/31/9998. I chose this as the maximum date that can
'be entered in the form so that none of the calculated dates can exceed
'12/31/9999 which is the maximum date that Access can handle.
Const minDate = -657069 'This represents 1/1/101. I chose this as the minimum date that can be
'be entered in the form so that none of the calculated dates can go below
'1/1/100 which is the minimum date that Access can handle.

'Note that there is no special handling required in dateserial() for month-ends, year-ends etc.
'The function automatically knows that adding a month in December puts you in January of next year,
'for example.

Private Sub DateEntered_AfterUpdate()
If CLng(Me.DateEntered) > maxDate Then
MsgBox ("The date you entered has to be before 12/31/9998")
Me.DateEntered = ""
Me.DateEntered.SetFocus
Exit Sub
End If
If CLng(Me.DateEntered) < minDate Then
MsgBox ("The date you entered has to be after 1/1/101")
Me.DateEntered = ""
Me.DateEntered.SetFocus
Exit Sub
End If

Me.PreviousDay = DateAdd("d", -1, Me.DateEntered)
Me.PreviousWorkDay = Me.PreviousDay
Me.NextDay = DateAdd("d", 1, Me.DateEntered)
Me.NextWorkDay = Me.NextDay
Me.ClosestWorkDay = Me.DateEntered
Select Case DatePart("w", Me.DateEntered)
Case 1:
Me.PreviousWorkDay = DateAdd("d", -2, Me.DateEntered)
Me.ClosestWorkDay = Me.NextWorkDay
Case 2:
Me.PreviousWorkDay = DateAdd("d", -3, Me.DateEntered)
Case 6:
Me.NextWorkDay = DateAdd("d", 3, Me.DateEntered)
Case 7:
Me.NextWorkDay = DateAdd("d", 2, Me.DateEntered)
Me.ClosestWorkDay = Me.PreviousWorkDay
End Select

Me.PreviousSaturday = DateAdd("d", -1 * DatePart("w", Me.DateEntered), Me.DateEntered)
Me.NextSaturday = DateAdd("d", 7 - DatePart("w", Me.DateEntered), Me.DateEntered)
If DateDiff("d", Me.PreviousSaturday, Me.DateEntered) < 4 Then
Me.ClosestSaturday = Me.PreviousSaturday
Else
Me.ClosestSaturday = Me.NextSaturday
End If
If DatePart("w", Me.DateEntered) = 7 Then
Me.NextSaturday = DateAdd("d", 7, Me.DateEntered)
Me.ClosestSaturday = Me.DateEntered
End If

Me.PreviousSunday = DateAdd("d", 1, Me.PreviousSaturday)
Me.NextSunday = DateAdd("d", 1, Me.NextSaturday)
If DatePart("w", Me.DateEntered) = 1 Then
Me.PreviousSunday = DateAdd("d", -7, Me.DateEntered)
Me.ClosestSunday = Me.DateEntered
Else
If DatePart("w", Me.DateEntered) = 7 Then
Me.NextSunday = DateAdd("d", 1, Me.DateEntered)
End If
If DateDiff("d", Me.PreviousSunday, Me.DateEntered) < DateDiff("d", Me.DateEntered, Me.NextSunday) Then
Me.ClosestSunday = Me.PreviousSunday
Else
Me.ClosestSunday = Me.NextSunday
End If
End If

Me.PreviousMonthBegin = DateSerial(Year(Me.DateEntered), Month(Me.DateEntered), 1)
If Day(Me.DateEntered) = 1 Then
Me.ClosestMonthBegin = Me.DateEntered
If Month(Me.DateEntered) = 1 Then
Me.PreviousMonthBegin = DateSerial(Year(Me.DateEntered), Month(Me.DateEntered) - 1, 1)
End If
End If

Me.NextMonthBegin = DateSerial(Year(Me.DateEntered), Month(Me.DateEntered) + 1, 1)

If Day(Me.DateEntered) <> 1 Then
If DateDiff("d", Me.PreviousMonthBegin, Me.DateEntered) < DateDiff("d", Me.DateEntered, Me.NextMonthBegin) Then
Me.ClosestMonthBegin = Me.PreviousMonthBegin
Else
Me.ClosestMonthBegin = Me.NextMonthBegin
End If
End If

Me.PreviousMonthEnd = DateAdd("d", -1, Me.PreviousMonthBegin)
Me.NextMonthEnd = DateAdd("d", -1, Me.NextMonthBegin)
If Day(Me.DateEntered) = 1 Then
Me.PreviousMonthEnd = DateAdd("d", -1, Me.DateEntered)
End If
If DateDiff("d", Me.PreviousMonthEnd, Me.DateEntered) < DateDiff("d", Me.DateEntered, Me.NextMonthEnd) Then
Me.ClosestMonthEnd = Me.PreviousMonthEnd
Else
Me.ClosestMonthEnd = Me.NextMonthEnd
End If

If Me.NextMonthEnd = Me.DateEntered Then 'This catches the case where the current date is a month end
Me.ClosestMonthEnd = Me.DateEntered
Me.NextMonthEnd = DateAdd("d", -1, DateSerial(Year(Me.DateEntered), Month(Me.DateEntered) + 2, 1))
End If

Select Case Month(Me.DateEntered)
Case 1, 2, 3:
Me.PreviousQuarterBegin = DateSerial(Year(Me.DateEntered), 1, 1)
Me.NextQuarterBegin = DateSerial(Year(Me.DateEntered), 4, 1)
Case 4, 5, 6:
Me.PreviousQuarterBegin = DateSerial(Year(Me.DateEntered), 4, 1)
Me.NextQuarterBegin = DateSerial(Year(Me.DateEntered), 7, 1)
Case 7, 8, 9:
Me.PreviousQuarterBegin = DateSerial(Year(Me.DateEntered), 7, 1)
Me.NextQuarterBegin = DateSerial(Year(Me.DateEntered), 10, 1)
Case Else:
Me.PreviousQuarterBegin = DateSerial(Year(Me.DateEntered), 10, 1)
Me.NextQuarterBegin = DateSerial(Year(Me.DateEntered) + 1, 1, 1)
End Select
If Me.PreviousQuarterBegin = Me.DateEntered Then
Me.ClosestQuarterBegin = Me.DateEntered
Me.PreviousQuarterBegin = DateAdd("m", -3, Me.PreviousQuarterBegin)
Else
If DateDiff("d", Me.PreviousQuarterBegin, Me.DateEntered) < DateDiff("d", Me.DateEntered, Me.NextQuarterBegin) Then
Me.ClosestQuarterBegin = Me.PreviousQuarterBegin
Else
Me.ClosestQuarterBegin = Me.NextQuarterBegin
End If
End If

Me.PreviousQuarterEnd = DateAdd("d", -1, Me.PreviousQuarterBegin)
Me.NextQuarterEnd = DateAdd("d", -1, Me.NextQuarterBegin)
If Me.DateEntered = Me.ClosestQuarterBegin Then
Me.PreviousQuarterEnd = DateAdd("d", -1, Me.DateEntered)
End If
If Me.DateEntered = Me.NextQuarterEnd Then
Me.ClosestQuarterEnd = Me.DateEntered
Me.NextQuarterEnd = DateSerial(Year(Me.NextQuarterBegin), Month(Me.NextQuarterBegin) + 3, Day(Me.NextQuarterBegin) - 1)
Else
If DateDiff("d", Me.PreviousQuarterEnd, Me.DateEntered) < DateDiff("d", Me.DateEntered, Me.NextQuarterEnd) Then
Me.ClosestQuarterEnd = Me.PreviousQuarterEnd
Else
Me.ClosestQuarterEnd = Me.NextQuarterEnd
End If
End If

Me.PreviousYearBegin = DateSerial(Year(Me.DateEntered), 1, 1)
Me.NextYearBegin = DateSerial(Year(Me.DateEntered) + 1, 1, 1)
If Me.PreviousYearBegin = Me.DateEntered Then
Me.ClosestYearBegin = Me.DateEntered
Me.PreviousYearBegin = DateAdd("yyyy", -1, Me.DateEntered)
Else
If DateDiff("d", Me.PreviousYearBegin, Me.DateEntered) < DateDiff("d", Me.DateEntered, Me.NextYearBegin) Then
Me.ClosestYearBegin = Me.PreviousYearBegin
Else
Me.ClosestYearBegin = Me.NextYearBegin
End If
End If

Me.NextYearEnd = DateAdd("d", -1, Me.NextYearBegin)
If Me.DateEntered = Me.ClosestYearBegin Then
Me.PreviousYearEnd = DateAdd("d", -1, Me.DateEntered)
Else
Me.PreviousYearEnd = DateAdd("d", -1, Me.PreviousYearBegin)
End If
If Me.DateEntered = Me.NextYearEnd Then
Me.ClosestYearEnd = Me.DateEntered
Me.NextYearEnd = DateAdd("yyyy", 1, Me.DateEntered)
Else
If DateDiff("d", Me.PreviousYearEnd, Me.DateEntered) < DateDiff("d", Me.DateEntered, Me.NextYearEnd) Then
Me.ClosestYearEnd = Me.PreviousYearEnd
Else
Me.ClosestYearEnd = Me.NextYearEnd
End If
End If

Me.FirstofCurrentWeek = DateAdd("d", -1 * DatePart("w", Me.DateEntered) + 1, Me.DateEntered)
Me.FirstOfPreviousWeek = DateAdd("ww", -1, Me.FirstofCurrentWeek)
Me.FirstofNextWeek = DateAdd("ww", 1, Me.FirstofCurrentWeek)

Me.FirstofCurrentMonth = DateAdd("d", -1 * DatePart("d", Me.DateEntered) + 1, Me.DateEntered)
Me.FirstofPreviousMonth = DateAdd("m", -1, Me.FirstofCurrentMonth)
Me.FirstofNextMonth = DateAdd("m", 1, Me.FirstofCurrentMonth)

Me.FirstofCurrentYear = DateAdd("d", -1 * DatePart("y", Me.DateEntered) + 1, Me.DateEntered)
Me.FirstofPreviousYear = DateAdd("yyyy", -1, Me.FirstofCurrentYear)
Me.FirstofNextYear = DateAdd("yyyy", 1, Me.FirstofCurrentYear)

Select Case Month(Me.DateEntered)
Case 1, 2, 3:
Me.FirstofCurrentQtr = DateSerial(Year(Me.DateEntered), 1, 1)
Case 4, 5, 6:
Me.FirstofCurrentQtr = DateSerial(Year(Me.DateEntered), 4, 1)
Case 7, 8, 9:
Me.FirstofCurrentQtr = DateSerial(Year(Me.DateEntered), 7, 1)
Case Else:
Me.FirstofCurrentQtr = DateSerial(Year(Me.DateEntered), 10, 1)
End Select
Me.FirstofPreviousQtr = DateAdd("m", -3, Me.FirstofCurrentQtr)
Me.FirstofNextQtr = DateAdd("m", 3, Me.FirstofCurrentQtr)
End Sub

Private Sub Form_Load()
DoCmd.Restore
End Sub

Private Sub RandomDate_Click()
Randomize
Me.DateEntered = CLng((maxDate - minDate) * Rnd() + minDate)
Me.DateEntered.SetFocus
DateEntered_AfterUpdate
End Sub

Private Sub TodayDate_Click()
Me.DateEntered = Date
Me.DateEntered.SetFocus
DateEntered_AfterUpdate
End Sub
Hope this post has been helpful in solving any problems you might have had with date manipulations in Access. The VBA code in this post has been tested in Access 2003 and should work without any problems in all versions of Access from Access 97 on up. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!

No comments:

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web