The Format functions comes in handy when you wish to display information in a different way to which it is stored,
for instance you have a DateTime Field and you only wish to show a portion of it.
It is best to store this in your Table as the full DateTime but then display it in a Query/Form or Report in a
different manor.
The Format function returns returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.
expression the value to format.
format the type of formatting you wish to expression to return.
firstdayofweek a constant specifying the first day of the week.
firstweekofyear a constant specifying the first week of the week.
Dates
Numbers and Currency
Text and Memos
Yes/No
Dates
FirstDayOfWeek
Constant
Value
Description
vbUseSystem
0
First day of week specified in system settings (default)
vbSunday
1
Sunday
vbMonday
2
Monday (complies with ISO standard 8601, section 3.17)
vbTuesday
3
Tuesday
vbWednesday
4
Wednesday
vbThursday
5
Thursday
vbFriday
6
Friday
vbSaturday
7
Saturday
FirstWeekOfYear
Constant
Value
Description
vbUseSystem
0
Use System Settings
vbFirstJan1
1
Start with week in which January 1 occurs (default)
vbFirstFourDays
2
Start with the first week that has at least four days in the new year
vbFirstFullWeek
3
Start with first full week of the year
Symbol
Symbol
Description
Range
: (colon)
Time separator. Separators are set in the regional settings of Windows.
/
Date separator.
c
Same as the General Date predefined format.
d
Day of the month in one or two numeric digits, as needed.
(1 to 31)
dd
Day of the month in two numeric digits.
(01 to 31)
ddd
First three letters of the weekday.
(Sun to Sat)
dddd
Full name of the weekday.
(Sunday to Saturday)
ddddd
Same as the Short Date predefined format.
dddddd
Same as the Long Date predefined format.
w
Day of the week.
(1 to 7)
ww
Week of the year.
(1 to 53)
m
Month of the year in one or two numeric digits, as needed.
(1 to 12)
mm
Month of the year in two numeric digits.
(01 to 12)
mmm
First three letters of the month.
(Jan to Dec)
mmmm
Full name of the month.
(January to December)
q
Date displayed as the quarter of the year.
(1 to 4)
y
Number of the day of the year.
(1 to 366)
yy
Last two digits of the year.
(01 to 99)
yyyy
Full year.
(0100 to 9999)
h
Hour in one or two digits, as needed.
(0 to 23)
hh
Hour in two digits.
(00 to 23)
n
Minute in one or two digits, as needed.
(0 to 59)
nn
Minute in two digits.
(00 to 59)
s
Second in one or two digits, as needed.
(0 to 59)
ss
Second in two digits.
(00 to 59)
ttttt
Same as the Long Time predefined format.
AM/PM
Twelve-hour clock with the uppercase letters "AM" or "PM", as appropriate.
am/pm
Twelve-hour clock with the lowercase letters "am" or "pm", as appropriate.
A/P
Twelve-hour clock with the uppercase letter "A" or "P", as appropriate.
a/p
Twelve-hour clock with the lowercase letter "a" or "p", as appropriate.
AMPM
Twelve-hour clock with the appropriate morning/afternoon designator as defined in the regional settings of Windows.
Date
Setting
Description
General Date
(Default) If the value is a date only, no time is displayed; if the value is a time only, no date is displayed. This setting is a combination of the Short Date and Long Time settings.
Examples: 4/3/93, 05:34:00 PM, and 4/3/93 05:34:00 PM.
Long Date
Same as the Long Date setting in the regional settings of Windows.
Example: Saturday, April 3, 1993.
Medium Date
Example: 3-Apr-93.
Short Date
Same as the Short Date setting in the regional settings of Windows.
Example: 4/3/93. WARNING The Short Date setting assumes that dates between 1/1/00 and 12/31/29 are twenty-first century dates
(that is, the years are assumed to be 2000 to 2029). Dates between 1/1/30 and 12/31/99 are assumed to be twentieth
century dates (that is, the years are assumed to be 1930 to 1999).
Long Time
Same as the setting on the Time tab in the regional settings of Windows.
Medium Time
Example: 5:34 PM.
Short Time
Example: 17:34.
Format the date to show the Week number:
VBA
Format([Date], "ww")
Numbers and Currency
Number and Currency Formats
Setting
Description
General Number
(Default) Display the number as entered.
Currency
Use the thousand separator; follow the settings specified in the regional settings of
Windows for negative amounts, decimal and currency symbols, and decimal places.
Euro
Use the euro symbol (€), regardless of the currency symbol specified in the regional settings of Windows.
Fixed
Display at least one digit; follow the settings specified in the regional settings of Windows for
negative amounts, decimal and currency symbols, and decimal places.
Standard
Use the thousand separator; follow the settings specified in the regional settings of Windows
for negative amounts, decimal symbols, and decimal places.
Percent
Multiply the value by 100 and append a percent sign (%); follow the settings
specified in the regional settings of Windows for negative amounts, decimal symbols,
and decimal places.
Scientific
Use standard scientific notation.
Sections
Section
Description
First
The format for positive numbers.
Second
The format for negative numbers.
Third
The format for zero values.
Fourth
The format for Null values.
VBA
$#,##0.00[Green]; ($#,##0.00)[Red]; "Zero";"Null"
Number and Currency Symbols
Symbol
Description
. (period)
Decimal separator. Separators are set in the regional settings in Windows.
, (comma)
Thousand separator.
0
Digit placeholder. Display a digit or 0.
#
Digit placeholder. Display a digit or nothing.
$
Display the literal character "$".
%
Percentage. The value is multiplied by 100 and a percent sign is appended
E- or e-
Scientific notation with a minus sign (ā) next to negative exponents and
nothing next to positive exponents.
This symbol must be used with other symbols, as in 0.00Eā00 or 0.00E00.
E+ or e+
Scientific notation with a minus sign (ā) next to negative exponents and a
plus sign (+) next to positive exponents.
This symbol must be used with other symbols, as in 0.00E+00.
Number and Currency Settings
Setting
Data
Display
General Number
3456.789 -3456.789 $213.21
3456.789 -3456.789 $213.21
Currency
3456.789 -3456.789
$3456.789 ($-3456.789)
Fixed
3456.789 -3456.789 3.56645
3456.789 -3456.789 3.57
Standard
3456.789
3,456.79
Percent
3 300%
0.45 45%
Scientific
3456.789 -3456.789
3.46E+03 -3.46E+03
Examples
Setting
Description
0;(0);;"Null"
Display positive values normally; display negative values in parentheses; display the word "Null" if the value is Null.
+0.0;-0.0;0.0
Display a plus (+) or minus (-) sign with positive or negative numbers; display 0.0 if the value is zero.
Text and Memos
Symbols
Symbol
Description
@
Text character (either a character or a space) is required.
&
Text character is not required.
<
Force all characters to lowercase.
>
Force all characters to uppercase.
Sections
Section
Description
First
Format for fields with text.
Second
Format for fields with zero-length strings and Null values.
Examples
Setting
Data
Display
@@@-@@-@@@@
465043799
465-04-3799
@@@@@@@@@
465-04-3799 465043799
465-04-3799 465043799
>
richard RICHARD Richard
RICHARD RICHARD RICHARD
<
alex ALEX Alex
alex alex alex
@;"Unknown"
Null value Zero-length string Any text
Unknown Unknown Same text as entered is displayed
Yes/No
Sections
Section
Description
First
This section has no effect on the Yes/No data type. However, a semicolon (;) is required as a placeholder.
Second
The text to display in place of Yes, True, or On values.
Third
The text to display in place of No, False, or Off values.