Software

10 tips for working with dates in Microsoft Access

Despite the Date/Time data type and a selection of functions for manipulating date values and components, handling dates in Access is often a little tricky and nonintuitive. These tips will help you avoid the most common date-related headaches.

This article is also available as a PDF download.

Working with dates in Access can be somewhat confusing. Fortunately, Access offers the Date/Time data type, which greatly simplifies the job of handling dates. In addition, you'll find a number of functions that manipulate date values and components. But even with all the built-in help, solutions are seldom intuitive. Here are a few tips that will help you solve some of the most common date problems.

#1: Date data entry shortcuts

You don't always have to enter a literal date value. Access offers a couple of keyboard shortcuts, which are quicker and help eliminate typos. To enter the current date, press [Ctrl] ; (semicolon). If you want to copy a date from the previous record, press [Ctrl] ' (apostrophe). To enter the current time, press [Ctrl] : (colon).

#2: Eliminating date data entry--sometimes

Sometimes a date is the current date, and sometimes it isn't. When a date is usually the current date, you can eliminate entering any value at all. Simply set the field's Default Value property to Now() or Date(). When you enter a new record, the property will enter the system's current date (and time). You don't even need to press [Ctrl] ;.

#3: Now() versus Date()

The Now() function returns the system's current date and time. It's a package deal--you always get both. If all you need is the current date and not the time, use the Date() function instead. On the other hand, if all you want is the current time, use the Time() function. Don't use Now() unless you really mean to store both the date and time.

Why does it matter? A value that includes both a date and time component usually responds differently to expressions. For instance, you might expect the following expression to find dates that fall after December 12, 2006:

WHERE field >#12/12/2006#

However, the resulting recordset will include December 12, 2006, entries when the entry includes a time value. Conversely, Access won't return December 12, 2006, dates if the entry stores just a date value.

#4: Auditing changes by adding an edit date

If you have multiple users updating data, you might want to track who's making changes. You can do so by adding a simple event procedure in the following form to a data entry form's Dirty event:

Private Sub Form_Dirty(Cancel As Integer)
editfield = Now()
End Sub

where editfield represents the field that stores the last edit date.

Access fires the form's Dirty event when the contents of the form changes. This happens even if the user re-enters the same entry. If the user catches the mistake, he or she can press [Esc] to cancel the change. However, the user must then press it a second time to delete the newly inserted edit date before leaving the record. Otherwise, Access will save the edit date.

#5: Calculating Julian dates

A Julian date identifies a date by its numeric rank since January 1 (relative to the current year). This rank tells us how many days fall between a date and January 1 of the same year. For instance, January 1 of any year is 1, February 1 of any year is 32, and so on. Leap years complicate things because dates following February 28 in a leap year aren't the same as those in a non-leap year. To calculate a Julian date, use the following expression:

JulianDate = date - DateSerial(Year(date), 1, 1) + 1

where date is the date or Date/Time field for which you're calculating a Julian value. This expression works for leap and non-leap years.

#6: Date delimiters

When including numeric values in expressions, you don't need to identify the value in any way. However, you must identify dates. The correct character to use when including a literal date value is the pound character (#). For instance, the following expression will not return the number of days between two dates:

TotalDays: 3/3/2007 - 2/1/2007

Instead, it returns -4.9825610363727E-04. That's because Access evaluates the dates mathematically: 3 divided by 3 divided by 2007, subtract 2, and so on. The returned value isn't wrong; it just doesn't reflect your intentions.

Be sure to identify date values so Access can handle them properly in equations, as follows:

TotalDays: #3/3/2007# - #2/1/2007#

When you identify the values as dates using the # delimiter, Access returns the value 30--the number of days between February 1, 2007, and March 3, 2007.

#7: Using comparison operators to span a date range

Finding all the records with a specific date is easy--simply enter the date in question. But finding a subset of dates that fall between two dates requires the following conditional operators: <, <=, >, and >=. For instance, the SQL expression for locating all date values earlier than a specific date is:

WHERE datefield < #date#

If you're using the QBE gird, you'd enter the simpler expression into datefield's Criteria cell:

< #date#

Perhaps you need a smaller subset, such as all the dates before 2006 but after 2002. In that case, you'd specify both dates as follows:

WHERE datefield > #12/31/2002# AND datefield < #1/1/2006#

Alternatively, use the simpler expression via the QBE grid:

> #12/31/2002# AND datefield < #1/1/2006#

Both expressions return only those dates that fall in 2003, 2004, and 2005.

#8: Using Format() to display dates

Most of the time, you'll want to display dates in a specific format without storing that format as part of the date. The Format() function allows you to display individual and combination date components without affecting the stored date value. To display a single component, use the appropriate format code. For instance, the following expression would return only the day of the month as an integer (1 through 31):

Format(datefield, "d")

If you want a leading zero, use dd instead of d. The table below contains more individual component formats.

ddd

Returns the day of the week as a three-letter abbreviation: Mon, Tues, Wed, and so on.

dddd

Returns the full name of the day of the week: Monday, Tuesday, Wednesday, and so on

m

Returns the month as an integer, 1 through 12.

mmm

Returns the month as a three-letter abbreviation: Jan, Feb, and so on.

mmmm

Returns the full month name: January, February, and so on.

yy

Returns the last two digits of the year: 06, 07, and so on.

yyyy

Returns a four-digit year: 2006, 2007, and so on.

When using Format(), remember that the resulting value is a string. You'll use Format() to display dates, but don't use it when you must use the results in mathematical equations.

#9: Sorting by date components

Sorting by dates is simple. You just set the date field's Sort option or add an ORDER BY clause to the SQL statement. However, Access will use the entire date entry. If you're trying to sort by a single date component--the day of the month, the month, or the year--use the Day(), Month(), or Year() functions. For instance, if you wanted to find all the January dates, you'd use the SQL WHERE clause in the form:

WHERE Month(datefield) = 1

To enter the equivalent expression in the QBE grid, you'd enter the following expression as datefield's criteria:

Month(datefield) = 1

#10: Returning the first or last day of the month

None of the Access date functions returns the first or the last day of a specific or relative month. To do both, you'll need custom expressions. To return the first and last day of the current and relative months, use the following expressions:

Current month

FirstDay = DateSerial(Year(date), Month(date), 1)
LastDay = DateSerial(Year(date), Month(date) + 1, 0)

Previous month

FirstDay = DateSerial(Year(date), Month(date) - 1, 1)
LastDay = DateSerial(Year(date), Month(date), 0)

Next month

FirstDay = DateSerial(Year(date), Month(date) + 1, 1)
LastDay = DateSerial(Year(date), Month(date) + 2, 0)

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

15 comments
DMCX
DMCX

New to access. I have created a basic query that shows associate's orders completed the from the previous day using " Between Now() And Now()-1" pretty basic. My boss would like to show orders completed between now and the beginning of shift (6:00 AM or 6:00 PM). I'm thinking I need to use the where function but all attempts have been futile up to this point. Any guidance would be appreciated.

RHinesBTS
RHinesBTS

I am looking for a way to create an expression or a custom function for displaying and calculating specific dates for "business days" only ie, excluding Saturday or Sunday or Holidays....Is that possible.

khanzafarh
khanzafarh

how to calculate how many days in each month for a duration which includes more than one month. For example if start date is 12/20/2011 and end date is 1/25/2012 then I need a formula which can output 11 in MonthCoulmn December and 25 in Month Column January

nehabeke
nehabeke

Hi, SELECT e.EmpCode, e.DesiCode, e.EmpName, e.EmpAdd, e.BasicSalary, d.DesiName, e.JoiningDate, Format (e.JoiningDate, 'MM/yyyy'),format(e.RetierDate,"mm/yy"),e.RetierDate, Format (e.RetierDate, 'MM/yyyy'), (SELECT SUM(a.Amount) FROM pay_emp_master as e1 INNER JOIN pay_emp_allowances as a ON e1.EmpCode=a.EmpCode WHERE a.EmpCode=e.EmpCode GROUP BY e1.EmpCode,a.AllowCode) AS SumAllow, IIF([SumAllow],[SumAllow],0) AS SumAllow1, (SELECT SUM(d.Amount) FROM pay_emp_master as e1 INNER JOIN pay_emp_deductions as d ON e1.EmpCode=d.EmpCode WHERE d.EmpCode=e.EmpCode GROUP BY e1.EmpCode,d.DeductionCode) AS SumDeduct, IIF([SumDeduct],[SumDeduct],0) AS SumDeduct1 FROM pay_emp_master AS e INNER JOIN pay_desi_master AS d ON e.DesiCode=d.DesiCode WHERE (e.EmpCode=17) And ((year(#02/2002#) between year(e.JoiningDate) and year(e.RetierDate)) AND (month(#02/2002#) >= month(e.JoiningDate) and month(#02/2002#)

georges1974
georges1974

Bonjour, je dois saisir 2 fois la m?me date sur un formulaire de saisie Access. Existe-t-il une formule pour que la date exp?dition = date r?ception sans la saisir une 2?fois? Celle-ci devrait s'imputer directement dans la table des enregistrements. Merci d'avance

access_newbie
access_newbie

I think it's very useful, but it's not all what i need.

etzed
etzed

I have been using Access since Version 2 was released. Dates are the one area MS has a lot to fix up across all its suite of applications. This is the one area I hide the sledge hammer and take extended time out to rethink a workaround.

JodyGilbert
JodyGilbert

Have you sometimes run into problems with unexpected or unwanted results involving dates in Access? What other tips/shortcuts have you found useful when it comes to working with dates?

GSG
GSG

The original article is 7 years old so I doubt it's being followed. Try re-posting in the Q&A section.The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here: http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'. Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". .

wizard57m-cnet
wizard57m-cnet

Try re-posting in the Q&A section.The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here: http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'. Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". . .

Tony Hopkinson
Tony Hopkinson

We do have a questions forum, and there are a lot of competent access people on here. Not me though, I hasten to add.

john.a.wills
john.a.wills

No, a Julian date is one written according to the Julian calendar, as distinct from one writtena ccording to, say, the Gregorian calendar. Today is 06 August 2008 Gregorian and July 24, 2008 Julian. Actually, I may have the Julian wrong by a day.

Tink!
Tink!

Cool. Now I know to use that event. I've been using On Change. But On Dirty simplifies things a bit.

tbarkdull
tbarkdull

I run a queryr which returns a computer inventory, but I need it to return the inventory for the current month. I did it this way: Format(Date(), "mmmm")

Editor's Picks