From Date x to Date y (Query table data)

[Paul (ESI)]

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.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Chaim]

Comments embedded.
------------------------

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.

Select <list of fields you want to display> from [Your Table]
where [the date field] between [Enter your Start Date] and [Enter your Stop
Date];

Assuming that your date/time field is not named [Enter your Start/Stop
Date], Access will ask you to input values for these. Basically parameters
to the query.

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.

These sound like options that you would need a form to collect those
criteria and then programmatically read the form, format the various fields
into the query string and run it. Options are not a feature of queries. They
can prompt you for input, but unless you are coding all of these options
into the argument strings that will be presented in the popup- NOT A
RECOMMENDATION - there is no way.

 

[Steve Schapel]

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.

 

[Paul (ESI)]

where [the date field] between [Enter your Start Date] and [Enter your Stop
Date];

Assuming that your date/time field is not named [Enter your Start/Stop
Date], Access will ask you to input values for these. Basically parameters
to the query.

I was able to get this to work for the query that reads directly from the
table. The problem is, this query is then used for a crosstab query, which is
then used for another query that gets the final calculation we need. I was
assuming that, since the first query now asks for a date and the other
queries are dependant on it, the other queries would prompt you for the dates
as well. Not so. When I tried to run the third and final query, it just says
"The Microsoft Jet database engine does not recognize '[Enter your start
date]' as a valid field name or expression."

How can we get this to work? Ultimately, the only query we actually need to
run and see is this final query. The query that comes directly from the
table, and the crosstab query from that are both just there to get the data
to a point where it can be queried for the calculation we need (the third
query). I tried, instead, putting your suggested WHERE clause in the final
query, but it gives the same error then.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Paul (ESI)]

Cool, thanks. I'll give that a try too. I'll get back to you when I try it out.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

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

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.

 

[Paul (ESI)]

Crud, wait... I forgot one important detail that I think may have made a
difference. None of the queries show the dates, the dates are only in the
table. This is because we want the totals for each category for the given
time frame. If we had the dates in the queries, it would put totals for each
date separately instead of for the entire time frame.

It sounded to me like your suggestion was dependant upon the queries having
the date in them. Was I wrong? Would your suggestion still work? If not, can
anybody else tell me how to achieve what I am trying to do. As I said in
response to Chaim, another problem is that the query I actually need to run
and see is a third level query. One query queries the table. A crosstab query
reads from that query to get all the data in a format I need. Then, the final
query does my calculation from the crosstab query. That final query is the
only one I need to actually see.
--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

Cool, thanks. I'll give that a try too. I'll get back to you when I try it out.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

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

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.

 

[Steve Schapel]

Paul,

No, the date field does not need to be included in the data returned by
the query. The approach I suggested should be fine. The date field
needs to be included in the query design of the first query, but it is
in the Where clause, not necessarily in the Select clause, hope you know
what I mean. And since your second and third queries are based on the
first query, then the date criteria in the first query should be
automatically carried through to the data returned via the third query,
with no further intervention on your part. There should be no problem
with this. Even though I would personally not consider the approach
suggested by Chaim - I do not consider a Parameter Query a suitable
solution except in very limited circumstances - nevertheless, the carry
through of the criteria should also apply in his example.

 

[John Spencer (MVP)]

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

 

[Paul (ESI)]

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Thanks again for your help, everybody. I'm still a bit confused because I
have little experience with doing this in Access. What is the parameter I
should enter? Is that where I want to enter the prompt for dates? You say to
fill in the EXACT name of the parameter. What do you mean by this? Should I
be entering exactly the name of the date field in the table, or "Enter your
start date" like the pop up will say? Also, do I need to put two parameters
then? Start date and end date, or am I just putting the date field from the
table and it is only one?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Paul (ESI)]

Thanks again for your help! I'm giving your suggestion a try along with John
gave me. I hope to figure it out. Maybe I'll even figure out the answers to
the questions I just aksed. You've all been a big help.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Paul (ESI)]

Cool. Okay, I started trying out the new suggestions. Going into the crosstab
query, I defined the parameters of "Enter your Start Date," and "Enter your
Stop Date." This worked fine. One slight problem, (VERY minor problem, no big
deal if there is no way around this) the final query asked for the start and
stop date twice. Anyway to make sure it only asks for it once?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Paul (ESI)]

AWESOME! Tried your suggestion, it worked too! Thanks again for your help! I
get the same problem as when trying Chaim's suggestion, though (which, again,
isn't such a big deal). It asks for the start date and stop date twice. Any
way to stop this? Also, any way to change what the pop up window says? Right
now, it just says "[Forms]![TestFrm]![Start Date]" and
"[Forms]![TestFrm]![Stop Date]." Is there any way to change it to say
something like "Enter start date," "Enter end date," even with the parameters
still being the dates from the form?

No big deal if this cannot be done, or is more difficult than it is worth.
This is DEFINITELY not of major importance since most folks using the
database will use the form instead of directly running the query anyway.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor

 

[Steve Schapel]

Paul,

Then general idea here is that you have a form where you enter the
dates, and the query gets the values from there, so... there should be
no "pop-up window", you shouldn't get asked for the start date and stop
date twice, you shouldn't even get asked for them once. I think this
might relate to what John Spencer said... with a crosstab query you have
to specify the parameters in the query design. Go to the design view of
the crosstab query, go to the Query menu, and select Parameters. Then,
you can enter [Forms]![TestFrm]![Start Date] in the Parameter column,
and Date/Time in the Data Type column, and the same for the Stop Date.