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.
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:
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:
The firstweekofyear requires the following settings:
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.
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.
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.
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:
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.