Lets assume they are date/time fields. You want a query which will show
those employee records which have an anniversary next month. (Let's simply
things and concentrate only on birthdays, and let's initially make it for
this month).
Create a new query in the builder, so you pick out some employee
identification (reference number or name - exactly what is up to you and can
be changed later). Also pick out the birthdate. Run the query. You should
get a list of all employees (say name) and birthdate.
OK? That's step (1). View the query in SQL mode. OK? That's step 2.
Now we want to be able too limit the returned records so that only those are
returned for which the Month part of their birthdate is the same (for now)
as this month. It's December. So we need to pick out the month part of the
date in each record.
Lynn pointed out the best way to do this: use the Month() function. You can
look this up in Help: "month function". You give it a date and it gives you
back a number. You use this in a query by wrapping the function around the
field, so where you already have:
birthdate
.... you now put:
Month(birthdate)
If you have spaces in your field name (bad idea) you'd need to put square
brackets around the field name.
Access will usually want to give it a "column header name", usually Expr1
(and so on). You can edit this to any label you want. Otherwise, you'll
end up with:
Expr1:Month(birthdate)
Run it. Ok? Step (3). Instead of dates you'll see month-numbers.
Now we want to restrict the records to those which match our chosen month,
which is December.
So, in the (first) Criteria row, just enter 12 (no quotes) . Run the query
again - you should see only 12s in the "Expr1" column.
Ok? Step 4.
That's the essential job done. You may want to improve it by adding the
birthdate to the query again without the Month function, and unticking the
check-box which tells Access to display the month numbers (Expr1), and there
are ways of automatically choosing the criterion month.
One way to do this simply (and inefficiently!) is to use the Date() function
in the criterion. Where you now have 12, replace that with:
=Month(Date())
Date() returns the current system date, and Month() picks out the Month
number as before. You could also add 1 to get _next_ month:
=Month(Date())+1
The problem with accessing today's date in the query is that it will do it
for every record - which is why I said it was inefficient. However,
efficiency doesn't matter until it matters, and if the delay in processing
can be neglected the benefit of doing this "better" may be less than the
cost of doing it. For the record, I'd be launching this query from a form
,which would invoke the Date() function one-time, and have the query refer
to the value stored in the form. If I thought it was worth it ....
Let us know how you get on.
Things to research:
In Access 2007, if you search Help for "functions arranged by category" and
