Open In App

FormatCurrency() and FormatDateTime() Function in MS Access

Last Updated : 17 Jan, 2022
Like Article

1. FormatCurrency() Function : 
FormatCurrency() Function in MS Access is used to Returns an expression formatted as a currency value using the currency symbol defined in the system control panel. 

Syntax :  

Expression [, NumDigitsAfterDecimal ] 
           [, IncludeLeadingDigit ] 
           [, UseParensForNegativeNumbers ] 
           [, GroupDigits ] // It represent group digit.

Parameter : 
FormatCurrency() accept five parameters as mentioned above and described below.  

  • Expression – 
    It identifies Expression to be formatted. 
  • NumDigitsAfterDecimal – 
    In this parameter NumDigitsAfterDecimal identifies Numeric value indicating how many places to the right of the decimal are displayed. -1 is the Default value, which indicates that the computer’s regional settings are used.It is optional. 
  • IncludeLeadingDigit – 
    In this parameter LeadingDigit indicates whether or not a leading zero is displayed for fractional values. It is optional. 
  • UseParensForNegativeNumbers – 
    It indicates whether or not to place negative values within parentheses . It is optional. 
  • GroupDigits – 
    It indicates whether or not numbers are grouped using the group delimiter specified in the computer’s regional settings.It is also optional

Note : 
The IncludeLeadingDigit, UseParensForNegativeNumbers, and GroupDigits arguments have the following value.-1 for True, 0 for False, and -2 for Default value. 

Returns : 
It returns the formatted currency value. 

Example-1 : 
Converting for Positive currency value.  

Select FormatCurrency(1200.2) as New_Currency;

Output : 

$1, 200.20

Example-2 : 
Converting for negative currency value.  

Select Formatcurrency(-1000, 2, -1, -1, -1) 
AS New_Currency;

Output : 

($1, 000.00)

2. FormatDateTime() Function : 
FormatDateTime() Function in MS Access is used to Returns an expression formatted as a date or time. 

Syntax :  

FormatDateTime( Date [, NamedFormat ] )

Parameter : 
FormatDateTime() accept two parameters as mentioned above and described below.  

  • Date – 
    It identifies expression to be formatted. 
  • NamedFormat – 
    It is numeric value that indicates the date/time format used. If omitted, GeneralDate is used. 0 Is used for General Date, 1 is used for Long Date, 2 for short Date, 3 for short Time, 4 for Long Time.

Returns : 
It returns a formatted Datetime expression. 

Example-1 : 
Formatting to long Date.  

SELECT FormatDateTime(#17/04/2004#, 1) 
as New_DFormat

Output : 

Saturday, April 17, 2004

Example-2 : 
Formatting to long Time.  

SELECT FormatDateTime(#12:30#, 4) 
as Long_Format;

Output : 

12:30:00 PM 

Like Article
Suggest improvement
Share your thoughts in the comments

Similar Reads