Access - Retrieval of Records by Part Date

By rob ·
I am doing a membership database for our local RSL sub-Branch and need to be able to sort members by their BIRTH MONTH.
I have tried different expressions but nothing is working.

Any help would be appreciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

re: Access - retrieval of records

by ThumbsUp2 In reply to Access - Retrieval of Rec ...

What format do you have their birth DATE stored in and what are you using to do your record retrieval? Access queries or a scripting language? What version of Access?

Collapse -


by rob In reply to re: Access - retrieval of ...

Short date ie: 00\->L<LL\-00;0;_
I have been trying to do a select query.
I have access 2002


Collapse -

re: sorting data

by ThumbsUp2 In reply to Format

That's the input mask for what you type into the field. The dates are stored internally in numerical value (mm/dd/yyyy or dd/mm/yyyy depending on your computers default region), not by input mask.

If you want to query just those who have a birthday in a certain month, the query criteria is:


Which will select those records with birth month 1 in the field named "Birthdate".

However, that query would need to be modified each time you wanted to search for a different month.

What I would do would be to add another field which would contain their birth month, then create a prompting criteria in the query to ask you which month you want to select.

You can store the birth month data as text (January, February, March, etc...) or store it as numeric (1, 2, 3, etc...).

So, now you have a field called Birthdate and a field called Birthmonth. In query design view under the Birthmonth field, use the following criteria to make it ask you which month you want to query:

[Enter birth month?]

Then, simply answer the question correctly when you run the query.

Collapse -


by rob In reply to re: sorting data

I had already tried month([Birthdate])=1 but as you said it would have to be entered each time.

I thought of your 2nd suggestion too, but there are far too many records that would have to be altered.

Thank you very much for your help

Related Discussions

Related Forums