Format Function

   
Format Function

Class

Microsoft.VisualBasic.Strings

Syntax

 Format(   expression   [,   style   [,   dayofweek   [, _   weekofyear   ]]]) 
expression (required; String/Numeric)

Any valid string or numeric expression

style (optional; String)

A valid named or user -defined format expression

dayofweek (optional; FirstDayOfWeek enumeration)

A constant that specifies the first day of the week

weekofyear (optional; FirstWeekOfYear enumeration)

A constant that specifies the first week of the year

First Day of Week Constants

Constant

Value

Description

 System 

NLS API setting

 Sunday 

1

Sunday (default)

 Monday 

2

Monday

 Tuesday 

3

Tuesday

 Wednesday 

4

Wednesday

 Thursday 

5

Thursday

 Friday 

6

Friday

 Saturday 

7

Saturday

First Week of Year Constants

Constant

Value

Description

 UseSystemDayOfWeek 

Use the NLS API setting.

 FirstJan1 

1

Start with the week in which January 1 occurs (default).

 FirstFourDays 

2

Start with the first week that has at least four days in the new year.

 FirstFullWeek 

3

Start with first full week of the year.

Return Value

A string containing the formatted expression

Description

Allows you to use either predefined or user-defined formats to output string, numeric, and date/time data

Rules at a Glance

  • style can be either a predefined or user-defined format.

  • User-defined formats for numeric values are created with up to four sections, delimited by semicolons. Each section is used for a different type of numeric value. The four possible sections are shown in the following table:

Section

Applies to

1

All values if used alone; positive values if used with more than one section

2

Negative values

3

Zero values

4

Nothing value

  • It is not necessary to include all four sections in the style clause. However, the number of sections present determines what types of numeric values each section defines, as the following table shows:

Number of sections

Applies to

1

All numeric values

2

Positive and zero values; negative values

3

Positive values; negative values; zero values

4

As shown in previous table

  • If you leave a section blank, it will use the same format as that defined for positive values. For example, the format string:

     "#.00;;#,##" 

    means that negative values will appear in the same format as positive values.

  • Only one section is allowed where one of the named formats is used.

  • User-defined formats for string values can have two sections. The first is for all values; the second applies only to Null values or zero-length strings.

  • The predefined date and time formats are:

Format

Example

Returns

General Date

 Format("01/06/98","General Date") 

1/6/98

Long Date

 Format("01/06/98","Long Date") 

Tuesday, January 06, 1998

Medium Date

 Format("01/06/98","Medium Date") 

06-Jan-98

Short Date

 Format("01/06/98","Short Date") 

1/6/98

Long Time

 Format("17:08:06","Long Time") 

5:08:06 PM

Medium Time

 Format("17:08:06","Medium Time") 

05:08 PM

Short Time

 Format("17:08:06","Short Time") 

17:08

  • The predefined numeric formats are:

Format

Examples

Returns

General Number

Format(562486.2356, "General Number")

562486.2356

Currency

Format(562486.2356, "Currency")

$562,486.24

Fixed

Format(0.2, "Fixed")

0.20

Standard

Format(562486.2356, "Standard")

562,486.24

Percent

Format(.7521, "Percent")

75.21%

Scientific

Format(562486.2356, "Scientific")

5.62E+05

Yes/No

Format(0,"Yes/No")

Format(23,"Yes/No")

No

Yes

True/False

Format(0,"True/False")

Format(23,"True/False")

False

True

On/Off

Format(0,"On/Off")

Format(23,"On/Off")

Off

On

  • Characters used to create user-defined date and time formats are:

Char

Element Used In

Display As

Example

Returns

c

Date

A date and/or time based on the short-date and short-time international settings of the current Windows system

Format("01/06/98 17:08:06", "c")

1/6/98 5:08:06 PM

dddddd

Date

A complete date based on the long-date international setting of the current Windows system

Format("01/06/98", "dddddd")

Tuesday, January 06, 1998

( / )

Dateseparator

A date delimited with the specified character

Format("01/06/98", "mm-dd-yyyy")

01-06-1998

d

Day

A number (1-31) without a leading zero

Format("01/06/98", "d")

6

dd

Day

A number (01-31) with a leading zero

Format("01/06/98", "dd")

06

ddd

Day

An abbreviation (Sun-Sat)

Format("01/06/98", "ddd")

Tue

dddd

Day

A full name (Sunday-Saturday)

Format("01/06/98", "dddd")

Tuesday

ddddd

Date

A date based on the short date section in the computer's Windows international settings

Format("01/06/98", "ddddd")

1/6/98

h

Hour

A number (0-23) without leading zeros

Format("05:08:06", "h")

5

hh

Hour

A number (00-23) with leading zeros

Format("05:08:06", "hh")

05

n

Minute

A number (0-59) without leading zeros

Format("05:08:06", "n")

8

nn

Minute

A number (00-59) with leading zeros

Format("05:08:06", "nn")

08

m

Month

A number (1-12) without a leading zero

Format("01/06/98", "m")

1

mm

Month

A number (01-12) with a leading zero

Format("01/06/98", "mm")

01

mmm

Month

An abbreviation (Jan-Dec)

Format("01/06/98", "mmm")

Jan

mmmm

Month

A full month name (January-December)

Format("01/06/98", "mmmm")

January

q

Quarter

A number (1-4)

Format("01/06/98", "q")

1

s

Second

A number (0-59) without leading zeros

Format("05:08:06", "s")

6

ss

Second

A number (00-59) with leading zeros

Format("05:08:06", "ss")

06

ttttt

Time

A time based on the 12-hour clock, using the time separator and leading zeros specified in Windows locale settings

Format("05:08:06", "ttttt")

5:08:06 AM

AM/PM

Time

A 12-hour clock format using uppercase AM and PM

Format("17:08:06", "hh:mm:ss AM/PM")

05:08:06 PM

am/pm

Time

A 12-hour clock format using lowercase am and pm

Format("17:08:06", "hh:mm:ss am/pm")

05:08:06 pm

A/P

Time

A 12-hour clock format using an uppercase "A" for AM and "P" for PM

Format("17:08:06", "hh:mm:ss A/P")

05:08:06 P

a/p

Time

A 12-hour clock format using a lowercase "a" for AM and "p" for PM

Format("17:08:06", "hh:mm:ss a/p")

05:08:06 p

( : )

Time separator

A time format using a nonstandard character

Format("17:08:06", "hh::mm::ss")

17::08::06

ww

Week

A number (1 - 54)

Format("01/06/98", "ww")

2

w

Weekday

A number (1 for Sunday through 7 for Saturday)

Format("01/06/98", "w")

3

y

Day of Year

A number (1 - 366)

Format("01/06/98", "y")

6

yy

Year

A 2-digit number (00 - 99)

Format("01/06/98", "yy")

98

yyyy

Year

A 4-digit number (100 - 9999)

Format("01/06/98", "yyyy")

1998

  • Characters used to create user-defined number formats are as follows :

Char

Description

Examples

( )

Digit placeholder. If expression contains a digit in the appropriate position, the digit is displayed; otherwise , a 0 will be displayed. The format definition dictates the number of digits after the decimal point, forcing the number held within an expression to be rounded to the given number of decimal places. It does not, however, affect the number of digits shown to the left of the decimal point.

Format(23.675, "00.0000") returns 23.6750

Format(23.675, "00.00") returns 23.68

Format(2658, "00000") returns 02658

Format(2658, "00.00") returns 2658.00

( # )

Digit placeholder. If expression contains a digit in the appropriate position, the digit is displayed; otherwise, nothing will be displayed.

Format(23.675, "##.##") returns 23.68

Format(23.675, "##.####") returns 23.675

Format(12345.25, "#,###.##") returns 12,345.25

( . )

Decimal placeholder. The actual character displayed as a decimal placeholder depends on the international settings of the local Windows system.

 

(%)

Percentage placeholder. Displays expression as a percentage by first multiplying the value of expression by 100.

Format(0.25, "##.00%") returns 25.00%

( , )

Thousands separator. The actual character displayed as a thousands separator depends on the international settings of the local Windows system. You only need to show one thousands separator in your definition.

Format(1000000, "#,###") returns 1,000,000

( E- E+ e- e+ )

Scientific format. If the format expression contains at least one digit placeholder ( or # ) to the right of E- , E+ , e- , or e+ , the number is displayed in scientific format, and the letter E or e that was used in the format expression is inserted between the number and its exponent. The number of digit placeholders to the right determines the number of digits displayed in the exponent. Use E- or e- to place a minus sign next to negative exponents. Use E+ or e+ to place a minus sign next to negative exponents and a plus sign next to positive exponents.

 

- + $ ( )

Displays a literal character.

Format(2345.25, "$#,###.##") returns $2,345.25

( \ )

The character following the backslash will be displayed as a literal character. Use the backslash to display a special formatting character as a literal.

Format(0.25, "##.00\%") returns .25%

Note the difference between the result of this example and the result of the % formatting character.

Programming Tips and Gotchas

  • A little known and very important use of the Format function is to prevent an "Invalid Use of Null" error from occurring when assigning values from a recordset to a variable within your program. For example, if a field within either a DAO or RDO recordset created from either an Access or SQL Server database contains a Null value, you could trap this and change its value to "" as follows:

     If IsNull(rsMyRecordSet!myValue) Then    sMyString = ""  Else    sMyString = rsMyRecordSet!myValue End If 

    However, assigning the value returned by the Format function that has been passed the recordset field can do away with this long and tedious coding, as the following line of code illustrates:

     sMyString = Format(rsMyRecordSet!myValue) 
  • If you are passing a date to SQL Server, what date format should you use? By default, SQL Server expects an American date format, mmddyy , but it is possible for the database to have been altered to accept other date formats, or you could be passing data to a stored procedure that begins with a date-time conversion statement ( SET DATEFORMAT dateformat ). The only sure way of passing a date into SQL Server is by using the ANSI standard date format 'yyyymmdd' (including the single quotation marks).

  • When passing a date to a Jet (Access) database, you should surround the date with hash characters ( # ); for example, #12/31/1999# .

  • Formatting numbers using Format without a format definition is also preferable to simply using the Str function. Unlike Str , the Format function removes the leading space normally reserved for the sign from positive numbers.

  • You can also use the Format function to scale numbers by 1000. This is achieved by placing a thousands separator to the immediate left of the decimal point for each 1000 you wish the number to be scaled by. Thus:

     'one separator divides the expression by 1000 = 1000 Format(1000000, "##0,.")  'two separators divides the expression by 1,000,000 = 1 Format(1000000, "##0,,.") 

VB.NET/VB 6 Differences

The VB 6 version of the Format function defined five special symbols (@, &, <, >, and !) for creating user-defined string formats. In VB.NET, these symbols are treated as literal characters.

See Also

FormatCurrency, FormatNumber, FormatPercent Functions, FormatDateTime Function

   


VB.Net Language in a Nutshell
VB.NET Language in a Nutshell
ISBN: B00006L54Q
EAN: N/A
Year: 2002
Pages: 503

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