Date Range in Macro

[danbl]

Access 2000?
I have three different reports which all work off a user specified date range. I have created a macro to run them and as long as I specify "Between And" conditions in the queries behind them the macro works fine, but you have to enter the date range three different times.

Is there a way to specify the date range once in the macro so that all the reports run for that date range???

Hope someone will help!!!

 

[SOS]

Use a FORM for input and then refer to THAT for the values.

 

[danbl]

Used the form and still requires to enter the date each time for each report. On the form I have a start and end date, then in the individual queries I used a "between [start date] and [end date]" expression which refers to the form fields respectively.

Why still requiring to enter dates multiple times??

 

[DCrake]

Read this Post it will give you all the information you need to accomplish what you want.

 

[danbl]

Thanks, but I am by no means a programer!! I follow what to enter into the individual queries, but am lost about creating the startup module and creating the new functions??

Can you guide me through this process?

 

[SOS]

Why still requiring to enter dates multiple times??

Because you need to use the FORM referencing instead of just [Enter Start], etc.

Form referencing is like:

[Forms]![YourFormNameHere]![YourControlNameHere]

 

[danbl]

Here is the command I tried.

Forms![Date Range Form]![Start Date] and Forms![Date Range Form]![End Date]

I placed this in the individual queries in the "Date" field criteria. When I execute the macro it steps through requiring to enter the dates 3 consecutive times to get the reports.

On the form I have two elements that are unbound; one is the start date and the other is the end date. When entering the end date it goes back to the start date. Not sure why it is not proceeding to the rest of the macro???

Is the issue in the Form and / or the macro??

 

[SOS]

If you have this as criteria in your query and it still pops up the parameter dialog, then you have something spelled wrong or are referencing an object that is not open.

Perhaps you can post your database here and we can take a quick look.

 

[Brianwarnock]

I didn't understand this

Not sure why it is not proceeding to the rest of the macro???

Are you running the macro from a command button on the form? That is the easy way to do this
I presume that you meant

Between Forms![Date Range Form]![Start Date] and Forms![Date Range Form]![End Date]

SOS is correct in that you should check the spelling and also ensure that the form remains open, using a command button ensures that of course.

Brian

 

[danbl]

Okay ... how do I post htis for you to look at??

 

[SOS]

Okay ... how do I post htis for you to look at??

See here for how to post a database to the forum.

 

[danbl]

Okay ... here it is.

I did not see any error but another set of eyes are appreciated. Don't laugh to hard ...just learning.

 

[SOS]

Okay, which queries are popping up the box?

Are these the ones?
q_Perioperative_Data_Query
q_DVT_Data Query
q_Complete_Advanced_Directive Query

 

[Brianwarnock]

What is happening is that the form is being opened and the queries /reports run.
The switchboard needs to open the Form, and after the dates have been entered a command button on the Form fires the macro to run the queries/reports, the macro could then close the Form.

Brian

 

[danbl]

The three queries that need to run to populate the reports are:

q_Advanced_Directive All Query
q_Advanced_Directive PAT Query
q_Advanced_Directive Phone Query

The others can run independently for Data Editing purposes from their associated Edit Forms.

 

[SOS]

okay, so Brian gave the answer then.

 

[danbl]

Ok .... then how do you make the command button trigger the macro? And how does that solve having to enter the dates several times??

 

[danbl]

I figured out the command button and told it to run the macro, however visually nothing happens. The reports if you click on the macro itself are there but do not open in print preview as designed. I have an "open report" after the run command but that makes no difference and finally it does not close the form at the end.

 

[danbl]

Finally I figured out the issue with the reports .... and now they work!!!!!

The only piece not working is the closing the form .... I get an error "This action can not be carried out while processing a form or report event". I click OK and the following Action Failed dialog box appears:
Macro Name = Identifies my macro
Condition = true
Actiong Name = Close
Arguments = Form, Data Range Form, Prompt

How can this be fixed??

Thanks for all your help.

 

[Brianwarnock]

Its years since I used macros and it is possible that I suggested doing something that is not allowed. I'll have to try and find time to design a test. It maybe that you will just have to close the form manually after you have run the reports.

Brian