Microsoft Access Format Number Function

Ms Access Format Number in this tutorial will discuss the format number that can be used for fixing the number, set and determine the value type. The syntax for Format function is:

Format ( expression, [ format ] )

Expression: the (numeric) value that going to be changed.

[format]: what the value will be turned into. Despite it is the main function, it is actually optional parameter. It has several values:

Format Explanation
General Number Displays a number without thousand separators. It is the default value if the [format] parameter is omitted.
Currency Displays thousand separators as well as two decimal places.
Fixed Displays at least one digit to the left of the decimal place and two digits to the right of the decimal place.
Standard Displays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place.
Percent Displays a percent value – that is, a number multiplied by 100 with a percent sign. Displays two digits to the right of the decimal place.
Scientific Scientific notation.
Yes/No Displays No if the number is 0. Displays Yes if the number is not 0.
True/False Displays False if the number is 0. Displays True if the number is not 0.
On/Off Displays Off if the number is 0. Displays On is the number is not 0.
Read Also:  MS Access Data Types and Properties

Note: these are the provided format from Access. There are still other [format]’s value and you can even define your own.

Example (based on format list):

  1. Format ($1,200.00, “General Number”)

Result: 1200

  1. Format (1,200, “Currency”)

Result: $1,200.00

  1. Format (1200, “Fixed”)

Result: 1200.00

  1. Format (1200, “Standard”)

Result: 1,200.00

  1. Format (.5, “Percent”)

Result: 50%

  1. Format (40, “Scientific”)

Result: 4.00E+01

  1. Format (40, ” Yes/No”)

Result: Yes

  1. Format (1, “True/False”)

Result: True

  1. Format (0, “On/Off”)

Result: Off

 

Example in VBA:

Ms Access Format Number-1

Ms Access Format Number-1

There is not much difference in the VBA code, except you need to add the syntax in the first line as the argument for the return code, mainly for Data Type.

Example in query:

Tutorial 2Ms Access Format Number-2. Ms Access Format Number-2

Tutorial 2Ms Access Format Number-2. Ms Access Format Number-2

In the sample query above, we a testing field which convert the quantity in general format into currency.

Related Access Database

  • access format number
  • ms access format number
  • Access format number function
  • format number in access expression
  • access format
  • MS Access FormatNumber
  • access database criteria format for number
  • format number in access
  • ms access format function
  • format function in access db


Access Database Tags: #access format function date #access format number as text #access format percent no decimal #format field in access query #ms access format currency in query #ms access format number no decimal #ms access format text #ms access format time

Powershell Query MS Access Database in Microsoft Windows Application
Powershell Query MS Access Database in Microsoft Windows Application
Powershell Access Database – Ms. Powershell is
Microsoft Access Weekday and Week Number Function
Microsoft Access Weekday and Week Number Function
Ms Access Weekday Function is purposed to


Must read×

Top