find all records in date field

Phil,

I am still getting no results with your suggestion. Any idea where I should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to suit, and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular month

.

PlarfySoober,

What is the Data Type of your field?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"PlarfySoober" wrote in message
...
Phil,

I am still getting no results with your suggestion. Any idea where I
should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to suit,
and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular month

.

(Just back from a trip).

Let's go back a few steps. Which version of Access are you using?

You have a table of employees which holds their birthdates and hire dates.
Are these fields date/time fields? (Look at the table in design view). If
not, we'll want to convert the data into that format (there are a raft of
functions which can help with this).

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
look at the Date category you'll find all sorts of ways to handle Date/Time
values. More generally, it's well worth learning to use the "Expression
Builder" - try Googling for videos on this, or just look in Help.

Phil, London

"PlarfySoober" wrote in message
...
Phil,

I am still getting no results with your suggestion. Any idea where I
should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to suit,
and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular month

.

Well, I'm glad I re-read that. Anyone spot the error?

If you add one (for "next month") to the 12 which represents January, you
get 13.

Hmm...

The way to fix this is to use the "Mod" operator (see Help). (27 Mod 24)
returns 3 - the remainder after dividing 27 by 24. That might be useful for
obtaining an hour value. I read it out loud as "27 with any whole 24s taken
away".

In our case we need to put the existing expression in brackets and add Mod
12, so the criterion becomes:

=(Month(Date())+1) Mod 12

That's (today) equivalent to 13 Mod 12, which is 1.

So, when we're in December, and the month number is therefore 12, the
expression returns 1 (for January) which is what we need.

Phil

"Philip Herlihy" wrote in message
...
(Just back from a trip).

Let's go back a few steps. Which version of Access are you using?

You have a table of employees which holds their birthdates and hire dates.
Are these fields date/time fields? (Look at the table in design view).
If not, we'll want to convert the data into that format (there are a raft
of functions which can help with this).

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 look at the Date category you'll find all sorts of ways to handle
Date/Time values. More generally, it's well worth learning to use the
"Expression Builder" - try Googling for videos on this, or just look in
Help.

Phil, London

"PlarfySoober" wrote in message
...
Phil,

I am still getting no results with your suggestion. Any idea where I
should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to suit,
and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular month

.

Philip,

Well I guess today I am *ayone* because I did not spot that error. Nice
catch!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Philip Herlihy" wrote in message
...
Well, I'm glad I re-read that. Anyone spot the error?

If you add one (for "next month") to the 12 which represents January, you
get 13.

Hmm...

The way to fix this is to use the "Mod" operator (see Help). (27 Mod 24)
returns 3 - the remainder after dividing 27 by 24. That might be useful
for obtaining an hour value. I read it out loud as "27 with any whole 24s
taken away".

In our case we need to put the existing expression in brackets and add Mod
12, so the criterion becomes:

=(Month(Date())+1) Mod 12

That's (today) equivalent to 13 Mod 12, which is 1.

So, when we're in December, and the month number is therefore 12, the
expression returns 1 (for January) which is what we need.

Phil

"Philip Herlihy" wrote in message
...
(Just back from a trip).

Let's go back a few steps. Which version of Access are you using?

You have a table of employees which holds their birthdates and hire
dates. Are these fields date/time fields? (Look at the table in design
view). If not, we'll want to convert the data into that format (there are
a raft of functions which can help with this).

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 look at the Date category you'll find all sorts of ways to handle
Date/Time values. More generally, it's well worth learning to use the
"Expression Builder" - try Googling for videos on this, or just look in
Help.

Phil, London

"PlarfySoober" wrote in message
...
Phil,

I am still getting no results with your suggestion. Any idea where I
should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to suit,
and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular
month

.

Still a small problem in that November (11) will return zero. The trick is to
do the mod of the month number before adding 1.

=(Month(Date())+1) Mod 12
(11 + 1) Mod 12 = 0
(12 + 1) Mod 12 = 1

= (Month(Date()) Mod 12) + 1
(11 Mod 12) + 1 = 12
(12 Mod 12) + 1 = 1

Or you could always USE
= Month(DateAdd("m",1,Date()))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Philip Herlihy wrote:
Well, I'm glad I re-read that. Anyone spot the error?

If you add one (for "next month") to the 12 which represents January,
you get 13.

Hmm...

The way to fix this is to use the "Mod" operator (see Help). (27 Mod
24) returns 3 - the remainder after dividing 27 by 24. That might be
useful for obtaining an hour value. I read it out loud as "27 with any
whole 24s taken away".

In our case we need to put the existing expression in brackets and add
Mod 12, so the criterion becomes:

=(Month(Date())+1) Mod 12

That's (today) equivalent to 13 Mod 12, which is 1.

So, when we're in December, and the month number is therefore 12, the
expression returns 1 (for January) which is what we need.

Phil

"Philip Herlihy" wrote in message
...
(Just back from a trip).

Let's go back a few steps. Which version of Access are you using?

You have a table of employees which holds their birthdates and hire
dates. Are these fields date/time fields? (Look at the table in
design view). If not, we'll want to convert the data into that format
(there are a raft of functions which can help with this).

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 look at the Date category you'll find all sorts of ways
to handle Date/Time values. More generally, it's well worth learning
to use the "Expression Builder" - try Googling for videos on this, or
just look in Help.

Phil, London

"PlarfySoober" wrote in
message ...
Phil,

I am still getting no results with your suggestion. Any idea where I
should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to
suit, and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular
month

.

Gina,

Thanks for replying. The data type of the Date_of_Birth field is date/time.

Don.

"Gina Whipp" wrote:

PlarfySoober,

What is the Data Type of your field?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"PlarfySoober" wrote in message
...
Phil,

I am still getting no results with your suggestion. Any idea where I
should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to suit,
and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular month

.



.

Don,

Did you see Philip's reply? It seems we all missed something which explains
why your criteria is not working.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"PlarfySoober" wrote in message
...
Gina,

Thanks for replying. The data type of the Date_of_Birth field is
date/time.

Don.

"Gina Whipp" wrote:

PlarfySoober,

What is the Data Type of your field?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"PlarfySoober" wrote in message
...
Phil,

I am still getting no results with your suggestion. Any idea where I
should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to
suit,
and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular
month

.



.

Nice one, John!

Best development group I ever worked in (part of BT) had a culture of "peer
review", in which the group spent time looking over your code and finding
the defects (before the customer did). Took a little getting used to, but
you soon realised that was why it was the best group!

Shame on me, nevertheless. I should know better than to skip testing on
other values...

Thanks - Phil

"John Spencer" wrote in message
...
Still a small problem in that November (11) will return zero. The trick
is to do the mod of the month number before adding 1.

=(Month(Date())+1) Mod 12
(11 + 1) Mod 12 = 0
(12 + 1) Mod 12 = 1

= (Month(Date()) Mod 12) + 1
(11 Mod 12) + 1 = 12
(12 Mod 12) + 1 = 1

Or you could always USE
= Month(DateAdd("m",1,Date()))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Philip Herlihy wrote:
Well, I'm glad I re-read that. Anyone spot the error?

If you add one (for "next month") to the 12 which represents January, you
get 13.

Hmm...

The way to fix this is to use the "Mod" operator (see Help). (27 Mod 24)
returns 3 - the remainder after dividing 27 by 24. That might be useful
for obtaining an hour value. I read it out loud as "27 with any whole
24s taken away".

In our case we need to put the existing expression in brackets and add
Mod 12, so the criterion becomes:

=(Month(Date())+1) Mod 12

That's (today) equivalent to 13 Mod 12, which is 1.

So, when we're in December, and the month number is therefore 12, the
expression returns 1 (for January) which is what we need.

Phil

"Philip Herlihy" wrote in message
...
(Just back from a trip).

Let's go back a few steps. Which version of Access are you using?

You have a table of employees which holds their birthdates and hire
dates. Are these fields date/time fields? (Look at the table in design
view). If not, we'll want to convert the data into that format (there
are a raft of functions which can help with this).

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 look at the Date category you'll find all sorts of ways to handle
Date/Time values. More generally, it's well worth learning to use the
"Expression Builder" - try Googling for videos on this, or just look in
Help.

Phil, London

"PlarfySoober" wrote in message
...
Phil,

I am still getting no results with your suggestion. Any idea where I
should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to
suit, and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular
month

.

OK,

Thanks to all for actively helping. And the end was that I used an SQL query
to create the filter I needed:

SELECT [Employee List A].Employee_First, [Employee List A].Employee_Last,
[Employee List A].Date_of_Birth
FROM [Employee List A]
WHERE (((Month([Date_of_Birth]))=12 Or (Month([Date_of_Birth]))=1));

The last line was me kicking up my heels, since I had found what works OK.

Thanks again to all, and I promise Philip I will study his last reply.

Don.


"Philip Herlihy" wrote:

(Just back from a trip).

Let's go back a few steps. Which version of Access are you using?

You have a table of employees which holds their birthdates and hire dates.
Are these fields date/time fields? (Look at the table in design view). If
not, we'll want to convert the data into that format (there are a raft of
functions which can help with this).

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
look at the Date category you'll find all sorts of ways to handle Date/Time
values. More generally, it's well worth learning to use the "Expression
Builder" - try Googling for videos on this, or just look in Help.

Phil, London

"PlarfySoober" wrote in message
...
Phil,

I am still getting no results with your suggestion. Any idea where I
should
go from here?

Thanks.

Don.

"Philip Herlihy" wrote:

Use a query, and in the "Criteria" box, put this (change dates to suit,
and
use the normal format for your location, eg 12/25/2009 instead of
25/12/2009)

Between #01/01/2009# and #25/12/2009#

Phil, London

"Keith" wrote in message
...
How do I find all records for a field "Birthdate" for particular month

.

.