Filtering

[DebbieV]

I want a user to be able to enter a date range on a form and hit the command button to bring up a report that will only display data based on that date range. I have tried creating a macro to make this happen. The macro and query will run find alone, When I bring up the form and enter the date range it will bring up the report but not show the correct data. I keep thinking there must be some properties I am not setting correctly on the Date Rage form but I am not finding anything left out. I tried following an example of this on the Northwinds Database (Sales by Year Dialog) I am at a lost, any directions you can offer me on accomplishing this task will be most helpful.
Debbie

 

[ddmcn]

Are you using the between operator to establish the range?

Does your code refer to the controls on the form itself...rather than the table the dates reside in?

Dennis

 

[DebbieV]

Hi Dennis,

I did used the Between operator. but I don't have any code anywhere on the form. I just selected the query as the control source. In which I typed the following in the Order Date column in the criteria: Is Not Null And Between[Forms]![Form1]![BeginningDate] And[forms]![Form1][EndingDate]

Debbie

 

[ddmcn]

Hi, Debbie...I think I have a solution.

Delete the where condition in your query.

Create a macro to open the report...having the where condition:

yourtable.datefield between form.date1 and form.date2

Attach the macro to the OnClick event on your forms button.

The report is still based on the query.

Let me know if this works...I got it to work here.

Bon chance!

Dennis

 

[DebbieV]

ok Dennis,

I think I may not have the syntex correct. I should enter in the where condition of the macro the
[tablename]![fieldname] Between [forms]![formname]![date1] and [forms]![date2].
Right name I am getting an error message but I think I am confused as to the correct syntex in the where condition.

Debbie

 

[ddmcn]

Hi, Debbie

Your where condition looks okay except that you don't have [formname] in the phrase identifying [date2]...what is the error message you are getting?

Dennis

 

[DebbieV]

Hi Dennis,
The error message says; This action can't be carried out while processing a form or report event.
A macro specified as the OnOpen, OnClose, OnFormat, OnRetreat, OnPage or OnPrint property setting contains an invalid actionf or the property.
For the macro I have;
Name OpenDialog Action OpenReport
OK Set Value
Cancel Close

I really appreciate you spending the time and helping me through this.

Debbie

 

[ddmcn]

No worries, Debbie...the forum provides a great place to learn for me as well. Glad to offer assistance.

It looks like you're using a dialog box for this...is the dialog box apart from the form that the dates are input on?

Name OpenDialog Action OpenReport
OK Set Value
Cancel Close

The way that I got this to work was that I created a macro called OpenReport. The macro only has one action, OpenReport, and the following properties:

Report Name
[YourReportName]

View
PrintPreview

Where Condition
[YourTableName]![YourDateField] Between [Forms]![YourInputFormName]![YourDate1] and [Forms]![YourInputFormName]![YourDate2]

I then created a form with two unbound fields, lets call them YourDate1 and YourDate2. I also put a command button on the form; we'll call it cb_Print_Report. In the OnClick property for cb_Print_Report I put the macro name OpenReport.

I then saved the form and then reopened it. I inserted a date in the YourDate1 field and a later date in the YourDate2 field. Pushing the button cb_Print_Report should present the report in a preview window; allowing the user to click on the print button to print the report or close to window to not print.

Hope this makes sense

 

[DebbieV]

Hey Dennis,

I finally got it to work with the help of your great directions.
Thank you very much. This is such a relief. I guess you know what it is like when it finally all comes together.

Thanks Again
Debbie V

 

[ddmcn]

Sure thing, Debbie.

Have a good weekend.

Dennis