Javatpoint Logo
Javatpoint Logo

Ms Access Format

In this module, you will learn to implement the format function in MS Access. The user can pass numerical values as the parameter for the format function.

The format function enables the user to pass a numerical value in the function and return a variant string. The formatting is performed based on the instructions specified in the format expression.

Note: There is a problem when you use this function in MS Access. The last Monday of the appearance that should be regarded as the 1 week for the next year is regarded as the 53 weeks of that year only. So, check the last week of the year because it may return the wrong week number.

Syntax:

The syntax of the function in MS Access is as follows:

Parameters Passed in Format Function

Expression: It is necessary to pass an expression in the format function. In the Format function, the user can pass these two parameters: the first parameter should be a valid expression that specifies the value formatted by the function.

Format: It is unnecessary to pass the format parameter in the function. The format should either be a valid named or a user-defined format expression. This parameter is used to specify the format that is to be applied to the expression. Numerous formats can be specified in the system, or you can also define a format of your own.

Some of the predefined formats in MS Access are:

Format Explanation
Numerical Value The format is used to represent the numerical values. The numerical value is represented without commas in between.
Currency They are used to represent the monetary transactions in the application. The numerical values include a thousand operators. The value is represented up to two decimal places.
Fixed This format requires printing at least one digit to the left of the decimal point and two digits after the decimal point. The user can print more digits on either side too.
Standard This is one of the best formats to represent the large numerical value making it relatively easier to understand. It includes thousands of separators. The numerical value must contain a digit before the decimal point and two digits after the point.
Percent The percent format is used to display the percentage value of the number. The percentage value is equal to the number times 100. the percentage is represented by a percentage sign at the end of the value. The value is represented up to two places after the decimal point.
Scientific The value is represented in the scientific notation. It only represents a single digit before the decimal point, and the values are represented using exponents.
Yes/No If the number is 0, then it will be regarded as No. For any other value, it will be regarded as Yes.
True/False If the number is 0, then it will be regarded as False for any other value. It will be regarded as True.
On/Off If the number is 0, then it will be regarded as Off. For any other value, it will be regarded On.

Other Parameters in Format Function

Two additional parameters can be passed in the format function in MS Access. These two parameters are firstdayofweek and firstweekofyear. These features are used when the user wants to format datetime at the end of the year.

To add these parameters to the function, the user needs to follow the given syntax:

Firstdayofweek: This parameter is optional. It contains a constant value that is used to specify the first day of the week.

Firstweekofyear: This is also an optional parameter. The constant value specifies the first week of the year.

The firstdayofweek includes the following settings:

Constant Value Description
vbUseSystem 0 The value is used to implement NLS API settings in the arguments.
vbSunday 1 It is the default value for the argument. It denotes Sunday.
vbMonday 2 It denotes Monday.
vbTuesday 3 It denotes Tuesday.
vbWednesday 4 It denotes Wednesday.
vbThursday 5 It denotes Thursday.
vbFriday 6 It denotes Friday.
vbSaturday 7 It denotes Saturday.

The firstweekofyear requires the following settings:

Constant Value Description
vbUseSystem 0 The value is used to implement NLS API settings in the arguments.
vbFirstJan1 1 It is the default value for this parameter. It begins with the week in which 1 Jan occurs.
vbFirstFourDays 2 The first week of the year will be the first week that has four days in the year.
vbFirstFullWeek 3 The first week of the year will be the first full week of that year.

What happens if the user does not specify a number without specifying the format in the function?

If the user does not specify a number when using the format function, the format function works similarly to the Str function in MS Access. The problem with using the format function is that when working with numbers is that it does not provide any extra space to store the sign of the value. That is no leading space for the sign in case of positive numbers. But if the user converts the number using the Str function instead of the format, the numbers have leading spaces for storing the sign.

Suppose the user formats a non-localized numeric string. The format can either be predefined or can be user-defined. The user can also define a format to ensure that the numerical format suits all the user's needs.

Note: If you use the Calender property in the format function, you must check the Calendar property settings. If the calendar's settings are in Gregorian, then the expression passed in the function should also be in Gregorian. If the VBA Calendar property setting is in Hijri, then the expression should be in Hijri too.

Most of the time, we use Gregorian Calender, and if the calendar property settings are set to Gregorian, then the symbols for the date, month, and year remain the same. But if the settings are set to the Hijri calendar, then the symbols will be changed accordingly.

Points to Remember

The user can use the format function to format different data types. Below is a list of data types that can be formatted using the function and the format type.

To Format To Perform Formatting
Numbers The application provides various predefined numeric formats. The user can also define a numeric format of their own.
Date and time The application provides various predefined date and time formats. The user can also define a date and time format of their own.
Date and time serial numbers The user can either use the date and time formats or can use a numerical format to format such data.
Strings To format the string, the user can define a string format that fulfills all the requirements of the user.

Implementing the Format Function in MS Access

The format function is implemented with an expression. This means that the user can implement the format function wherever the user uses expressions.

You can directly implement format functions in a query or when using the expression in the Control Source property in the report. The format function can also be implemented when assigning an expression as the alias for a part of a specific field.

Example:

Let us see some examples to understand how to use the Format function in MS Access:

Implementing the Format Function In VBA Code

Let us see some examples to understand how to use the Format function in the VBA code in Microsoft Access:

In the example above, the variable PVal was declared as a string and was assigned '0.567' as the value. But as we implemented the Format function, the variable now stores "56.70%".

Implementing the Format Function In SQL/Queries

Let us see some examples to understand how to use the Format function in the Structured Query Language or in the Queries:

The query enables the user to input the Unit Price in the system as a numerical value, and the numerical value entered will be converted as a currency value. The currency value is used to represent the monetary values in the application. The numerical values are separated by thousand operators (,). The value is represented up to two decimal places. Since we have not used any labels in the query, the result after performing formatting will be displayed in the column named Expr.

The user can change the name of the column directly in the query making it easier and simpler to understand.

For Example:

When you implement this query, the result will be displayed in the column named CostOfProduct.

Below, we have used the Format function in a query in MS Access:

Ms Access Format

Using the Format Function with Zero-length Strings

The MS Access Version 2.0 and the previous versions allow the user to implement the Format function to return one value for a zero-length string and another for the value in the string.

But, in the later versions of the Access, the user must first check if the string is Null or not. To check it, the user can use the IIf function in the expression. And once checked that the string is Null, the user can return the value based on it.

Note: This is only required when the string is Null; else, they work similarly to other Format functions.







Youtube For Videos Join Our Youtube Channel: Join Now

Feedback


Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA