Business Solutions: Access 2003 - Using Dates to Run Query

Business Solutions: Access 2003 - Using Dates from a Form to Run a Query

        by James D. Duncan, CPA   March 2005

          I will give you a simple example of using a form with a date field to run a query based on the date input. The real question is: if this is the simple version, what does the complicated version look like? Ha ha.

          We have a table that has many fields, but all we care about is the entry date and exit date of clients. This project is about reporting for a United Way agency for their annual report. In this example we will answer just one question. How many clients on the first day of the operating year? With just the entry date and exit date what question would you ask to get a list of clients on the first day of the year?

          While you are thinking about that, let’s make a form with two fields. The sample looks like this:

         Nothing fancy here, 2 text boxes for the beginning date and ending date. We named the first one txtbegindate and the other one txtenddate. And we will come back and add the Run Query button a little later. Save the form as MasterDateInput.

          Now, let’s create a query using the dates from this form. Click on queries, new, design view, and OK. Then select the file you are working with and click on OK. Now choose a couple of fields and the entry date and exit date.

          Have you decided on the question that will give us the list of clients that were there on the first date of the operating year? I will give you a hint. We only need the beginning date; I put the ending date on to use this form for other questions. The question to ask is “list all the clients that have an entry date that is less that the beginning date and that the exit date is greater than the beginning date if they left”. As you can see the questions can be as complicated as the SQL behind the queries.

          To convert this for Access, put “<[Forms]![MasterDateInput].[txtBeginDate]” in the Criteria for the beginning date field. In this case a field called NSOEntryDate. This tells Access to retrieve the records that are less than field txtBeginDate from the form MasterDateInput. And enter “>[Forms]![MasterDateInput].[txtBeginDate] Or Is Null “ into the criteria for the exit date. We are just checking that the exit date is greater than the input date or is null.

          Run the query and you will be asked for “Forms!MasterDateInput.txtBeginDate “. Enter the date the program began (07/01/2003) and press enter. If you get an error, fix it and run the query again. Save the query, naming it “qryPartIQuestion2a” and go back to the form MasterDateInput.

          Now let’s add that button to run the query. We have a wizard for that. Select the command button from the tool box and drag and paint in on the form. The wizard will open and you choose miscellaneous for Categories and Run Query from the actions.

Click on next and the following window appears:

Select qryPartIQuestion2a and click on next.



I changed to the text selection and left Run Query as the text on the button. Then click on next.

I changed the button name to “cmdRunQueryButton”. Now size the button and we are ready to save the form and run it to see what happens. If this simple example works you can now enter the beginning date and press the query button and you will get the query on your data.



          This gives us 12 records. That is the number of clients at the beginning of the operating year. I always verify this number to see what I missed. You have to know the answer before you ask the question. There are no null dates here but when I use a date that the clients have not left yet I have to have the null test. Look at the formula in the NSOExitDate in the query to see what I mean. I did not have the null the first time I ran this. Better yet, try this on your data and see if I have an error in my calculations. Will not be the first time.

          The next questions just get harder. With this data, give us ages in ranges, how many single people, how many children and the questions go on for pages. This little project is really a good exercise in building queries. Always check your answers; Access will let you trick yourself. Verify manually your computerized entries.

          Want to take the boredom out of your life? Volunteer your talents and time to an organization of our choice. Take on the project like you were getting graded on professionalism and correctness. I always write up a project goal list and present it before I start these “little” projects. That is the professional thing to do. Try it, you will love it.



James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James.