Solved

# How do I get all the dates that occured in a specifed month regardless of day or year?

Posted on 2014-08-02
155 Views
We want to pay our respects to horses that have died in a specific month.  So in my report, Horses that have Crossed Rainbow Bridge (died), I only want the names of the horses that died during the month of, say, August, regardless of day or year.
So what is the criteria in the datDateCrossedRainbowBridge column of the report's Record Source query?
0
Question by:bobbat

LVL 47

Assisted Solution

SELECT * FROM yourTable
WHERE Month([datDateCrossedRainbowBridge]) = 8
0

LVL 30

Assisted Solution

or;
``````SELECT * FROM tbl
WHERE FORMAT([datDateCrossedRainbowBridge],"mmmm")="August"
``````
0

LVL 44

Assisted Solution

If you are running this report for the current month:
``````SELECT * FROM HorsesTable
WHERE Month([datDateCrossedRainbowBridge]) = Month(Date)
``````
If reporting on next month's horses
``````SELECT * FROM HorsesTable
WHERE Month([datDateCrossedRainbowBridge]) = (month(x) mod 12) + 1
``````
0

Author Comment

For Dale Fye and hnsar: Your SQL works, thanks.

For aikimark:  When I execute the report with Record Source of: SELECT * FROM tblHorses WHERE Month([datDateCrossedRainbowBridge]) = Month(Date)

I get a dialog box asking me ("Enter Parameter Value") for the "Date".  If I enter todays date or any date the report works.  I would like to be able to skip entering the date.  How can I do this?
0

LVL 44

Accepted Solution

Either this:
``````SELECT * FROM tblHorses WHERE Month([datDateCrossedRainbowBridge]) = Month(Date())
``````
or this:
``````SELECT * FROM tblHorses WHERE Month([datDateCrossedRainbowBridge]) = Month(Now)
``````
0

Author Comment

That worked.  Access turns it into:
SELECT * FROM tblHorses
WHERE (((Month([datDateCrossedRainbowBridge]))=Month(Date())));

Thanks to all.

This report reminds us that in 2008 Point To Ponder was euthanized after screws securing the broken bone in her knee failed.
The tribute video is here: https://www.youtube.com/watch?v=hnQAlC7-vng
0

Author Closing Comment

Many thanks to everyone that helps us with the horses.  https://www.facebook.com/bhfer
0

## Featured Post

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…