Database Reference
In-Depth Information
UP TO SPEED: NAME THE DAY (OR MONTH)
Even when you're using the month number to group your data, you may prefer to see months by
name
. For example, if you produce a report of sales by month, you probably want the groupings
labeled January, February, March, and so on, instead of 1, 2, and 3. You can use the
MonthName()
function to get this effect:
MonthName ( Invoice Date )
You can still sort all your invoices by the date field to get them in order, but you use your new
MonthName()
value to display in the subsummary part. See
Summary Fields and Subsummary Parts
to learn how to use subsummary parts in reports.
Sometimes you need to see the day name (like Monday, Tuesday, or Wednesday). The
DayName()
function does just that. To get its numerical equivalent, use
DayOfWeek()
instead, which returns
1
for Sunday,
2
for Monday, and so forth.
Calculations that Create Dates and Times
Without even being aware of it, people do incredibly complex math every time they glance at
a paper calendar or analog clock. When the boss said, “I want these invoices to go out 2 days
before the end of this month,” a human clerk knew exactly what to do. But how do you tell a
computer to put “2 days before the end of next month” in the Invoice Date field? The answer
is at the end of this section, but first you'll learn the functions you'll need to calculate dates
and times:
▪ The
Date()
function accepts three parameters—Month, Day, and Year—and returns the
appropriate date value. For example, to get a date value of June 21, 2015, from a calcula-
tion, you use the date function like this:
Date ( 6 ; 21 ; 2015 )
NOTE
To ensure accurate dates and date calculations, always use four digits when entering a year. You
can configure date fields on layouts to just display the last two if that's how you want to see
them.
▪ The
Time()
function wants three parameters as well, this time Hours, Minutes, and Se-
conds. It returns the time value. (The Seconds parameter can have a decimal point if ne-
cessary.) For example, you can construct the time value “8:00 p.m.” like this:
Time ( 20 ; 0 ; 0 )
