Functions for Expressions
You can call a number of functions when you are building an expression , including:
String (character) functions
Date functions
Mathematical functions
System functions
Other functions, such as those to convert data from one type to another
User-defined functions
For more information, see Functions.
If you are creating queries (not views, stored procedures, or triggers) that might be run against different databases, you can also use ODBC functions. ODBC syntax includes the "fn" qualifier in front of the function name and braces around the entire function. For example, the following expression uses an ODBC function to convert text to lowercase letters:
{fn LCASE ( address ) }
The Query Designer can help you work with functions by:
Correctly inserting quotation marks in function arguments
Validating the data types of arguments
Validating the data types of return values
For information on ODBC, see the Data Access Services section of the MSDNĀ® Online Library Microsoft Web site. For more information on functions, see User-Defined Functions.
Note You can use a special set of functions, the aggregate functions such as SUM( ) and AVG( ), to create queries that summarize data. For details, see Summarizing and Grouping.
String Functions
The following table contains samples of string functions. For more information, see String Functions and Using String Functions.
Function | Description | Example |
---|---|---|
LCASE( )1, LOWER( ) |
Converts strings to lowercase |
|
LTRIM( ) | Removes leading spaces from a string |
|
SUBSTRING( ) | Extracts one or more characters from a string |
|
UCASE( )1, UPPER( ) |
Converts strings to uppercase |
|
1 If calling as an ODBC function, use syntax such as: { fn LCASE(
text) }
.
Date Functions
The following table contains samples of date functions. For more information, see Date and Time Functions.
Function | Description | Example |
---|---|---|
DATEDIFF( ) | Calculates an interval between two dates. |
|
DATEPART( ) | Returns the specified portion of a date or datetime column, including the day, month, or year. |
|
CURDATE( )1, GETDATE( ) or DATE( ) |
Returns the current date in datetime format. This function is useful as input for many other date functions, such as calculating an interval forward or backward from today. |
|
1 If calling as an ODBC function, use syntax such as: { fn CURDATE() }
.
Mathematical Functions
The following functions are typical of those available in many databases. Refer to Mathematical Functions for more information.
Note You can use the aggregate functions AVG( ), COUNT( ), MAX( ), MIN( ), and SUM( ) to create averages and totals in your report. For details, see Summarizing and Grouping.
Function | Description | Example |
---|---|---|
ROUND( ) | Rounds a number off to the specified number of decimal places |
|
FLOOR( ) | Rounds a number down to the nearest (smallest) whole number |
|
CEILING( ) | Rounds a number up to the nearest whole number |
|
System Functions
The following functions are typical of those available in many databases. For more information, see System Functions.
Function | Description | Example |
---|---|---|
DATALENGTH( ) | Returns the number of bytes used by the specified expression |
|
USER( )1, USER_NAME( ) |
Returns the current user name |
|
1 If calling as an ODBC function, use syntax such as: { fn USER() }
.
Other Functions
The following functions illustrate utility functions available in many databases. For more information, see Functions.
Function | Description | Example |
---|---|---|
CONVERT( ) | Converts data from one data type into another. Useful to format data or to use the contents of a data column as an argument in a function that requires a different data type. |
|
SOUNDEX( ) | Returns the Soundex code for the specified expression, which you can use to create "sounds like" searches. |
|
STR( ) | Converts numeric data into a character string so you can manipulate it with text operators. |
|