ToText Function

Uses

The ToText function can be used to convert number, date, boolean, or time values to a string (text). It provides you with controls that let you control how the resulting string looks.

It’s mostly used for concatenation (for example, you can create a title for your report that shows the records covered, or a group name that shows the date range included.)

• Add parameters to a Report title
• Control how group names appear (you can force your dates to appear in the format 3/7/10 or 20100901.

ToText for Boolean Values

Syntax

ToText(x)

Examples

Example	Result
ToText({Completed})	True when the field Completed is true. False when the field Completed is false.

ToText for Numbers (and Currency)

Syntax	Variable	Optional
ToText(x, y, z, w)	x	The number to convert to text
	y	The number of decimal places to include in result (optional). The value will be rounded to that decimal place.
	z	The character to use as the thousands separator. If you don’t specify one, it will use your application default. (Optional.)
	w	The character to use as the decimal separator. If you don’t specify one, it will use your application default. (Optional.)

Examples

Example	Result
ToText(12345.678)	“12345.678″
ToText(12345.678,2)	“12345.67″
ToText(12345.678,0)	“12345″
ToText(12345.678, 0, ‘,’, ‘.’)	“12,345.67″
ToText(12345.678,0,”")	“12345″
“Time Logged for Change # ” & ToText(12345.567,0,”")	“Time Logged for Change # 12345″

ToText for Dates

Syntax	Variable	Optional
ToText(x, y)	x	The date value to be converted.
	y	A text string that defines how the value is to be formatted. (Optional)

Examples (where datefield = January 15, 2011)

Example	Result
ToText({datefield}, “yyyyMM”)	“201101″
ToText({datefield},”dd-MMM-yyyy”)	“15-Jan-2011″
ToText({datefield},”dd MMM yyyy”)	“15 Jan 2011″
ToText({datefield},”MM/dd/yy”)	“01/15/11″
ToText({datefield},”dddd, MMM d, yyyy”)	“Saturday, Jan 1, 2011″

This looks a little confusing at first. Sometimes d’s return the day of the week and sometimes they return a number. This is because the format is determined by the specific pattern:

Pattern	Result
d	Numeric day of month without leading zero (1, 7, 31)
dd	Numeric day of month with leading zero (01, 07, 31)
ddd	Three day abbreviation of day of week (Mon, Sat)
dddd	Full name of day of week (Monday, Saturday)
M	Numeric month without leading zero (1, 7, 12)
MM	Numeric month with leading zero (01, 07, 12)
MMM	Three letter abbreviation of month (Jan, Feb, Mar)
MMMM	Full name of month (January, February, March)
yy	Last two digits of year (11, 14, 22)
yyyy	Full four digits of year (2011, 2014, 2022)

ToText for Times

Syntax	Variable	Optional
ToText(x, y, z, w)	x	The time value to be converted.
	y	A text string that defines how the value is to be formatted. (Optional)
	z	A text string defining the AM string. (Optional)
	w	A text string defining the PM string. (Optional)

Examples (where timefield =2:43:23 p.m.)

Example	Result
ToText({timefield}, “HH:mm:ss”)	“14:43:23″
ToText({timefield}, “hh:mm:ss tt”)	“02:43:23 PM”
ToText({timefield},”h*mm*ss tt”,’am’, ‘pm’)	“2*43*23 pm”

Here are the patterns used by ToText when converting time:

Pattern	Result
h	Hours without leading zeros in 12 hour format (1, 7, 12)
hh	Hours with leading zeros in 12 hour format (01, 07, 12)
H	Hours without leading zeros in 24 hour format (1, 12, 17, 24)
HH	Hours with leading zeros in 24 hour format (01, 12, 17, 24)
m	Minutes without leading zeros (5, 15, 55)
mm	Minutes with leading zeros (05, 15, 55)
s	Seconds without leading zeros (5, 15, 55)
ss	Seconds with leading zeros (05, 15, 55)
t, tt	Includes a single or multi-character AM/PM string.

If you want to include any of the pattern characters in your resulting string, you can. You just need to enclose them in quotes.

Example	Result
ToText({timefield}, “hh ‘h’ mm ‘min’ ss ‘sec’”)	“02 h 43 min 23 sec”

Filed under Function