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 * 30 End Type Dim Customer As CustomerRecord ' Declare variables. Dim MyInt As Integer, MyCur As Currency Dim MyString, MyLen MyString = "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 Function Dim MyString, MyLen MyString = "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.