more about queries in access

A database is a long term project.

The database you create today will start accumulating data and will continue to do so for the next 3 or 5 or 10 years, maybe more.

At some point, maybe 7 years from now, your boss will ask: "What were the sales? How much money did we make?"

Does she mean "Over the last 7 years"? Of course not. That would be a meaningless figure. She means: "yesterday" or "last month" or "last year" or "over the special promotion period between March 12 and March 24".

Databases keep track of transactions. Transactions are business processes usually involving an exchange of money. Every transaction has a date associated with it. A cutomer purchase has an invoice date, a student signing-up for courses has a registration date, an employee getting his pension has a retirement date, etc.

Most transaction dates are processed based on the system date of the computer creating the transaction. Every computer has a system clock and there are standard functions to extract the date and time from the system clock.

At the top of this page you should see today's date in red. I do not go around changing the date on every one of my web pages every morning! It's done with code, more specifically, Javascript code that extracts the information from your system and displays it. If the date is not correct, the problem is in your system and you should correct it. Here's what the code looks like:

javascript date code

You can probably guess what the functions getYear(), getMonth() and so on are used for, and the names of the functions are different in every language, but that's the kind of thing we'll be using to build our queries.

access date functions

Here's a useful reference to keep handy. It lists all Access functions by category. We'll start with the DATE/TIME functions and you may want to refer to it often. It will also save a lot of keying since you can copy/paste all the code into queries and VBA and so on.

Functions (arranged by category)

From now on, when working on an Access database, forget that today's date is January 10 or whatever.

Today is date(), yesterday is date()-1, tomorrow is date()+1, the month we're in is month(date()) and last month is ... well, we'll have to calculate that one later because it's not as obvious as it seems.

Here are the basic functions:
date() - extract the current system date
day(date()) - extract the day of the month (1-31) from the system date
month(date()) - extract the number of the month (1-12) from the system date
year(date()) - extract the 4-digit year from the system date

Let's build a few sample queries with Mike's Garage:

List the invoices from yesterday:

Now, list the invoices for this month. So, you have to extract the month of the invoice and compare it with the month of today's date.

Hold on a second! Remember that the database will be in use for years. So, if the database started in January 2011 and if the query is run in January 2013, do you want to see invoices for every month #1, from 2013, 2012, 2011? Of course not. You'll have to compare year() and month() to match invoices.

In this query you will add a column to extract the year and a column to extract the month from the invoice date and compare both with today.

There's another way of doing it. It uses one of the most useful of the date functions: dateserial().

You can read the reference to it here:
Functions for calculating and for displaying Date
and you can then simply Copy and Paste the function into your query.

So, to get this month's invoices, [invoice date] is between the first day of the current month and today.

Remember that we said that last month is complicated. If month() is 6 - June, month()-1 returns 5 - May. But what if month() is 1 - January? Then, month()-1 returns 0. That's why it doesn't work.

To make a long story short: use the dateserial() function to calculate what last month was. Extract the invoices where [invoice date] is between the first day of the previous month and the last day of the previous month. It works even when the current month is 1.

This month's invoices:

Last month's invoices: