Parameter Query and reports

S

Sarah0824

I have a parameter query that I run to pull certain dates (ie - 3/1/08 -
3/31/08) and the name of the event that happened in that date range. The end
result that I want to get is a report that lists these dates (which is easy)
and also same events for the prior month and prior year.

I have tried to create a 2nd query that will pull the event name from the
parameter query, but I am stuck at how to make it look for only those that
are the prior month or prior year. I think the parameter is what is
confusing me because that changes each time the query is run.

If I could get a 2nd query or even a third one, I know I can join them
together and create my report, but I'm stuck in the middle.

Any help is very much appreciated!
 
A

Allen Browne

So you want an query that asks you which month you are interested in, and
then list the previous and next months events as well?

Something like this:

PARAMETERS MonthNumber Long;
SELECT tblEvent.*
FROM tblEvent
WHERE EventDate >= DateSerial(Year(Date()), [MonthNumber]-1, 1)
AND EventDate < DateSerial(Year(Date()), [MonthNumber]+1, 1);
 
S

Sarah0824

Thanks for your quick response. That is close to what I need, but there are
actually two parameters [enter start date] and [enter end date]. The user
specifies the month, date and year for the start and end dates. This is
important becuase the data flows across several years and we only want
certain information from certain years.

Not only do I need the data that falls on the dates between the two
parameters, but I then need the previous months info and the previous years
info.

Does that make sense?

Allen Browne said:
So you want an query that asks you which month you are interested in, and
then list the previous and next months events as well?

Something like this:

PARAMETERS MonthNumber Long;
SELECT tblEvent.*
FROM tblEvent
WHERE EventDate >= DateSerial(Year(Date()), [MonthNumber]-1, 1)
AND EventDate < DateSerial(Year(Date()), [MonthNumber]+1, 1);

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sarah0824 said:
I have a parameter query that I run to pull certain dates (ie - 3/1/08 -
3/31/08) and the name of the event that happened in that date range. The
end
result that I want to get is a report that lists these dates (which is
easy)
and also same events for the prior month and prior year.

I have tried to create a 2nd query that will pull the event name from the
parameter query, but I am stuck at how to make it look for only those that
are the prior month or prior year. I think the parameter is what is
confusing me because that changes each time the query is run.

If I could get a 2nd query or even a third one, I know I can join them
together and create my report, but I'm stuck in the middle.

Any help is very much appreciated!
 
A

Allen Browne

Perhaps you could get the user to enter the starting date, e.g. 1/1/2009:

PARAMETERS StartingDate AS DateTime;
SELECT tblEvent.*
FROM tblEvent
WHERE (EventDate >= DateAdd("m", -1, [StartingDate])
And (EventDate < DateAdd("m", 2, [StartingDate]);

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sarah0824 said:
Thanks for your quick response. That is close to what I need, but there
are
actually two parameters [enter start date] and [enter end date]. The user
specifies the month, date and year for the start and end dates. This is
important becuase the data flows across several years and we only want
certain information from certain years.

Not only do I need the data that falls on the dates between the two
parameters, but I then need the previous months info and the previous
years
info.

Does that make sense?

Allen Browne said:
So you want an query that asks you which month you are interested in, and
then list the previous and next months events as well?

Something like this:

PARAMETERS MonthNumber Long;
SELECT tblEvent.*
FROM tblEvent
WHERE EventDate >= DateSerial(Year(Date()), [MonthNumber]-1, 1)
AND EventDate < DateSerial(Year(Date()), [MonthNumber]+1, 1);

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sarah0824 said:
I have a parameter query that I run to pull certain dates (ie - 3/1/08 -
3/31/08) and the name of the event that happened in that date range.
The
end
result that I want to get is a report that lists these dates (which is
easy)
and also same events for the prior month and prior year.

I have tried to create a 2nd query that will pull the event name from
the
parameter query, but I am stuck at how to make it look for only those
that
are the prior month or prior year. I think the parameter is what is
confusing me because that changes each time the query is run.

If I could get a 2nd query or even a third one, I know I can join them
together and create my report, but I'm stuck in the middle.

Any help is very much appreciated!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top