Time Frame

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following in a query that my report is based off of

Between [Forms]![frmEventsCalendar].[EStart] And
[Forms]![frmEventsCalendar].[EEnd]

this works great, I get the count of Product Events, Docks and so on in my
report for the given time frame but now my boss wants my report to look
backwards as well

ex. time frame
7/1/2007 thru 8/1/2007 and it works perfect we get the exact info we are
looking for but now my boss want to select a time frame get the info in the
time frame as well as 30 days prior to the time frame. I have tried to put
-30 after [EStart] and [EEnd] and other places but I just can not figure out
how to get the prior 30 days. I know that if this gets figured out he will
surly want the total for the year and then the total for last year all in one
report but I can only do what I can.

Would a union query be better or something else possibly?

Any help is Greatly Appreciated
 
The solution I would go for involves changing the query on which the report
is based.

I assume you go from a form to the report by clicking a button.

On the form add a unbound text box into which you add the number of days
before and after. for this example I have called it "txtdays" with a default
of 0 (zero)

When you click the button to preview the report, code changes the query so
the dates to and from are adjusted for the extended window.

between #" & Forms]![frmEventsCalendar].[EStart] - me.txtdays & "# and #" &
[Forms]![frmEventsCalendar].[EEnd] + me.txtdays & "#

so it would look something like this

dim qdf as querydef

set qdf = currentdb.querydefs("<the query for the report>")

qdf.sql = "SELECT mailings.*, mailings.Dateadded FROM mailings WHERE
(((mailings.Dateadded) Between #" & Forms]![frmEventsCalendar].[EStart] -
me.txtdays & "# and #" & [Forms]![frmEventsCalendar].[EEnd] + me.txtdays &
"#));
(changing the name of the table and field to suit your query)

set qdf = nothing

then onto opening your report.

Trust this helps.
 
Back
Top