Re: Altering a reports source
- From: Steven Sutton <StevenSutton@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 7 Feb 2008 07:14:01 -0800
Thanks Allen, that did the trick.
"Allen Browne" wrote:
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
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Steven Sutton" <StevenSutton@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7B15E737-0D3C-47BA-8C88-51CD6823E722@xxxxxxxxxxxxxxxx
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!
- References:
- Re: Altering a reports source
- From: Allen Browne
- Re: Altering a reports source
- Prev by Date: Re: Report for single record
- Next by Date: Re: Invalid string in expression builder
- Previous by thread: Re: Altering a reports source
- Next by thread: Re: Report for single record
- Index(es):
Relevant Pages
|
