Re: From Date x to Date y (Query table data)

Paul,

You need to make a form for this purpose, with a couple of unbound textboxes for the entry of the date range criteria. Then, in the Criteria of the date field in the query, you put the equivalent of...
Between [Forms]![NameOfForm]![DateFrom] and [Forms]![NameOfForm]![DateTo]
.... where DateFrom and DateTo are the names of the textboxes.

Of course, it means the form has to be open at the time that the query/report is run.

It is relatively easy to set up customised date ranges, to shortcut the process. Put a command button on the form for each pre-set range you want, and then on the click event of the button, something like this...

 example: month to date
 Me.DateFrom = Date - Day(Date) +1
 Me.DateTo = Date

 example: year to date
 Me.DateFrom = DateSerial(Year(Date),1,1)
 Me.DateTo = Date

.... etc

As an alternative user interface for setting your custom date ranges, you can use an Option Group with one button for each type, and then just one Command Button to set the date range based on the option group selection.

--
Steve Schapel, Microsoft Access MVP


Paul (ESI) wrote:
I am wondering if it is possible to use a query to pull data from a table, but only for a specific time frame. The query we are creating will pull data from the table to sum up several fields (the data is based on employees, and we are querying to get totals for the supervisor). The table has a date entered next to each entry. I am wondering if there is a way to put something in the query that will make a popup window ask for a beginning date and ending date, and then pull only the records in the table that show a date in that range. This way, you could use the same exact query (or a report based on it) to see different time frames, instead of having to create several tables, and several queries for all the different time frames.

Also, is it possible to have the query give you options, such as choosing month to date (it will pull up all info from that month so far), previous month (all info from the past month), year to date (all info from that year so far), and the ability to enter your own range, as I described above. Can this be done? Any help you can give me would be greatly appreciated.

.

Loading