Need parameter w/calculated default

[Guest]

Hi there everyone! Using A02 on XP. Love all the help I get here. Couldn't
learn so much anywhere else!

I have a report query that is pulling a PlanYearEnd Date from my table and I
am calculating the PlanYearBegin Date with this in my query grid field lines:

FieldLine Result-Example

PYE '04/30/2006

PYB1: DateAdd("d",1,[PYE]) 'adds one day - 05/01/2006

PYB: DateAdd("yyyy",-1,[PYB1]) 'minus one year - 05/01/2005

But, the question is this - how can I allow the user to accept the
calculation of 05/01/2005 or change it just in case it was a short plan year?
I need to print both a Plan Year Beginning and Plan Year End date in my
report.

Must I create another query? Can't I ask it in this query? Hope I've
described it well enough.

Also, don't want to push my luck but I'm positive I could do the above PYB1
and PYB in one step but not sure how to nest it. Would love to do that also.

Thanks in advance for any time or advice you can provide.

 

[Allen Browne]

Query parameters are not powerful enough to do what you are asking. They
lack defaults, validation, range checking, pick lists, and lots of other
things that you can do with forms.

Most professional developers don't use query parameters for these reasons,
as well as others, such as the annoyance of handling a whole series of
dialogs, and the inefficiency of handling the cases where the user does not
want to enter something for all the parameters the query offers. Instead, we
use forms where all these things can take place.

Create the form. Calculate the default in the Load event of the form, and
assign it to the text box. Then either have the query read the value from
the form, e.g.:
[Forms].[Form1].[txtPlanYearEnd]
or else apply the result directly from the form, (e.g. to the Filter of the
form, or the WhereCondition of OpenReport.)

 

[Guest]

Thanks Allen. I use forms for that purpose frequently, just wanted to avoid
another field on my form.
--
Bonnie

Query parameters are not powerful enough to do what you are asking. They
lack defaults, validation, range checking, pick lists, and lots of other
things that you can do with forms.

Most professional developers don't use query parameters for these reasons,
as well as others, such as the annoyance of handling a whole series of
dialogs, and the inefficiency of handling the cases where the user does not
want to enter something for all the parameters the query offers. Instead, we
use forms where all these things can take place.

Create the form. Calculate the default in the Load event of the form, and
assign it to the text box. Then either have the query read the value from
the form, e.g.:
[Forms].[Form1].[txtPlanYearEnd]
or else apply the result directly from the form, (e.g. to the Filter of the
form, or the WhereCondition of OpenReport.)

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

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

Hi there everyone! Using A02 on XP. Love all the help I get here. Couldn't
learn so much anywhere else!

I have a report query that is pulling a PlanYearEnd Date from my table and
I
am calculating the PlanYearBegin Date with this in my query grid field
lines:

FieldLine Result-Example

PYE '04/30/2006

PYB1: DateAdd("d",1,[PYE]) 'adds one day - 05/01/2006

PYB: DateAdd("yyyy",-1,[PYB1]) 'minus one year - 05/01/2005

But, the question is this - how can I allow the user to accept the
calculation of 05/01/2005 or change it just in case it was a short plan
year?
I need to print both a Plan Year Beginning and Plan Year End date in my
report.

Must I create another query? Can't I ask it in this query? Hope I've
described it well enough.

Also, don't want to push my luck but I'm positive I could do the above
PYB1
and PYB in one step but not sure how to nest it. Would love to do that
also.

Thanks in advance for any time or advice you can provide.