MS Access Mid() Function

The Mid() function in MS Access extracts a portion (substring) of a string, starting at a specified position and extending for a given length. It's a very useful function for working with text data and retrieving specific parts of strings.

Mid(): Definition and Usage

Mid() allows you to select a specific part of a string. You specify where to begin the extraction and how many characters to include. If you omit the length, it extracts all characters from the starting position to the end of the string. The starting position is 1-based (the first character is at position 1).

Syntax

Syntax

Mid(string, start, length)

Parameter Values

Parameter Description
string The string from which to extract the substring. This is required.
start The starting position of the substring (1-based index). This is required.
length (Optional) The number of characters to extract. If omitted, the function extracts to the end of the string.

Examples

Extracting a Substring

This example extracts characters from position 3 to the end of the string "SQL Tutorial".

Syntax

SELECT Mid("SQL Tutorial", 3) AS ExtractString;
Output

L Tutorial

Extracting a Substring from a Column

This example extracts six characters from the 'CustomerName' column, starting at position 4 (assuming a 'Customers' table exists with a 'CustomerName' column).

Syntax

SELECT Mid(CustomerName, 4, 6) AS ExtractString
FROM Customers;
Output

ExtractString
-------------
(A six-character substring from each CustomerName, starting at position 4, will be displayed here.)
**Note:** The second example's output will depend on the data in your `Customers` table. Each row will show a six-character substring from the `CustomerName` column, starting at the fourth character. If a `CustomerName` is shorter than ten characters, the function will return the remainder of the string.