Parameter Query and reports

[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!

 

[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);

 

[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?

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.

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!

 

[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.

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?

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.

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!