Re: Parameter query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

A subreport can be called many times (e.g. for each record in the main report), and will ask for its parameter each time.

To avoid that, create a form with text boxes where the user can enter the limiting dates before opening the report. The query can then read the dates from the form as often as it gets called.

This kind of thing:

PARAMETERS [Forms].[Form1].[txtStart] DateTime,
[Forms].[Form1].[txtEnd] DateTime;
SELECT ...
HAVING [Investment Details].DateTransaction
Between [Forms].[Form1].[txtStart]
And [Forms].[Form1].[txtEnd] - 1
ORDER BY ...

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

"DJTI via AccessMonster.com" <u30755@uwe> wrote in message
news:6e1941f03ae6a@xxxxxx
I hope someone can help me with this.

I have a Parameter query that is a subquery for 2 queries for 2 reports.
These reports are run the same time. The problem is the Parameter prompts
before the second report opens for preview. How do I get the parameter prompt
to run only once for the 'subquery' and continue through until the 2 reports
are closed.

The criteria for the parameters to get [LastTransDate] are:
Between [Enter Last Confirm Date] And ([Enter Most Recent Confirm Date]-1)

SQL for subquery:
PARAMETERS [Enter Last Confirm Date] DateTime, [Enter Most Recent Confirm
Date] DateTime;
SELECT [Investment Details].InvestmentID, [Investment Details].
DateTransaction AS LastTransDate, [CumuShares]+nz([CumuShares]*[QDivValue])-
nz([CumuShares]*[RedeembyShare])*[CurShareValue] AS LastTotalShares
FROM Investment LEFT JOIN [Investment Details] ON Investment.InvestmentID =
[Investment Details].InvestmentID
GROUP BY [Investment Details].InvestmentID, [Investment Details].
DateTransaction, [CumuShares]+nz([CumuShares]*[QDivValue])-nz([CumuShares]*
[RedeembyShare])*[CurShareValue], Investment.CumuShares, [Investment Details].
QDivValue, [Investment Details].QCashPdValue, [Investment Details].
CurShareValue, [Investment Details].RedeembyShare
HAVING ((([Investment Details].DateTransaction) Between [Enter Last Confirm
Date] And ([Enter Most Recent Confirm Date]-1)))
ORDER BY [Investment Details].DateTransaction DESC;

Thanks,
Deb

.

Relevant Pages

  • East-West gap in Turkey widening
    ... average per capita income in the provinces of the region is 12 percent ... The report says that despite decades-long attempts to find a remedy to ... That tendency was reversed in the Southeast, ... It also said the current system to encourage investment in the region ...
    (soc.culture.turkish)
  • $$$The Dow increased 203.82 points, or 2.1 percent, to 10,005.96 at 4:01 p.m. in New York for the bi
    ... Nov. 5 -- The Dow Jones Industrial Average rose the most ... topping the highest estimate of economists surveyed by ... investment officer at Baltimore-based MTB Investment Advisors, ... Department report. ...
    (soc.retirement)
  • Re: All losers go to Canada
    ... From Wednesday's Globe and Mail ... The report card, to be released today, compares Canada's performance in ... investment in training is falling. ...
    (soc.culture.argentina)
  • Re: "HP breakup on the way" - Merrill Lynch
    ... Having worked in the investment business for a number of years as an analyst ... existence of the investment research report written by a Merrill Lynch ... to you and your VMS colleagues when there are no bids when the 'VMS For ...
    (comp.os.vms)
  • Policy conflicts in Cambodia: environment, development and energy supply
    ... Original report from Phnom Penh ... At least two large Chinese companies have received investment licenses ... But the investment in Cambodian hydroelectricity is ... "We could not evaluate unless we have studied the environment around," ...
    (soc.culture.cambodia)