In this post I will teach you how to set up an Access query to return customers orders within a specific date range. A query in a relational database is asking your database a question. Let’s say I want to know how many orders were placed in the first quarter of 2012 or how many sales were made in June 2012? Those answers can be found by either setting up a query wizard or by just going straight to the design view of a query. In this post, I am going to go through the query wizard in Access 2010. The first step is to click the Create Tab then click Query Wizard.
Then I pick the Simple Query Wizard and pick the table(s) I want to pull my data from. Then send over the fields you want to display in your query by clicking the arrow right button.
Then I passed through the wizard’s default choices until I came to the final step in which I named the query qrySalesByDates and clicked Modify. The only thing waiting for me now is finish.
Welcome to the design view. This where you raise your hand and ask a question if you were a student.🙂 Well actually you ask your question in a written statement on the criteria line.
If I want to find all dates between Jan 1, 2012 and March 31, 2012 then I would type the following down in the criteria line: Between 1/1/2012 And 3/31/2012. Access would then automatically place pound signs around both dates. A little trick to type in a bigger box is to right click the criteria line and choose Zoom or the shortcut Shift+F2. Click Access keyboard shortcuts for my downloadable guide.
Save the query and run it by clicking the run button which is on the Query Tools Design tab if you are in the design view.
All dates within my question query show up as follows just as I had asked for.
A great way to not hard code this query and give it some flex muscles is to write a parameter query. Maybe you want the user to put the start date and end date each time they run the query but the dates will be different every time they want to run it. Then all you have to do is type the following in the criteria line. Between [enter start date] And [enter end date]. Place the script prompt for the user in square brackets.
After running the query and typing the following I get these different answers based on user input at the time of running the query. That’s awesome sauce indeed!
Good luck with collecting and querying your data. Hope this post helps.