Formatting Fields
Numeric and date fields placed inside Text/Field objects can be formatted using the special codes below:
Date, Time and DateTime fields:
c |
Display both the date and time using the system short date and time formatting as defined in the Windows Control Panel. |
d |
Display the day as a number without a leading zero (1-31). |
dd |
Display the day as a number with a leading zero (01-31). |
ddd |
Display the day as an abbreviation (Sun-Sat). |
dddd |
Display the day as a full name (Sunday-Saturday). |
ddddd |
Display the date using the system short date format as defined in the Windows Control Panel. |
dddddd |
Display the date using the system long date format as defined in the Windows Control Panel. |
m |
Display the month as a number without a leading zero (1-12). If "m" immediately follows an "h" or "hh" code, the minute rather than the month is displayed. |
mm |
Display the month as a number with a leading zero (01-12). If "mm" immediately follows an "h" or "hh" code, the minute rather than the month is displayed. |
mmm |
Display the month as an abbreviation (Jan-Dec). |
mmmm |
Display the month as a full name (January-December). |
yy |
Display the year as a two-digit number (00-99). |
yyyy |
Display the year as a four-digit number (0000-9999). |
h |
Display the hour without a leading zero (0-23). |
hh |
Display the hour with a leading zero (00-23). |
n |
Display the minute without a leading zero (0-59). |
nn |
Display the minute with a leading zero (00-59). |
s |
Display the second without a leading zero (0-59). |
ss |
Display the second with a leading zero (00-59). |
t |
Display the time using the short (without seconds) system time format as defined in the Windows Control Panel. |
tt |
Display the time using the long (with seconds) system time format as defined in the Windows Control Panel. |
ampm |
When used with the "h" or "hh" codes, this option will display the time in 12-hour format followed by the "AM" or "PM" symbols as defined in the Windows Control Panel. |
/ |
Display the date separator character as defined in the Windows Control Panel. |
: |
Display the time separator character as defined in the Windows Control Panel. |
Integer, BigInt, AutoInc, Currency and Float Fields:
0 |
If the number being formatted has a digit in the position where the "0" appears in the format string, then that digit is displayed. Otherwise, a "0" is displayed in that position. |
# |
If the number being formatted has a digit in the position where the "#" appears in the format string, then that digit is displayed. Otherwise, nothing is displayed in that position. |
. |
The "." character determines the location of the decimal point in the displayed number. The actual character displayed is the decimal separator defined in the Windows Control Panel. |
, |
Including at least one "," character in the format string will display the number with thousand separators inserted between each group of three digits to the left of the decimal point. The placement of the "," character isn't important as it's merely an indicator that thousand separators are wanted. The actual character displayed is the thousands separator defined in the Windows Control Panel. |
| |
Separates sections for positive, negative, and zero numbers in the format string. |
The formatted number will be rounded to as many decimal places as there are digit place holders ("0" or "#") to the right of the decimal point. If the format string contains no decimal point, the number will be rounded to the nearest whole number.
If the formatted number contains more digits to the left of the decimal separator than there are digit place holders to the left of the "." character in the format string, the extra digits will still be displayed.
Below are some example numeric format strings:
Format String |
Number |
Displayed Value |
Comments |
#.## |
125.5 |
125.5 |
Extra digits to the left of decimal still appear. |
#.000 |
2.5 |
2.500 |
Extra zeros added to the right of decimal. |
00.## |
.007 |
00.01 |
Zeros added to left of decimal and fractional part rounded to two decimal places. |
$#.00 |
125.5 |
$125.50 |
Dollar sign added to left of number and extra zeros added to the right of decimal. |