Access 2007 Report Criteria

Hi All

I have and access 2007 report that when runs returns all staff birthdays in the company, i want to be able to run the report with the option to only return staff with birthdays on a particular month, how can i achieve this

Thanks in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In the query that the report is based on, define a column as:

Month([<name of birthdatefield>])

so if the field name was DOB:


This will return a number from 1 - 12.   Now you can put criteria on it.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
to make a query return DOB on current month automatically

select *
from employees
where month([DOB]) =month(now())
If you want the option, you could set up a parameter query in the criteria row of the query's design grid under the DOB field, like this
BETWEEN [Birthday range start date:] AND [range end date:] 

Open in new window

That gives some flexibility to your report. You can change the input message portions that show up between the square brackets.
techsolve1Author Commented:
Thanks for the feedback very helpful
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
adding onto the above comments...

put a combobox on the form with the command button to open the report.  

OTHER tab of Property Sheet:
Name: txtMonthNumber
Status Bar Text: Choose Month for the report

DATA tab of Property Sheet:
Row Source Type: Value List
RowSource: 1;"January";2;"February";3;"March";4;"April";5;"May";6;"June";7;"July";8;"August";9;"September";10;"October";11;"November";12;"December"
DefaultValue:  =Month(Date())

FORMAT tab of Property Sheet:
ColumnCount: 2
ColumnWidths: 0;1    'assuming inches
ListWidth: 1.2

Then on the command button to open the report, here would be the CLICK event:
   Dim vCriteria As Variant
   vCriteria = Null
   With Me.txtMonthNumber
      If Not IsNull(.Value) Then
         vCriteria = "Month(DOB) = " & .Value
      End If
   End With
   DoCmd.OpenReport "ReportName", acViewPreview, , vCriteria

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.