databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Query to display Birthdays for the Current Month

You may like to run a Microsoft Access Query at the beginning of each month to display important dates, such as birthdays or appointments, relevant to the current month only. We can perform this action by using the Month() function in our query design.

The following example shows our original table. The data includes a date of birth field, which we will use as part of our query criteria.

Microsoft Access table showing original data, including a Date Of Birth field.

In our query design, to return Birthdays for only the current month, we will use the Month() function. In our design, we will add a new column, that includes the function:

Syntax: Month(date)

This returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.

Our example will use: ThisMonthsBirthdays: Month([dtmBirthDate])

If we then set the criteria of this new field to:

Month(Now())

The query design should look like the following:

Microsoft Access Query design to query for birthdays in the current Month

The query returns records with a dtmBirthDate value from the current month. We have chosen to hide this new column (Show, unchecked) as we do not need to see the value of Month([dtmBirthDate]), which is 7 at the time of writing (July being the 7th month)

The following results are returned from the sample table, where they only match the criteria of records that fall in the current Month:

Records that match the current month criteria of the Microsoft Access query