Take an expression and return it as a formatted string (Number, Date or String).


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

   expression      Any valid expression.
format The format expression as described below. firstdayofweek For date expressions, a constant that specifies the first day of the week, default=vbSunday. firstweekofyear For date expressions, a constant that specifies the first week of the year. default = the week of January 1st.

The Format function can be used in VBA code or in SQL queries, the format expressions use a very similar syntax to that used in the format property of Access Text fields.

A user-defined format expression for numbers can have from one to four sections separated by semicolons.
A user-defined format expression for Strings can have one or two sections separated by semicolons.
A user-defined format expression for dates can have a single format applied:

  Section 1   Section 2   Section 3   Section 4
Number Number format - Applies to Positive numbers. ; Number format - Applies to Negative numbers* ; Number format - Applies to Zeros* ; Format for Null values or Text.
Date Date/Time format            
String Format for string data ; Null Values and zero-length strings ("")*        

* If sections 2 and 3 are not defined then Section 1 format will be applied.

The format codes for Numbers, Dates and Strings cannot be mixed in the same format() statement.

If the format() function is applied to an Access password field, it will reveal the password.

Format() will return a Variant, you can also use Format$() to return a String.
You should use Format() if there is any chance of a Null value, since assigning Null to a String will raise an error.


strDiscount = Format("0.15", "Percent") will return '15%'

intPrice = Format(12.45, "Currency") will return '$12.45'

intPrice = Format(64.5, "#,##0.00") will return '64.50'

dtmOrder = Format([order_time], "Long Time") will return a system defined long time.

strStart = Format(MyDate, "yyyy-mm-dd") will return an ISO 8601 date.

strName = Format(MyName, ">") will return the text for 'MyName' in upper case.

strName = Format(MyName, "<;Required item missing") will return the text for 'MyName' in lower case, if MyName is empty it will return the string "Required item missing"

intPrice = Format(MyPrice, "+ 0.00;(0);nothing") will return a positive price prefixed with a + with 2 decimal places, a negative price will be enclosed in parentheses and rounded (no decimals) and lastly a price of Zero will return the string "nothing"

SELECT Format(order_price, "Currency") FROM T_Orders; Sql statement using the format function.

“An essential aspect of creativity is not being afraid to fail” ~ Edwin Land


Date - Return the current date.
DateAdd - Add a time interval to a date.
- Return the time difference between two dates.
DatePart - Return a specified part of a given date.
MonthName - Return a string representing the month.
Q200299 - BUG: Format or DatePart Functions return wrong Week number for last Monday in year.

Copyright © 1999-2024
Some rights reserved