Establishment of a free accounting date report

Microsoft Access reports provide statistics grouped by date, but only for full years, months, days, and so on, that is to say one months of data must be from the beginning of the month to the end of the month.

We write programs, there are many statistical systems in the work, and the units of the statistics are based on the accounting date, the accounting date is not necessarily from 1st to 31st, and some companies from the last month of 26th to this month's 25th counted one months, so that the Report Wizard automatically generated by Access report will not be. Of course, or we do it ourselves, in fact, very simple, a learning will.

1. Recognize several functions related to time
2. Create a form that lets users select a date
3. Automatically calculates beginning and end dates based on user-selected dates
4. Let the report process the data between the beginning and the end of the period

1. Recognize several functions related to time

1.Cdate (date expression)

Converts a date expression to data of a date type.

A date expression is any expression that can be interpreted as a date, contains a date literal, can be viewed as a string of dates, and a date returned from a function.

For example: MyDate = CDate ("99-5-20"), such a date expression must be double quotes, otherwise the result is unpredictable.

CDate determines the format of the date according to the locale on the system. If the format provided is an unrecognized date setting, the order of year, month, and day is not correctly judged.

2.Now ()

Returns the date and time of the current computer system setting.

3.Year (date expression)

Returns an integer that represents the year.

For example: Year ("00-6-15") = 2000

4.Month (date expression)

Returns an integer from 1 to 12 that represents a month in the year.

For example: Month ("00-6-15") = 6

5.DATEADD (interval, number, date)

Returns a date that is added over a period of time. You can use DATEADD to calculate a date that is 30 days away from today, or to calculate a time of 45 minutes from now.

Dataadd function parameters

Parameters	Description
Interval	A string expression, which is the unit of time interval to add.
Number	Numeric expression, which is the number of time intervals to add. The value can be positive (get a future date), or it can be negative (get past date).
Date	A date expression, which is also preceded by a time interval.

tr>

interval parameter setting

value	description
year
season
m	month
day
w
WW	weeks
h	time
minutes
s	seconds

Like what:
DATEADD ("D", 10, "2000-6-18") = 2000-06-28
DateAdd ("M",-1, "2000-6-18") = 2000-05-18

2. Create let user select Date form

Create a form and add the following controls: a "year" combo box, a "month" combo box, a "Start date" text box, and an End date text box. If the Control wizard appears when you add a combo box, cancel the wizard.

Set the data properties of the "year" combo box as above, so that users can only choose 1999-2002, and of course you can increase the year. The default value is the year of the current date.

Similar to the year combo box, set the Data property of the month combo box as above, so that users can only choose 1-12 months. The default value is the month of the current date.
3. Automatically calculates beginning and end dates based on user-selected dates

Fill in the control source at the Start date text box:

=dateadd ("M",-1, CDate ([year] & "-" & [Month] & "-26"))

The function you just learned is used, this is a nested expression, CDate ([year] & "-" & [Month] & "-26") represents the 26th of the user's selected date, and the entire expression represents the 26th of the one months before the user's selected date. The "year" and "month" here are added [] to indicate that they are controls.

Fill in the control source at the End Date text box:

=cdate ([Year] & "-" & [Month] & "-25")

25th that represents the date selected by the user.

This is the effect of the form running, and the start and end dates are updated as the years and months change.

4. Let the report process the data between the beginning and the end of the period

Design a report that selects your table or query in its record source, in which fields that relate to dates fill in the guidelines: Between [forms]! [Select Date]! [Start Date] and [forms]! [Select Date]! [End Date] so that the report will only process data between the start date and end date.

Finally, add a command button to start the report for your window, and it's done!

The following figure is an instance of a report run

October of this report, which is actually September 26 to October 25