Parameter query

[DJTI via AccessMonster.com]

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

 

[Allen Browne]

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

 

[DJTI via AccessMonster.com]

Thank you for responding Allen. I did find previous posts from you addressing
this matter, but it hasn't worked for me yet. I do have a few questions...

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

First let me point out that these are not subreports...they are two reports
running simultaneously. Am I trying to do something wrong by having 2 reports
open at the same 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.

1. On the Form, is the Record Source the parameter query?
2. Are the text boxes Unbound? If not what is the Control Source?
3. In the query there is ONE Field called "LastTransDate", Criteria is
"Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]-1" (without
quotes for both). How does the two txtbox entry dates point to txtStart and
txtEnd parameters?

Thanks again.
Deb

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

I hope someone can help me with this.

[quoted text clipped - 33 lines]

Thanks,
Deb

 

[Allen Browne]

Replies embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for responding Allen. I did find previous posts from you
addressing
this matter, but it hasn't worked for me yet. I do have a few questions...



First let me point out that these are not subreports...they are two
reports
running simultaneously. Am I trying to do something wrong by having 2
reports
open at the same time?

There's nothing wrong with having 2 reports open at once.

The suggested technique can be used for 2 standalone reports just as well as
for a report and subreport.

1. On the Form, is the Record Source the parameter query?

No. The paramater report is the Record Source for your report(s).

The form is already open, and has the dates present. When you run the
report, the Expression Service in Access attempts to evaluate the parameter
before popping up the parameter dialog. If Form1 is open, and has a text box
named txtStart on it, than the ES exaluates the expression
[Forms].[Form1].[txtStart] as the value of the text box, and supplies that
value to the query.

2. Are the text boxes Unbound? If not what is the Control Source?
Unbound.

3. In the query there is ONE Field called "LastTransDate", Criteria is
"Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]-1"
(without
quotes for both). How does the two txtbox entry dates point to txtStart
and
txtEnd parameters?

In the Criteria row in query design, under LastTransDate, you entered the
expression:
Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]-1

Therefore the LastTransDate field must be between the values in the 2 text
boxes in order for the record to be returned to the query (and hence to the
report based on the query.)

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

I hope someone can help me with this.

[quoted text clipped - 33 lines]

Thanks,
Deb

 

[DJTI via AccessMonster.com]

Allen,
The error I get at the for the parameter query is:
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Thry simplifying the expression by assigning parts of the expression to
variables.

Thanks again,
Deb

Replies embedded.

Thank you for responding Allen. I did find previous posts from you
addressing

[quoted text clipped - 8 lines]

reports
open at the same time?

There's nothing wrong with having 2 reports open at once.

The suggested technique can be used for 2 standalone reports just as well as
for a report and subreport.

1. On the Form, is the Record Source the parameter query?

No. The paramater report is the Record Source for your report(s).

The form is already open, and has the dates present. When you run the
report, the Expression Service in Access attempts to evaluate the parameter
before popping up the parameter dialog. If Form1 is open, and has a text box
named txtStart on it, than the ES exaluates the expression
[Forms].[Form1].[txtStart] as the value of the text box, and supplies that
value to the query.

2. Are the text boxes Unbound? If not what is the Control Source?
Unbound.

3. In the query there is ONE Field called "LastTransDate", Criteria is
"Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]-1"
(without
quotes for both). How does the two txtbox entry dates point to txtStart
and
txtEnd parameters?

In the Criteria row in query design, under LastTransDate, you entered the
expression:
Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]-1

Therefore the LastTransDate field must be between the values in the 2 text
boxes in order for the record to be returned to the query (and hence to the
report based on the query.)

This kind of thing:
[quoted text clipped - 11 lines]
Thanks,
Deb

 

[Allen Browne]

The message indicates that JET is having a hard type matching the data types
of your expressions.

You may need to explicitly typecast them as described here:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
The error I get at the for the parameter query is:
This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Thry simplifying the expression by assigning parts of the expression to
variables.

Thanks again,
Deb

Replies embedded.

Thank you for responding Allen. I did find previous posts from you
addressing

[quoted text clipped - 8 lines]

reports
open at the same time?

There's nothing wrong with having 2 reports open at once.

The suggested technique can be used for 2 standalone reports just as well
as
for a report and subreport.

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.

1. On the Form, is the Record Source the parameter query?

No. The paramater report is the Record Source for your report(s).

The form is already open, and has the dates present. When you run the
report, the Expression Service in Access attempts to evaluate the
parameter
before popping up the parameter dialog. If Form1 is open, and has a text
box
named txtStart on it, than the ES exaluates the expression
[Forms].[Form1].[txtStart] as the value of the text box, and supplies that
value to the query.

2. Are the text boxes Unbound? If not what is the Control Source?
Unbound.

3. In the query there is ONE Field called "LastTransDate", Criteria is
"Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]-1"
(without
quotes for both). How does the two txtbox entry dates point to txtStart
and
txtEnd parameters?

In the Criteria row in query design, under LastTransDate, you entered the
expression:
Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]-1

Therefore the LastTransDate field must be between the values in the 2 text
boxes in order for the record to be returned to the query (and hence to
the
report based on the query.)

This kind of thing:

[quoted text clipped - 11 lines]

Thanks,
Deb

 

[DJTI via AccessMonster.com]

Hi Allen,
I read your link concerning typecast. Then I removed the -1 in the parameter:
Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]-1

and it worked. The problem is I don't want to include the date [txtEnd]. How
do I get the desired results?

Thanks,
Debra

The message indicates that JET is having a hard type matching the data types
of your expressions.

You may need to explicitly typecast them as described here:
http://allenbrowne.com/ser-45.html

Allen,
The error I get at the for the parameter query is:

[quoted text clipped - 65 lines]

 

[Allen Browne]

You could try:
DateDiff("d", -1, [Forms].[Form1].[txtEnd])
or
CVDate([Forms].[Form1].[txtEnd] - 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,
I read your link concerning typecast. Then I removed the -1 in the
parameter:
Between [Forms].[Form1].[txtStart] And [Forms].[Form1].[txtEnd]-1

and it worked. The problem is I don't want to include the date [txtEnd].
How
do I get the desired results?

Thanks,
Debra

The message indicates that JET is having a hard type matching the data
types
of your expressions.

You may need to explicitly typecast them as described here:
http://allenbrowne.com/ser-45.html

Allen,
The error I get at the for the parameter query is:

[quoted text clipped - 65 lines]

Thanks,
Deb

 

[DJTI via AccessMonster.com]

Allen,
Thanks for all your help! CVDate([Forms].[Form1].txtEnd] -1) did the trick.
Deb

You could try:
DateDiff("d", -1, [Forms].[Form1].[txtEnd])
or
CVDate([Forms].[Form1].[txtEnd] - 1)

Hi Allen,
I read your link concerning typecast. Then I removed the -1 in the

[quoted text clipped - 20 lines]