Question Locked

Access - Retrieval of Records by Part Date

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.
Robyn

5 replies

by thumbsup2

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?

by thumbsup2

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:

month([Birthdate])=1

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.

by rob

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
Rob

This discussion is closed to new replies.