Applies ToAccess for Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.

Syntax

Len( string | varname)

The Len function syntax has these arguments:

Argument

Description

string

Any valid string expression. If string contains Null, Null is returned.

varname

Any valid variable name. If varname contains Null, Null is returned. If varname is a Variant, Len treats it the same as a String and always returns the number of characters it contains.

Remarks

One (and only one) of the two possible arguments must be specified. With user defined types, Len returns the size as it will be written to the file.

Note:  Use the LenB function with byte data contained in a string, as in double-byte character set (DBCS) languages. Instead of returning the number of characters in a string, LenB returns the number of bytes used to represent that string. With user-defined types, LenB returns the in-memory size, including any padding between elements. For sample code that uses LenB, see the second example in the example topic.

Note:  Len may not be able to determine the actual number of storage bytes required when used with variable-length strings in user-defined data types.

Query example

Expression

Results

SELECT ProductID, Len(ProductID) AS ProductLen FROM ProductSales;

Returns the values from the field "ProductID" and the length of those values in the columns ProductLen.

VBA examples

Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.

The first example uses Len to return the number of characters in a string or the number of bytes required to store a variable. The Type...End Type block defining CustomerRecord must be preceded by the keyword Private if it appears in a class module. In a standard module, a Type statement can be Public.

Type CustomerRecord    ' Define user-defined type.    ID As Integer    ' Place this definition in a     Name As String * 10    ' standard module.    Address As String * 30End TypeDim Customer As CustomerRecord    ' Declare variables.Dim MyInt As Integer, MyCur As CurrencyDim MyString, MyLenMyString = "Hello World"    ' Initialize variable.MyLen = Len(MyInt)    ' Returns 2.MyLen = Len(Customer)    ' Returns 42.MyLen = Len(MyString)    ' Returns 11.MyLen = Len(MyCur)    ' Returns 8.

The second example uses LenB and a user-defined function (LenMbcs) to return the number of byte characters in a string if ANSI is used to represent the string.

Function LenMbcs (ByVal str as String)    LenMbcs = LenB(StrConv(str, vbFromUnicode))End FunctionDim MyString, MyLenMyString = "ABc"' Where "A" and "B" are DBCS and "c" is SBCS.MyLen = Len(MyString)' Returns 3 - 3 characters in the string.MyLen = LenB(MyString)' Returns 6 - 6 bytes used for Unicode.MyLen = LenMbcs(MyString)' Returns 5 - 5 bytes used for ANSI.

String functions and how to use them

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.