Using Built-In Functions - MS Access

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.

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.

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.

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.

String Functions

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.

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:

  • Left, Right, Mid String extraction.
  • Len Sets the length of a string.
  • Trim, LTrim, and RTrim Removes spaces.
  • Space and String Adds space characters (Space) or a number of repeating characters (String).
  • Upper, Lower, and StrConv (supports proper case) Changes case.
  • InStr, InStrReverse Searches for a string inside another string.
  • Replace Replaces a substring with another string.
  • Split Breaks a string into words.
  • Join Reassembles words that were broken up by Split back into strings.
  • ASC and Chr Get the ASCII code for a character (ASC), or make the character from the ASCII code (Chr).
  • Format Formats a string.
  • StrComp Compares strings.
  • vbCR, vbCRLF, vbLF, vbTab Constants for formatting.

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.

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:

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

MS Access Topics