Kurt,
My proposed solution does not require any code, and is different to Peter's
in that it assumes a form that is already open before you run the report,
whereas in Peter's solution the form is opened by the report itself and
"lives" until the report is closed. My solution would be more advantageous
if the user needed to supply a number of criteria and run the report
repeatedly with only some of them changing from run to run, in that they
wouldn't have to enter all the criteria every time, but in your case I
suppose Peter's approach is just as good (at least).
If I may jump in for Peter, just in case he doesn't see your follow-up post:
While in report design view, select he whole report and display Properties,
tab Event (correct guess). Place the cursor next to On Open and click on the
little button that appears on the right hand side, to invoke the VBA editor.
You will see the procedure header and footer, like:
Private Sub Report_Open(Cancel As Integer)
End Sub
Paste the following in between:
DoCmd.OpenForm "FTE Yearly Information", acNormal, , , , acDialog
Repeat for the report's On Close event, and paste the following code:
DoCmd.Close acForm, "FTE Yearly Information", acSaveNo
Then open the form in design view, and paste the following code behind the
command button's Click event:
Me.Visible = False
The criteria in your query feeding the report must be:
....Between Forms![FTE Yearly Information].[txtStartPeriod] AND Forms![FTE
Yearly Information].[txtFinishPeriod]
where I have assumed the two controls for the date range on the form to be
named txtStartPeriod and txtFinishPeriod.
HTH,
Nikos
Kurt said:
Thanks Peter and Nikos.
Is what you are talking about within the 'On Open' event in report
properties -> Event? I spose i have to use the code builder to create a
dialog of the form. Exactly what procedure do i take to make this happen? I
also believe that the values entered into the form that has StartPeriod and
FinishPeriod will be used within the report, which is displayed after the
form dialog is hidden? For example, say the form is called 'FTE Yearly
Information'.
If you could outline the steps I am to take, or guide me to a web site that
can outline me the solution, ill be very gratefull! I have a bit of knowledge
about access, and programming languages, im just not really proficient with
access yet
Peter R. Fletcher said:
You would probably be better to create a Form for entering StattPeriod
and FinishPeriod, and open it (as a Dialog) from the Open Event of
your Report. A button on the Form would make it invisible and return
from the Dialog call, but the two time fields would remain in scope
until the Form is closed (which it could be in the Report's Close
Event).
Hey all,
Right now i have two fields within a query that is assigned a value from a
text box. For example: StartPeriod: [Enter starting period], FinishPeriod:
[Enter finishing period]. Now, i want to store these values (really only the
FinishPeriod value), for processing requirements for a report, but also to
define criteria for my Time ID field within the query (Time ID is a table
field). However, when i use:
Between [StartPeriod] and [FinishPeriod], it displays new message boxes
Is there any way to do this? Really, i want to store these values for use in
a report. What i have set up now is a starting date value in my table, and
the FinishPeriod is used to calculate an amount up to this period. I hope my
question is not too confusing!
Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
