Home > Articles > Computer Software > Business Office Software > Database Software > Other

  • Print
  • + Share This
  • 💬 Discuss

This chapter is from the book

Formatting Values

Often data is stored is differently from the way it's displayed on forms and in reports. The Format function is your tool to change how data is displayed. Access provides many predefined formats for you to use and allows you to customize your own formats. For example, a phone number might be stored as 10 digits but you can display it like (111) 222-3333 by applying a format. Another example are Date/Time values. As previously noted, they are stored as a Double number. However, the Format function can display the number in a variety of date or time formats.

The Format function uses the following syntax, where expression can be either a String or Numeric data type that results in the value you want to format:

Format(expression[, format[,firstdayofweek[, firstweekofyear]])

There are three optional arguments, the first of which determines how the data is formatted. The other two optional arguments, firstdayofweek and firstdayofyear, are numerical constants that can be used to adjust the first day of a week or year when using the DatePart function. Tables 4.2 and 4.3 show a list of the values for each constant. The default values are Sunday and January 1, respectively.

Tables 4.6 and 4.7 show some of the predefined formats you can use.

Table 4.6. Numeric Named Formats

Format

Example

Result

General Number

Format(12345.6789,"General Number")

12345.6789

Currency

Format(12345.6789, "Currency")

$12,345.68

Fixed

Format(0.1, "Fixed")

0.10

Standard

Format(12345.6789, "Standard")

12,345.68

Percent

Format(6789, "Percent")

67.89%

Scientific

Format(12345.6789, "Scientific")

1.23E+03

Yes/No

Format(0, "Yes/No")

No

Format(3, "Yes/No")

Yes

True/False

Format(0, "Yes/No")

False

Format(3, "Yes/No")

True

On/Off

Format(0, "Yes/No")

Off

Format(3, "Yes/No")

On

Table 4.7. Date/Time Named Formats

Format

Example

Result

General Date

Format("04/01/07", "General Date")

4/1/2007

Long Date

Format("04/01/07", "Long Date")

Sunday April 1, 2007

Medium Date

Format("04/01/07", "Medium Date")

01-Apr-07

Short Date

Format("04/01/07", "Short Date")

4/1/2007

Long Time

Format('13:13:13', "Long Time")

1:13:13 PM

Medium Time

Format('13:13:13', "Medium Time")

1:13 PM

Short Time

Format('13:13:13', "Short Time")

13:13

The result for Currency is based on the United States regional settings; if you use a different regional setting, the Currency format uses those settings. For the Boolean types a zero results in a No, False, or Off result. Any other value gives the opposite result.

Applying User-Defined Formats

Although the predefined formats listed in Tables 4.6 and 4.7 cover many situations, at times you'll need to create your own formats. You can use a number of special characters and placeholders to define your own formats. Tables 4.8, 4.9, and 4.10 list these formats.

Table 4.8. Numeric User-Defined Formats

Format

Explanation

Example

Result

0

Display actual digit or 0 for each 0 used. Rounds if more digits than shown.

Format(12.3456, "000.00000")

012.34560

Format(12.3456, "000.00")

012.35

#

Display actual digit or nothing. Rounds if more digits than shown.

Format(12.3456, "###.#####")

12.3456

Format(12.3456, "##.##")

12.35

%

Multiples by 100 and adds percent sign

Format(.3456, "##%")

35%

E- E+ e- e+

Display scientific notation.

Format(1.234567, "###E-###)

123E-2

- + $ ()

Display a literal character.

Format(123.45, "$####.##")

$123.45

\

Display following character as a literal.

Format(.3456, "##.##\%"

.35%

Table 4.9. Date User-Defined Formats

Format

Explanation

Example

Result

d

Display day of month without leading zero

Format("04/04/07", "d")

1

dd

Display day of month with leading zero where needed

Format("04/04/07", "dd")

01

ddd

Display abbreviated day of week

Format("04/01/07", "ddd")

Sun

dddd

Display full day of week

Format("04/01/07", "dddd")

Sunday

ddddd

Display short date

Format("04/01/07", "ddddd")

4/1/2007

dddddd

Display long date

Format("04/01/07", "dddddd")

Sunday, April 1, 2007

m

Display month without leading zero

Format("04/01/07", "m")

4

mm

Display month with leading zero

Format("04/01/07", "mm")

04

mmm

Display abbreviated month name

Format("04/01/07", "mmm"

Apr

mmmm

Display full month name

Format("04/01/07", "mmmm")

April

q

Display quarter of year

Format("04/01/07", "q")

2

h

Display hours without leading zero

Format("13:13:13", "h")

1

hh

Display hours with leading zero

Format("13:13:13","hh")

01

n

Display minutes without leading zero

Format("13:07:13", "n")

7

nn

Display minutes with leading zero

Format("13:07:13", "nn")

07

s

Display seconds without leading zero

Format("13:13:07", "s")

7

ss

Display seconds with leading zero

Format("13:13:07", "ss")

07

ttttt

Display 12-hour clock

Format("13:13:13", "ttttt")

1:13:13 PM

AM/PM

With other time formats displays either upper- or lowercase AM/PM

Format("13:13:13", "hh:nn AM/PM")

1:13 PM

am/pm

Format("13:13:13", "hh:nn am/pm")

1:13 pm

A/P

With other time formats displays either upper- or lowercase A/P

Format("13:13:13", "hh:nn A/P")

1:13 P

a/p

Format("13:13:13", "hh:nn a/p")

1:13 p

ww

Display the number of the week (1–54)

Format("04/01/07", "ww")

14

w

Display the number of the day of the week

Format("04/01/07", "w")

1

y

Display the day of the year (1–366)

Format("04/01/07", "y")

91

yy

Display 2-digit year (00–99)

Format("04/01/07", "yy")

07

yyyy

Display 4-digit year (0100–9999)

Format("04/01/07", "yyyy")

2007

Table 4.10. String User-Defined Formats

Format

Explanation

Example

Result

@

Display actual character or space

Format("VBA", "@@@@@")

VBA

&

Display actual character or nothing

Format("VBA", "&&&&&")

VBA

<

Display character as lowercase

Format("VBA", "<<<<")

vba

>

Display character in uppercase

Format("VBA", ">>>>")

VBA

These formats can also be combined to display different date or time formats. The following are some examples:

Format("04/01/07", "yyyymmdd") = 20070401
Format("4/01/07", "mmm dd") = Apr 01

Format("04/01/07", "mmm yyyy") = Apr 2007)
  • + Share This
  • 🔖 Save To Your Account
Microsoft Office Access 2007 VBA

This chapter is from the book

Microsoft Office Access 2007 VBA

Discussions

comments powered by Disqus