VBA has more than 120 built-in functions. You will look at some of the more popular of these, which we have split into logical groups.
Date and Time Functions
The family of date and time functions allow you to retrieve values for dates and times, extract the values, and add or subtract values (see the modDates module for examples), as demonstrated in the following code, with the results of executing these statements shown in Figure:
Examples of the various date and time functions.
Time values also have a default date value; the 30th December, 1899 is used to indicate that time-only information is being stored in a Date variable or database field type, which is shown in Figure. When viewing data in tables and queries, Microsoft Access hides this value and only shows the time component. There is no Time data type.
Time-only data has the default date 30 December 1899 assigned.
Default dates representing time-only data and moving data between database systems
Care must be taken when moving time-only data from Access into other data storage systems. For example, the earliest date in Microsoft SharePoint is 1 January 1900, which means that before moving the time data from Access to SharePoint, you need to change the date, 30 December 1899, to 1 January 1900 (you also need to check that you have no date data before this date). For SQL Server, the earliest date that can be held in a DateTime data type is 1 January 1753, so when entering time-only data, SQL Server will take 1 January 1900 to represent time-only data.
You can perform direct arithmetic operations on date data, as illustrated in Figure.
VBA supports direct date arithmetic and also provides functions for these operations.
Although VBA understands that when you add or subtract one from a date, you are adding or subtracting one day, you might prefer to use built-in functions, such as DateAdd, Date- Diff, and DateSerial for directly manipulating dates. For example, the first parameter of the DateAdd function is the interval, which can take a value in seconds, hours, quarters, days, and other increments, and then add the value to a date.
VBA supports a rich variety of string functions. The simplest string operation is adding, or concatenating, strings together, as demonstrated in the following, with the results shown in Figure:
The results of string concatenation.
Note that when you concatenate strings, you are more likely to use the ”&“ (ampersand)character rather than the “+” (plus) character because this prevents NULL values from propagating through string expressions, making the overall result NULL (unless, of course, you want to have NULLs propagate through the calculation; adding NULL to a string sets the result to NULL and also requires the use of a variant variable to hold the result).
The Nz function will convert NULL values to a safe value (in this case an empty string), and this is often combined with the Trim function to remove any leading or trailing spaces.
The following is a summary of important string functions:
Functions that return variant or string data
You might notice that many of the string functions (and some other VBA functions such as the Format function) have two forms; for example, UCase and UCase$. This is to large extent historical (beginning on page, we discuss the use of a $ symbol at the end of a variable name to indicate the data type); the UCase function has an advantage over the UCase$ function in that it can return NULL values. The current Help system does not mention the older forms such as UCase$ (but if you search in the object browser you will find these functions). The result from using UCase$ with NULL is shown in Figure.
An error message appears because the older UCase$ function cannot handle NULL values.
The Format Function
You use the Format function to display numeric, date/time, and string data, by using builtin and custom formatting (there is an extensive description of this function in the Help system). For numeric data, the Format function supports separate formatting options when values are positive, negative, zero, or null; for formatting dates there are options for the FirstDayOfWeek and FirstWeekOfYear.
The ASC Function
The ASC function returns an integer representing a character code. In the following example,the ASC function removes all characters in a string, except letters and digits:
The Mid Function
The Mid string function is one of the string extractors (Mid, Left, Right). It has a unique property that is not shared by the Left and Right string functions; you can use the Mid string function to directly replace values inside a string, as demonstrated in the following:
MS Access Related Tutorials
|Oracle 8i Tutorial||VBA For Excel Tutorial|
|Management Information systems Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Dashboards Tutorial|
|Excel Data Analysis Tutorial||Excel pivot tables Tutorial|
|Excel Power View Tutorial|
MS Access Related Interview Questions
|Oracle 8i Interview Questions||VBA For Excel Interview Questions|
|Management Information systems Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft FrontPage Interview Questions|
|Excel Data Analysis Interview Questions||Excel pivot tables Interview Questions|
|Excel Power View Interview Questions||Access Control List Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.