There are several functions that you can use
with variables and they greatly enhance the capabilities of custom
report scripting. Please find below a summary of these functions
and the parameters. It should be noted that any literal string /
substring parameters must be enclosed with speech marks unless they
are variables.
There is a sample script provided with the program called
Script Expression Functions that uses some of
these functions.
|
As you are typing in your expession
the editor will display all possible functions that can be
used.
|
|
Once it has determined the function
itself it will show you the paremeter list.
|
abs( number )
|
| |
Returns the absolute value (magnitude) of a number (negatives
becomes positives, positives remain unchanged). |
ceil( number )
|
| |
Returns the next integer above the given value, unless it is
already an integer (always rounds). |
| count( substring,
main_string[, ignore_case = 1] ) |
| |
Returns the number of times substring appears in
main_string, ignoring case by default.
Example:
$strTalksThisYear = "55, 121, 86, 134,
121, 19, 121"
$iNumTalks = count("121", $strTalksThisYear)
TEXT "The brother will be giving talk number 121 $iNumTalks time(s)
this year."
EOL |
ellipsis( string,
max_chars )
|
| |
Truncates a string at the nearest word and adds ..., such that
the resulting string is no longer than the specified number of
characters. |
| field( field name ) |
| |
This function extracts the contents of the specified field in
the current row, exactly as the FIELD or VARIABLE_FIELD command
would do; since the return value of this function is the field
contents, it can be either assigned to a variable (an alternative
to VARIABLE_FIELD), or more usefully, it can be embedded directly
into TEXT or PARAGRAPH to more easily print out field contents as
part of a string.
Three ways to print out text mixed with a field:
TEXT "The brother is called "
FIELD "BrotherName"
EOL
or
VARIABLE_FIELD "BrotherName"
"$strName"
TEXT "The brother is called $strName"
EOL
or
TEXT "The brother is called {
field("BrotherName") }"
EOL |
floor( number
)
|
| |
Returns the next integer below the given value, unless it is
already an integer (never rounds). |
index_of( substring,
main_string[, start_index = 0[,
ignore_case = 1] ] )
|
| |
Returns the index of the first occurrence of substring
found in main_string, starting from start_index.
It ignores case unless you specify 0 for the last optional
parameter. |
int( number )
|
| |
Is the same as round( number,
0 ) - i.e., it rounds to the nearest integer. |
last_index_of( substring,
main_string[, last_index = 0[,
ignore_case = 1] ] )
|
| |
Returns the index of the last occurrence of substring
found in main_string, working back from (and including)
last_index. It ignores case unless you specify 0 for the
last optional parameter. |
left( string,
count )
|
| |
Returns up to the specified number of characters from the start
of the string. |
len( string )
|
| |
Returns the number of characters in the specified
string. |
lower_case( string
)
|
| |
Returns the entire string with all letters converted
to lower case. |
mid( string,
start_index[, count = to end]
)
|
| |
Returns up to the specified number of characters from the
middle of the string starting at start_index. If
you don't supply the count, it returns from
start_index to the end of the string. |
| nearest_space( string,
start_index ) |
| |
Returns the result of either next_space() or prev_space(),
whichever is nearer to the given index. |
next_space( string,
start_index )
|
| |
Returns the index of the next space within the specified
string, starting from (and including)
start_index. |
prev_space( string,
start_index )
|
| |
Returns the index of the previous space within the specified
string, working back from (and including)
start_index. |
| replace( substring,
replace_string, main_string[,
ignore_case = 1] ) |
| |
Returns a string where all occurrences of substring
appearing in main_string have been changed to
replace_string, ignoring case by default. Note that even
if it ignores case when finding occurrences, it always inserts an
exact copy of replace_string, no matter what case it has.
Example:
TEXT {"Brother " + $strBrotherName + "
will be providing hospitality this week. Please contact " +
$strBrotherName + " if you have any questions."}
can also be accomplished with:
$strTemplate = "Brother %1 will be
providing hospitality this week. Please contact %1 if you have any
questions."
TEXT { replace("%1", $strBrotherName, $strTemplate) } |
right( string,
count )
|
| |
Returns up to the specified number of characters from the end
of the string. |
round( number,
decimal_places )
|
| |
Rounds a number to the nearest value, to the specified
number of decimal places. |
sgn( number )
|
| |
Returns just the sign of the number; it returns 1 for
positive values, -1 for negative values and 0 for values within
1e-5 (+/- 0.00001) of zero. |
title_case( string
)
|
| |
Returns the entire string with all letters that start
words converted to upper case, even if they follow symbols such as
speech marks, brackets, hashes, etc.). Note that apostrophes do not
cause this behaviour unless they are used as quotes around
text. |
| trim( string ) |
| |
Removes spaces, tabs and newline characters from the start and
end of the specified string (but not from within the string). |
upper_case( string
)
|
| |
Returns the entire string with all letters converted
to upper case. |
Manipulating Dates
Strings
representing dates can be manipulated with + and - just like
numbers, and have a few functions to help work with them. Dates are
seen internally as strings, and should be enclosed in quotes when
trying to use literal dates; they can be given in your local /
national format. The following operators are
supported:
date + count --> date string representing date + count
days
date - count --> date string representing date - count
days
date1 - date2 --> integer representing elapsed days (can be
negative if date2 is a later date)
Note that when using standard operators, the operation always uses
days as the time unit. To use other time units, you must use the
supplied functions. |
| date_add( date, count[,
unit_string] ) |
| |
This adds the specified count of time units to the given date,
and returns a new date. |
| date_subtract( date, count[,
unit_string] ) |
| |
This subtracts the specified count of time units from the given
date, and returns a new date.
|
| date_subtract( date1, date2[,
unit_string] ) |
| |
This subtracts the specified date from the first, and returns
the elapsed time in the specified units. The result is *always* the
count of *whole* units that have elapsed. So if you specify dates
20 days apart and ask for months, no whole months have elapsed and
so 0 will be returned. |
| format_date( date,
format_string ) |
| |
This reformats the date string into the specified format for
friendlier presentation. format_string can be #SHORTDATE,
#LONGDATE, #CUSTOMDATE[date_format] just as they are for formatting
dates with format specifiers. The hashes are optional here; for
CUSTOMDATE you can simply supply the format such as
"%Y-%m-%d".
To make it easier to select date formats for the second parameter
of format_date(), you can apply formatting whenever you are inside
speech - as long as you have an odd number of double quotes to the
left of the cursor position the format can be inserted / updated as
if the speech mark on the left were a variable. For example, if you
type
format_date($Today, "
(note the trailing double quote) you can now select a date format
and click Apply. It will be inserted at the cursor
position, right after the quote. Placing the cursor anywhere within
that format string will reflect the current format and allow you to
change it, just like it does for a variable suffix. |
date_part( date,
units_string )
|
| |
This returns an integer representing just the portion of the
date you request.
|
make_date( year, month, day[,
hour, minute, second] )
|
| |
This returns a date string built from the parts you supply.
Month is 1 - 12, day is 1 - 31.
|
| Notes:
|
| - Unless otherwise specified, any date strings are
returned in Access' timestamp format; use
format_date() to change to a more familiar
style. |
| - units_string is "Y", "M", "D"
for years / months / days or the relevant portion of a date, or
"h", "m", "s" for hours / minutes / seconds or the relevant portion
of a date. Where supported, if you omit the unit string (or supply
an unrecognised one), it is assumed to be "D" for days. |
|
