FormatCurrency() and FormatDateTime() Function in MS Access

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 :  

FormatCurrency 
( 
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 : 

Example-2 : 
Converting for negative currency value.  

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

Output : 

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 : 

Example-2 : 
Formatting to long Time.  

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

Output : 

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL