You're invited to try Microsoft 365 for free

Stay a step ahead with Microsoft 365. Subscribe now >

Access Functions (by category)

Access Functions (by category) Access for Microsoft 365 Access 2021 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007 More... Less. This article contains links to articles that provide details about common functions used in expressions in Microsoft Access.
Applies To: Access for Microsoft 365, Access 2021, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007

Choose Function

Choose returns a value from the list of choices based on the value of index. If index is 1, Choose returns the first choice in the list; if index is 2, it returns the second choice, and so on. You can use Choose to look up a value in a list of possibilities. For example, if index evaluates to 3 and choice-1 = "one", choice-2 = "two", and choice ...
Applies To: Access for Microsoft 365, Access 2021, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007

String functions and how to use them

Subtract 1 from that value and you get the correct number of characters for the Left function to return. Seems a little complicated at first, but with a little experimentation you can combine two or more expressions to get the results you want. For more information about using string functions, see Using string functions in your Access SQL queries.
Applies To: Access for Microsoft 365, Access 2021, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007

Mid Function

The second example use MidB and a user-defined function (MidMbcs) to also return characters from string. The difference here is that the input string is ANSI and the length is in bytes. Function MidMbcs(ByVal str as String, start, length) MidMbcs = StrConv(MidB(StrConv(str, vbFromUnicode), _ start, length), vbUnicode) End Function Dim MyString
Applies To: Access for Microsoft 365, Access 2021, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007

Replace Function

Microsoft Office Access 2007 only. Performs a comparison based on information in your database. Return Values. Replace returns the following values: If. Replace returns. expression is zero-length. ... The return value of the Replace function is a string, with substitutions made, ...
Applies To: Access for Microsoft 365, Access 2021, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007

StrComp Function

Microsoft Office Access 2007 only. Performs a comparison based on information in your database. Return Values. The StrComp function has the following return values: If. ... This example uses the StrComp function to return the results of a string comparison. If the third argument is 1, a textual comparison is performed; if the third argument is ...
Applies To: Access for Microsoft 365, Access 2021, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007

Val Function

Val (" 1615 198th Street N.E.") In the code below, Val returns the decimal value -1 for the hexadecimal value shown: Val ("&HFFFF") Note: The Val function recognizes only the period (.) as a valid decimal separator. When different decimal separators are used, as in international applications, use CDbl instead to convert a string to a number.
Applies To: Access for Microsoft 365, Access 2021, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007

UCase Function

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. This example uses the UCase function to return an uppercase version of a string. Dim LowerCase, UpperCase. LowerCase = "Hello World 1234" ' String to convert. UpperCase = UCase (LowerCase)
Applies To: Access for Microsoft 365, Access 2021, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007

Choose the right date function

Access provides built-in functions you can use in expressions to format, create, parse, and calculate dates and times. Pick a table that matches what you want to do. I want to… Display a date or time in a specific format. Split a date or time into parts. Turn numbers and text into dates and times
Applies To: Access 2013
1 - 9 of 9