Using the Format Function

 < Day Day Up > 

Using the Format Function

Formatting data can consume a lot of your time. Fortunately, the Format function provides a number of predefined formatting options. In addition, you can create user-defined formats for custom formatting needs. The possibilities are too numerous to adequately cover, but this section lists the tools you have at your disposal.

CAUTION

The most important thing to remember about the Format function is that the resulting value is a Variant String subtype the resulting value's data type does not remain the same. That means that although the Format function can make your data look good, you won't want to use it with values used in other calculations. When a value needs manipulating before calculating, look to the conversion functions for help.


The Format function uses the form

 

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

where expression is a String or Numeric data type that represents the value you're formatting. The optional arguments determine how expression is formatted. The format argument is one of many predefined or custom format expressions. You saw the last two, firstdayofweek and firstweekofyear, in the earlier section, "Extracting Date Components."

Numbers, including date and time values and serial numbers, as well as strings, can all be formatted using the Format function. You use a unique set of named and user-defined format expressions, defined in Tables 5.5 and 5.6.

Table 5.5. Numeric Named Formats

Format

Example

Result

General Number

Format(1234.5678, "General Number")

1234.5678

Currency

Format(1234.5678, "Currency")

$1,234.57

Fixed

Format(0.1, "Fixed")

0.10

Standard

Format(1234.5678, "Standard")

1,234.57

Percent

Format(.5678, "Percent")

56.78%

Scientific

Format(1234.5678, "Scientific")

1.23E+03

Yes/No

Format(0, "Yes/No")

Format(2, "Yes/No")

No

Yes

True/False

Format(0, "True/False")

Format(2, "True/False")

False

True

On/Off

Format(0, "On/Off")

Format(2, "On/Off")

Off

On


Table 5.6. Date/Time Named Formats

Format

Example

Result

General Date

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

4/1/2004

Long Date

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

Thursday, April 01, 2004

Medium Date

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

01-Apr-04

Short Date

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

4/1/2004

Long Time

Format("13:41:02", "Long Time")

1:41:02 PM

Medium Time

Format("13:41:02", "Medium Time")

1:41 PM

Short Time

Format("13:41:02", "Short Time")

13:41


Most of the format expressions are self-explanatory, but a few deserve a bit more information. Currency relies on your system's locale settings for the appropriate symbols and thousand separators. Fixed always displays at least one digit to the left and two digits to the right of the decimal point. Standard is similar to Fixed except it also includes the thousands separator, as determined by the system's locale settings. Percent simply multiples the value by 100 and adds the percent symbol.

Applying User-Defined Formats

The named formats listed in Table 5.5 and Table 5.6 won't always be adequate. When they don't suffice, you can use the user-defined format characters to create your own. These formats are listed in Tables 5.7, 5.8, and 5.9.

Table 5.7. Numeric User-Defined Formats

Format

Explanation

Example

Result

0

Existing digit or 0 is displayed for each 0

Format(12.3456, "000.00000")

012.34560

Rounds if more digits than placeholder characters

Format(12.3456, "000.00")

012.35

#

Displays existing digit or nothing

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

12.3456

Rounds if more digits than placeholder characters

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

12.35

%

Multiples value by 100 and adds the percentage symbol

Format(.3456, "##%")

35%

E- E+ e- e+

Converts to scientific notation

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

123E-2

 

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

123e-2

- + $ ()

Displays a literal character

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

$123.45

\

Displays following character as a literal character

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

.35%


Table 5.8. Date User-Defined Formats

Format

Explanation

Example

Result

d

Displays day of month without a leading zero

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

1

dd

Displays day of month with a leading zero when month is one character value

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

01

ddd

Displays day of week by abbreviated name

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

Thu

dddd

Displays day of week by full name

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

Thursday

ddddd

Displays short date

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

4/1/2004

dddddd

Displays long date

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

Thursday, April 01, 2004

m

Displays month value without a leading zero

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

4

mm

Displays month value with leading zero when month is single digit value

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

04

mmm

Displays month as abbreviated name

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

Apr

mmmm

Displays month as full name

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

April

q

Displays yearly quarter

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

2

h

Displays hour of the day without leading zeros

Format("9:41:02", "h")

9

hh

Displays hour of the day with leading zeros when hour is single digit value

Format("9:41:02", "hh")

09

n

Displays minute without leading zeros

Format("9:03:02", "n")

3

nn

Displays minute with leading zeros when minute is single digit value

Format("9:03:02", "nn")

03

s

Displays second without leading zeros

Format("9:03:02", "s")

2

ss

Displays second with leading zero when second is single digit value

Format("9:03:02", "ss")

02

ttttt

Displays 12-hour clock using locale settings

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

1:41:02 PM

AM/PM

Displays 12-hour clock using uppercase AM/PM

Format("13:41:02","hh:mm AM/PM")

01:41 PM

am/pm

Displays 12-hour clock using lowercase am/pm

Format("13:41:02", "hh:mm am/pm")

01:41 pm

A/P

Displays 12-hour clock with uppercase A or P

Format("13:41:02", "hh:mm A/P")

01:41 P

a/p

Displays 12-hour clock with lowercase a or p

Format("13:41:02", "hh:mm a/p")

01:41 p

ww

Displays the week value, 1 to 54

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

14

w

Displays the weekday value, 1 to 7

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

5

y

Displays a day value from 1 to 366

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

92

yy

Displays a two-digit year value from 00 to 99 that represents the date's year

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

04

yyyy

Displays a four-digit year value from 0100 to 9999 that represents the date's year

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

2004


Table 5.9. String User-Defined Formats

Format

Explanation

Example

Result

@

Displays existing character or space

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

VBA (includes two spaces before the string)

&

Displays existing character or nothing

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

VBA

<

Displays all characters in lowercase

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

vba

>

Displays all character in uppercase

Format("vba", ">>>")

VBA


     < Day Day Up > 


    Automating Microsoft Access with VBA
    Automating Microsoft Access with VBA
    ISBN: 0789732440
    EAN: 2147483647
    Year: 2003
    Pages: 186

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net