Altering a reports source

[Steven Sutton]

I have a report which is based on a query. The query is this:

SELECT DISTINCT tblJobTickets.JobNumber, tblJobTickets.JobName,
tblJobTickets.intJobID, Max(tblJobTickets.TicketDate) AS MaxOfTicketDate,
Max(tblJobs.intQuantity) AS MaxOfintQuantity

FROM tblJobTickets INNER JOIN tblJobs ON tblJobTickets.intJobID =
tblJobs.intJobID

GROUP BY tblJobTickets.JobNumber, tblJobTickets.JobName,
tblJobTickets.intJobID

What I would like to do is to limit the Report to a particular range of
dates upon opening. Specifically, the TicketDate field. When I changed the
query to be Between certain dates I got an SQL statement that added the
following line:


HAVING (((Max(tblJobTickets.TicketDate)) Between #1/1/2008# And #1/31/2008#));

The Query worked fine but I need to set the dates when the report is opened.
If it is possible to restrict the Report's range of records retrieved by
using a DoCmd and a Where clause I haven't figured out the correct Where
clause yet. Can someone tell me how to restrict the records retrieved by the
Report?

Thanks in advance!

 

[Allen Browne]

Try something like this:
Dim strWhere As String
strWhere = "MaxOfTicketDate Between #1/1/2008# And #1/31/2008#"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

That should give you the same results.

Of course, this does not give you a report only the ticket sales for
January. For example, if job number 19 had tickets dates April 1 2007,
October 1 2007, and January 1 2008, the values from all 3 tickets would be
in the report (since the most recent ticket date is in January.) If you
wanted the report limited to only those tickets dated in January, use WHERE
instead of MAX under the TicketDate, and have the query read the values from
a form with a couple of unbound text boxes where you enter the limiting
dates before you open the report. Example in Method 2 here:
http://allenbrowne.com/casu-08.html

 

[KARL DEWEY]

Try this ---
SELECT .....
FROM ....
WHERE Max(tblJobTickets.TicketDate) Between [Enter start date m/d/yyyy] AND
[Enter end date m/d/yyyy]
GROUP BY ...

 

[Steven Sutton]

Thanks Allen, that did the trick.

Try something like this:
Dim strWhere As String
strWhere = "MaxOfTicketDate Between #1/1/2008# And #1/31/2008#"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

That should give you the same results.

Of course, this does not give you a report only the ticket sales for
January. For example, if job number 19 had tickets dates April 1 2007,
October 1 2007, and January 1 2008, the values from all 3 tickets would be
in the report (since the most recent ticket date is in January.) If you
wanted the report limited to only those tickets dated in January, use WHERE
instead of MAX under the TicketDate, and have the query read the values from
a form with a couple of unbound text boxes where you enter the limiting
dates before you open the report. Example in Method 2 here:
http://allenbrowne.com/casu-08.html