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
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.
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. |
Value |
Description |
yyyy |
Years |
Q |
Quarter |
M |
Month |
Y |
The number of days in a year |
D |
Day |
W |
Number of days in a week |
Ww |
Week |
H |
When |
N |
Minutes |
S |
Seconds |
Like what:
DATEADD ("D", 10, "2000-6-18") = 2000-06-28
DateAdd ("M",-1, "2000-6-18") = 2000-05-18