Monthly Reports

[cathyb569]

Hi There

I want to run a commission report but which is filtered on a monthly basis. I have a date field in the report but when I run it I want to tell the database that I would like to run it for the month of September, is there a formula for doing this or some kind of filter I can apply to the existing report?

Thanks

 

[Kryst51]

In the query that the report is using you can specify the dates using "BETWEEN" or >= YourStartDate and <= YourEndDate. You can even make it pop up a box that will ask you for the dates if you put brackets around the words like >=[Start Date] and <= [End Date]. (Called a parameter query, I believe.)

 

[cathyb569]

I dont use a query to run the report, i created the report directly from a table and just removed some fields, then I set up a macro just to open the report....

could you tell me how i create and integrate the query into the report?

 

[Kryst51]

Sure.

1. Open your report in Design view.
2. Right click on the square to the top left and click on the properties option.
3. Click on the "Data" Tab.
4. Click into the "Record Source" line.
5. To the right their should be a button with three dots on it. "..." Click it.
6. It will ask you if you want to create a query based on the table. Click yes.
7. The query builder will pop up with your table in it.
8. Drag the fields you want onto the grids below if they are not already there.
9. In your date column in the criteria section you can type what I put above. See my attachment. My example will prompt you for dates if you open the report, but you can put whatever dates you want into the query. Or even design a report where you can type the dates in, press a button and the report will open with only those dates in it.
10. Close the query, It will ask if you want to save the changes, click yes. Save your report.

Please let me know if you have any questions.

 

[cathyb569]

Hi - OK, i have tried to enter =[Start Date] And [End Date] in the date field and when I run the report it asks me for both of these dates, which is perfect. The dates I enterred were 01/08/2009 -- 30/08/2009. But, when the repot opened it still showed all of the records in the database (not just the ones between those dates), and, it changed the dates in the report to 29/12/1899 ??

Any ideas?

Thanks a mil for your help

 

[Kryst51]

Hi - OK, i have tried to enter =[Start Date] And [End Date] in the date field and when I run the report it asks me for both of these dates, which is perfect. The dates I enterred were 01/08/2009 -- 30/08/2009. But, when the repot opened it still showed all of the records in the database (not just the ones between those dates), and, it changed the dates in the report to 29/12/1899 ??

Any ideas?

Thanks a mil for your help

It's reading the date as mm/dd/yyyy and you are typing it in as dd/mm/yyyy. I am not sure (Although I know it is possible) how to get it to read the way you want.

Any ideas anyone else how to make it read the typed in date differently?

 

[srs09]

Hey there,

Since you are doing a monthly report based solely on month, why not simply let the user enter a month and a year, so that the records from those month and year (say, April 2008) would show up without having the user having to enter the first and last date of the month in question?

To do this, in your query, you need to create two columns, one for the month criteria, and one for the year criteria.

The month column
Month: Format([Date],'MMMM')

Criteria for the month column:
[Enter Month]

The Year Column
Year: Year([Date])

Criteria for the year column:
[Enter Year]

*"Date" is the date field you use for filtering results

When you run the query, you should be prompted for the month name and the year.

Additionally, when you have time, you can make it even more user friendly, like building a small user interface where the user doesn't have to type in the months or year, but rather select them from a drop down list...etc.

Hope this helps!