K
Keith
How do I find all records for a field "Birthdate" for particular month
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Philip Herlihy
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
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
L
Lynn Trapp
SELECT *
FROM YourTable
WHERE Month(BirthDate) = [Enter the number of a Month]
FROM YourTable
WHERE Month(BirthDate) = [Enter the number of a Month]
P
Philip Herlihy
That does make more sense, if you want everyone born in a particular month
(regardless of year!).
Phil
(regardless of year!).
Phil
Lynn Trapp said:SELECT *
FROM YourTable
WHERE Month(BirthDate) = [Enter the number of a Month]
--
Lynn Trapp
Trainer/Application Developer
Keith said:How do I find all records for a field "Birthdate" for particular month
P
PlarfySoober
Philip,
I tried that, because I'm so new to this (I did a bit of dBase programming
when there WAS such a thing), I'm not getting very far.
The statement you gave (WHERE (Birthdate) = 12, e.g.) doesn't seem to work
in a query, as far as I can tell.
And I don't seem to be able to get into SQL mode, either. I get only Display
and Design modes.
I know this starts a few steps back, but I need to select, every month, a
list of employees with birth dates and hire dates, separately, occur in the
current month +1.
Thanks.
I tried that, because I'm so new to this (I did a bit of dBase programming
when there WAS such a thing), I'm not getting very far.
The statement you gave (WHERE (Birthdate) = 12, e.g.) doesn't seem to work
in a query, as far as I can tell.
And I don't seem to be able to get into SQL mode, either. I get only Display
and Design modes.
I know this starts a few steps back, but I need to select, every month, a
list of employees with birth dates and hire dates, separately, occur in the
current month +1.
Thanks.
Philip Herlihy said:That does make more sense, if you want everyone born in a particular month
(regardless of year!).
Phil
.Lynn Trapp said:SELECT *
FROM YourTable
WHERE Month(BirthDate) = [Enter the number of a Month]
--
Lynn Trapp
Trainer/Application Developer
Keith said:How do I find all records for a field "Birthdate" for particular month
J
John Spencer
You are missing a bit of code.
WHERE MONTH([Birthdate]) = 12
should return all records where the month of the birthdate is December.
If you are using query design view then you would see something like:
Field: MONTH([Birthdate])
Criteria: 12
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
WHERE MONTH([Birthdate]) = 12
should return all records where the month of the birthdate is December.
If you are using query design view then you would see something like:
Field: MONTH([Birthdate])
Criteria: 12
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Philip,
I tried that, because I'm so new to this (I did a bit of dBase programming
when there WAS such a thing), I'm not getting very far.
The statement you gave (WHERE (Birthdate) = 12, e.g.) doesn't seem to work
in a query, as far as I can tell.
And I don't seem to be able to get into SQL mode, either. I get only Display
and Design modes.
I know this starts a few steps back, but I need to select, every month, a
list of employees with birth dates and hire dates, separately, occur in the
current month +1.
Thanks.
Philip Herlihy said:That does make more sense, if you want everyone born in a particular month
(regardless of year!).
Phil
.Lynn Trapp said:SELECT *
FROM YourTable
WHERE Month(BirthDate) = [Enter the number of a Month]
--
Lynn Trapp
Trainer/Application Developer
How do I find all records for a field "Birthdate" for particular month
P
PlarfySoober
Philip,
Thanks for the tip. I get no results, though, when I view as a Datasheet.
Don.
Thanks for the tip. I get no results, though, when I view as a Datasheet.
Don.
P
PlarfySoober
Philip,
Sorry about the multiple responses. I saved the query, thinking that would
help, but when it didn't closed it. Clicking to open it, I received an error,
"data type mismatch in criteria expression".
Don.
Sorry about the multiple responses. I saved the query, thinking that would
help, but when it didn't closed it. Clicking to open it, I received an error,
"data type mismatch in criteria expression".
Don.
P
PlarfySoober
Please ignore the above. It was a different attempt. Somebody had advised me
to use:
DatePart("m","Date_of_Birth")
in the Criteria line. No joy, and the error message, Data Type Mismatch.
to use:
DatePart("m","Date_of_Birth")
in the Criteria line. No joy, and the error message, Data Type Mismatch.
D
Douglas J. Steele
That's because "Date_of_Birth" is a literal text string, and you can't use
the DatePart function on a string.
Assuming the name of the field is Date_of_Birth, try
DatePart("m",Date_of_Birth)
the DatePart function on a string.
Assuming the name of the field is Date_of_Birth, try
DatePart("m",Date_of_Birth)
P
PlarfySoober
Phil,
I am still getting no results with your suggestion. Any idea where I should
go from here?
Thanks.
Don.
I am still getting no results with your suggestion. Any idea where I should
go from here?
Thanks.
Don.
G
Gina Whipp
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
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
P
Philip Herlihy
(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
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
P
Philip Herlihy
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
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
G
Gina Whipp
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
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
J
John Spencer
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
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
P
PlarfySoober
Gina,
Thanks for replying. The data type of the Date_of_Birth field is date/time.
Don.
Thanks for replying. The data type of the Date_of_Birth field is date/time.
Don.
G
Gina Whipp
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
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
P
Philip Herlihy
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
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
P
PlarfySoober
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.
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.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.