How can I get birthdate filtered for this september

[Guest]

I want to select a range of different birthdate in the range of 19 sep to 8
nov this year.

 

[Dr. Know]

hrnaef said:

I want to select a range of different birthdate in the range of 19 sep to 8
nov this year.

Have you tried a query with

.... WHERE [DOB] > #9/18/2005# and [DOB] < #11/9/2005#

?

Dr. Know

 

[Douglas J Steele]

That will give you nothing, since presumably there won't be any records in
the database where the DOB hasn't yet occurred!

You need to add a computed field to the query that either returns only the
month and day of birth, or returns when their birthday will occur this year.

The former is Format([DOB], "mmdd"), the latter is DateSerial(Year(Date()),
Month([DOB]), Day([DOB]).

If you use the former, the condition would be Between "0919" And "1108". For
the latter, the condition Dr. Know gave would work.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

hrnaef said:

I want to select a range of different birthdate in the range of 19 sep to 8
nov this year.

Have you tried a query with

... WHERE [DOB] > #9/18/2005# and [DOB] < #11/9/2005#

?

Dr. Know

 

[John Vinson]

I want to select a range of different birthdate in the range of 19 sep to 8
nov this year.

You can use a calculated field to get this year's birthday
anniversary:

HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

Use a criterion on this field of

BETWEEN [Enter start date:] AND [Enter end date:]

to prompt for the desired date range (or, of course, just use the
literal dates):

BETWEEN #9/19# AND #11/8#

John W. Vinson[MVP]

 

[Guest]

This fit perfectly.

Thank you

I want to select a range of different birthdate in the range of 19 sep to 8
nov this year.

You can use a calculated field to get this year's birthday
anniversary:

HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

Use a criterion on this field of

BETWEEN [Enter start date:] AND [Enter end date:]

to prompt for the desired date range (or, of course, just use the
literal dates):

BETWEEN #9/19# AND #11/8#

John W. Vinson[MVP]

 

[Allen Browne]

In query design, type this into a fresh column in the Field row:
TheMonth: Month([DOB])
In the Sorting row under this, choose:
Ascending

If you wish to sort by day within the month, type this into the next column
in the Field row:
TheDay: Day([DOB])

You can filter for birthdays in December by typing:
12
into the Criteria row under the first field above.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

sorry everyone i'm not very good with computers, but I was wondering if
anyone can tell me how to sort or filter my access client info database to
find all date of births by month.
I entered dob's as 11/9/1968. The sort function sorts by the day (11th)
and
not by the month (9). Can I change its searching capability to get month
of
birth without having to re-entering 4000 new birthdates which would read
9/11/1968.
I just dread the thought of spending 40 hours doing such a thing.
Rather spend it on the kids.

Thanking anyone.
Gus.

That will give you nothing, since presumably there won't be any records
in
the database where the DOB hasn't yet occurred!

You need to add a computed field to the query that either returns only
the
month and day of birth, or returns when their birthday will occur this
year.

The former is Format([DOB], "mmdd"), the latter is
DateSerial(Year(Date()),
Month([DOB]), Day([DOB]).

If you use the former, the condition would be Between "0919" And "1108".
For
the latter, the condition Dr. Know gave would work.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

hrnaef said:

I want to select a range of different birthdate in the range of 19 sep
to 8
nov this year.

Have you tried a query with

... WHERE [DOB] > #9/18/2005# and [DOB] < #11/9/2005#

?

Dr. Know

 

[John Vinson]

sorry everyone i'm not very good with computers, but I was wondering if
anyone can tell me how to sort or filter my access client info database to
find all date of births by month.
I entered dob's as 11/9/1968. The sort function sorts by the day (11th) and
not by the month (9). Can I change its searching capability to get month of
birth without having to re-entering 4000 new birthdates which would read
9/11/1968.
I just dread the thought of spending 40 hours doing such a thing.
Rather spend it on the kids.

Jargon alert:

You're making a very common misuse of the word "sort". In database
terms, to Sort means to take a set of records and put them in a
particular sequential order. I think you want to filter or search by
the month.

It's very easy to do so. You can create a Query based on your table
and - if the dob field is actually a Date/Time field, as it should be
- put

BirthMonth: Month([dob])

in a vacant Field cell. This field will contain numbers 1 through 12,
for January through December.

Maybe better: to see all records for birthdates in the upcoming month,
use a different calculated field:

HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

to get this year's birthday anniversary. Use a criterion

Between Date() AND Date() + 30

to get all birthdays during the next thirty days.

John W. Vinson[MVP]

 

[Guest]

me again.
thanks for the info. I have spent a couple of hours trying your suggestion.
I can't design my own query so I was using the wizard.
I kept getting regected with error 3075. It said I am getting the field info
wrong.
Should I purchase a book on access?
Lost!
Gus.

sorry everyone i'm not very good with computers, but I was wondering if
anyone can tell me how to sort or filter my access client info database to
find all date of births by month.
I entered dob's as 11/9/1968. The sort function sorts by the day (11th) and
not by the month (9). Can I change its searching capability to get month of
birth without having to re-entering 4000 new birthdates which would read
9/11/1968.
I just dread the thought of spending 40 hours doing such a thing.
Rather spend it on the kids.

Jargon alert:

You're making a very common misuse of the word "sort". In database
terms, to Sort means to take a set of records and put them in a
particular sequential order. I think you want to filter or search by
the month.

It's very easy to do so. You can create a Query based on your table
and - if the dob field is actually a Date/Time field, as it should be
- put

BirthMonth: Month([dob])

in a vacant Field cell. This field will contain numbers 1 through 12,
for January through December.

Maybe better: to see all records for birthdates in the upcoming month,
use a different calculated field:

HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

to get this year's birthday anniversary. Use a criterion

Between Date() AND Date() + 30

to get all birthdays during the next thirty days.

John W. Vinson[MVP]

 

[John Vinson]

me again.
thanks for the info. I have spent a couple of hours trying your suggestion.
I can't design my own query so I was using the wizard.
I kept getting regected with error 3075. It said I am getting the field info
wrong.

What did it ACTUALLY say?

Should I purchase a book on access?

There are several good ones. CHeck out the links at:

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

But if you could open your Query in SQL view (use the View menu) and
copy and paste it to a message here someone could help (I'll be
leaving for a week tomorrow morning).

I *suspect* - just a guess - that you have put this date value into a
Text type field. Open the table in design view and select the field -
if it's of Text type, try backing up your database and changing it to
Date/Time.

John W. Vinson[MVP]