ActPresentation.com HOME FAQ forum info@ActPresentation.com
Addons for ACT! by Sage :
Abacus Calculated Fields for ACT!
make calculations involving ACT! fields
  • Create calculations involving ACT! fields
  • Assign calculation formula to field (make a field "calculated")
  • Calculate Total
  • Run your calculations either automatically or manually
  • Over 80 supported functions (mathematical, string, datetime, financial)
  • Mortgage/loan calculation
  • Use "IF" and "CASE" conditional statements to build calculation scenarios
  • Automatic data entry (fill fields automatically)
  • Calculate Interest Rates
  • Accounting software functions
  • Mass replace field values
Dynamic Field Color for ACT! by Sage
highlight fields depending on field values
  • Calculate and change field colors "ON THE FLY"
  • Conditional color calculations using "IF" and "CASE" statements
  • Perform data entry validation
  • Highlight incorrecly entered fields
  • Highlight fields depending on field values
New Contact List for ACT! by Sage
say "good bye" to look-ups!
  • Contact list is EMBEDDED in Detail View
  • List can be docked or floating
  • Built-in search by any field
  • Built-in FILTER bar - build quick LOOKUPS!
  • "Two monitors view" in floating mode
PicturePlus for ACT! by Sage
advanced features for picture fields + Scanner & Camera support
  • Links picture fields to Image files
  • Links to PDF, Video and Audio files
  • Scan directly to picture fields!
  • Load images from digital cameras
  • Full size image view
  • Open pictures in your favourite image viewer
  • Clipboard support (copy/paste images)
  • Drag'n'drop image files to/form ACT!
Employment History for ACT! by Sage
track who works for whom records
  • Associate chronological employment history with contacts
  • Enter work period ( start date, end date ) for every company associated with contact
  • Usefull for recruitment agencies
Quick Search for ACT! by Sage
find contacts quickly and easily!
  • Quick search tool for contacts, companies, groups and opportunities
  • Search by several fields simultaneously
Presentation for ACT! by Sage
run slide show based on ACT! report
  • Run slide show based on ACT! report
  • Convert report to MS Powerpoint Presentation

Our add-ons are compatible with:

ACT! 2005 (v7)
ACT! 2006 (v8)
ACT! 2007 (v9)
ACT! 2008 (v10)
ACT! 2009 (v11)
ACT! 2010 (v12)
ACT! 2011 (v13)
ACT! 2012 (v14)
Sage ACT! 2013 (v15)
Swiftpage ACT! 2014 (v16)
Swiftpage ACT! 2015 (v17)
Swiftpage ACT! 2016 (v18)
Swiftpage ACT! 2017 (v19)
Swiftpage ACT! 2018 (v20)
Swiftpage ACT! 2019 (v21)

forum
Abacus Calculated Fields for ACT!
On-line help - Functions

ABS
Returns the absolute, positive value of the given numeric expression.

Syntax
ABS ( numeric_expression )

Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.

Return Types
Returns the same type as numeric_expression.

Examples

ABS( [NumericField] )
ABS( -1.0 ) -- the result is: 1.0
ABS( 0.0 )-- the result is: 0.0
ABS( 1.0 )-- the result is: 1.0



ACOS
Returns the angle, in radians, whose cosine is the given float expression; also called arccosine.

Syntax
ACOS ( float_expression )

Arguments float_expression
Is an expression of the type float or real, with a value from -1 through 1. Values outside this range return NULL.

Return Types
float

Examples

ACOS( [NumericField] )
ACOS(-1) -- the result is: 3.14159



AGE
Returns the age of a date in years.


Syntax
AGE ( datetime_expression )

Arguments
datetime_expression
Is an expression of the datetime data type.

Return Types
int

Examples

AGE( [Birth Date] )


ASCII
Returns the ASCII code value of the leftmost character of a character expression.

Syntax
ASCII ( character_expression )

Arguments
character_expression
Is an expression of the type char or varchar.

Return Types
int

Examples

ASCII( [StringField] )
ASCII('test') -- the result is: 116



ASIN
Returns the angle, in radians, whose sine is the given float expression (also called arcsine).

Syntax
ASIN ( float_expression )

Arguments
float_expression
Is an expression of the type float, with a value from -1 through 1. Values outside this range return NULL.

Return Types
float

Examples

ASIN( [FloatField] )
ASIN(-1) -- the result is: -1.5708



ATAN
Returns the angle in radians whose tangent is the given float expression (also called arctangent).

Syntax
ATAN ( float_expression )

Arguments
float_expression
Is an expression of the type float.

Return Types
float

Examples
ATAN( [FloatField] )
ATAN(-45.01) -- the result is: -1.54858
   

 

ATN2
Returns the angle, in radians, whose tangent is between the two given float expressions (also called arctangent).

Syntax
ATN2 ( float_expression , float_expression )

Arguments
float_expression
Is an expression of the float data type.

Return Types
float

Examples
ATN2( [FloatField1], [FloatField2] )
ATN2( 35.175643, 129.44 ) -- the result is: 0.265345
    

 

CAST
Explicitly converts an expression of one data type to another.

Syntax
CAST ( expression AS data_type )

Arguments
expression
Is an expression to convert.
data_type
Is the target data type.

Examples
CAST( SQUARE([Field1]) as int )
'The price is ' + CAST( [NumericField] as varchar(12) )

 

CEILING
Returns the smallest integer greater than, or equal to, the given numeric expression.

Syntax
CEILING ( numeric_expression )

Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.

Return Types
Returns the same type as numeric_expression.

Examples
CEILING([NumericField])
CEILING( 123.45) -- the result is: 124
CEILING(-123.45) -- the result is: -123
CEILING(0.0) -- the result is: 0

 

CHAR
A string function that converts an int ASCII code to a character.

Syntax
CHAR ( integer_expression )

Arguments
integer_expression
Is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.

Return Types
char(1)

Remarks
CHAR can be used to insert control characters into character strings. The table shows some commonly used control characters.

Control character

Value

Tab

CHAR(9)

Line feed

CHAR(10)

Carriage return

CHAR(13)

Examples
CHAR( 78 ) -- the result is: N
[Contact] + CHAR(13) + [Address 1]  + CHAR(13) + [City] 

/*  
the result is:

Chris Huffman
13 East 54th St.
New York
*/

 

CHARINDEX
Returns the starting position of the specified expression in a character string.

Syntax
CHARINDEX ( expression1 , expression2 [ , start_location ] )

Arguments
expression1
Is an expression containing the sequence of characters to be found.
expression2
Is an expression, usually a field searched for the specified sequence. expression2 is of the character string data type category.
start_location
Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.

Return Types
int

Remarks
If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.
If either expression1 or expression2 is NULL, CHARINDEX returns NULL.
If expression1 is not found within expression2, CHARINDEX returns 0.

Examples
CHARINDEX( 'Huf', [Contact] )  -- the result is 7 for contact 'Chris Huffman'

 

CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments
expression
Is an expression to convert.
data_type
Is the target data type.
length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.
style
Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).
In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Without century (yy)

With century (yyyy)

  Standard

  Input/Output**

-

0 or 100 (*)

Default

mon dd yyyy hh:miAM (or PM)

1

101

USA

mm/dd/yy

2

102

ANSI

yy.mm.dd

3

103

British/French

dd/mm/yy

4

104

German

dd.mm.yy

5

105

Italian

dd-mm-yy

6

106

-

dd mon yy

7

107

-

Mon dd, yy

8

108

-

hh:mm:ss

-

9 or 109 (*)

Default + milliseconds

mon dd yyyy hh:mi:ss:mmmAM (or PM)

10

110

USA

mm-dd-yy

11

111

JAPAN

yy/mm/dd

12

112

ISO

yymmdd

-

13 or 113 (*)

Europe default + milliseconds

dd mon yyyy hh:mm:ss:mmm(24h)

14

114

-

hh:mi:ss:mmm(24h)

-

20 or 120 (*)

ODBC canonical

yyyy-mm-dd hh:mi:ss(24h)

-

21 or 121 (*)

ODBC canonical (with milliseconds)

yyyy-mm-dd hh:mi:ss.mmm(24h)

-

126(***)

ISO8601

yyyy-mm-dd Thh:mm:ss:mmm(no spaces)

-

130*

Kuwaiti

dd mon yyyy hh:mi:ss:mmmAM

-

131*

Kuwaiti

dd/mm/yy hh:mi:ss:mmmAM

* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
** Input when converting to datetime; output when converting to character data.
*** Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when converting from datetime or smalldatetime values by using an appropriate char or varchar data type length.
This table shows the style values for float or real conversion to character data.

Value

Output

0 (default)

Six digits maximum. Use in scientific notation, when appropriate.

1

Always eight digits. Always use in scientific notation.

2

Always 16 digits. Always use in scientific notation.

In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.

Value

Output

0 (default)

No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1

Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2

No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

Return Types
Returns the same value as data type 0.

Remarks
Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST (CONVERT) function to be specified. This chart shows all explicit and implicit data type conversions allowed.
  
View conversations table

Note Because Unicode data always uses an even number of bytes, use caution when converting binary or varbinary to or from Unicode supported data types. For example, this conversion does not return a hexadecimal value of 41, but of 4100: Examples

-- converts Birth Date field to string using style 113 (Europe default + milliseconds )
CONVERT ( varchar(64),  [Birth Date]   , 113 )  
-- result is  '04 Apr 1964 20:00:00:000'  for contact 'Chris Huffman'


 

COS
A mathematic function that returns the trigonometric cosine of the given angle (in radians) in the given expression.

Syntax
COS ( float_expression )

Arguments
float_expression
Is an expression of type float.

Return Types
float

Examples
COS( [NumericField] )
COS(14.78) -- result is: -0.599465

 

COT
A mathematic function that returns the trigonometric cotangent of the specified angle (in radians) in the given float expression.

Syntax
COT ( float_expression )

Arguments
float_expression
Is an expression of type float.

Return Types
float

Examples
COT( [NumericField] )
COT(124.1332) -- result is: -0.040312


 

DATEADD
Returns a new datetime value based on adding an interval to the specified date.

Syntax
DATEADD ( datepart , number, date )

Arguments
datepart
Is the parameter that specifies on which part of the date to return a new value. The table lists the dateparts and abbreviations.

Datepart

Abbreviations

Year

yy, yyyy

quarter

qq, q

Month

mm, m

dayofyear

dy, y

Day

dd, d

Week

wk, ww

Hour

hh

minute

mi, n

second

ss, s

millisecond

ms

number
Is the value used to increment datepart. If you specify a value that is not an integer, the fractional part of the value is discarded. For example, if you specify day for datepart and 1.75 for number, date is incremented by 1.
date
Is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Return Types
Returns datetime, but smalldatetime if the date argument is smalldatetime.

Examples
DATEADD( day, 10,  [Birth Date] ) -- add 10 days to 'Birth Date' field

 

DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates.

Syntax
DATEDIFF ( datepart , startdate , enddate )

Arguments
datepart
Is the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations.

Datepart

Abbreviations

Year

yy, yyyy

quarter

qq, q

Month

mm, m

dayofyear

dy, y

Day

dd, d

Week

wk, ww

Hour

hh

minute

mi, n

second

ss, s

millisecond

ms

startdate
Is the beginning date for the calculation. startdate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
enddate
Is the ending date for the calculation. enddate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Return Types
integer

Remarks
startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.
The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1.

Examples
DATEDIFF( year, [Birth Date], getdate() ) -- calculate age in years
DATEDIFF( month,[Birth Date], getdate() ) % 12  -- and months
DATEDIFF( month,[Birth Date], getdate() ) / 12  -- the same as 1st line( calculate age in years )

 

DATENAME
Returns a character string representing the specified datepart of the specified date.

Syntax
DATENAME ( datepart , date )

Arguments
datepart
Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations.

Datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

weekday

dw

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

The weekday (dw) datepart returns the day of the week (Sunday, Monday, and so on).
Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type for dates after January 1, 1753. Store as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.

Return Types
nvarchar

Examples
DATENAME( month,  [Birth Date] ) -- result is 'April' for contact 'Chris Huffman'


DATEPART
Returns an integer representing the specified datepart of the specified date.

Syntax
DATEPART ( datepart , date )

Arguments
datepart
Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations.

Datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

weekday

dw

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.
date
Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

Return Types
int

Remarks
The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.

Examples
DATEPART( month,  [Birth Date] ) -- result is 4 for contact 'Chris Huffman'

 

DateToStr
Explicitly converts an datetime expression to string in USA format 'MM/DD/YYYY'

Syntax
DateToStr ( datetime_expression )

Arguments
datetime_expression
Is an expression to convert.

Examples
DateToStr( [Birth Date] )

 

DateToString
Explicitly converts an datetime expression to string using specified format style.

Syntax
DateToString ( datetime_expression,  style )

Arguments
datetime_expression
Is an expression to convert.
style
Is the style of date format ( see CONVERT function description for style details )

Examples
DateToString( [Birth Date], 104 ) - convert to Germat format dd.mm.yy

 

DAY
Returns an integer representing the day datepart of the specified date.

Syntax
DAY ( date )

Arguments
date
Is an expression of type datetime or smalldatetime.

Return Type
int

Remarks
This function is equivalent to DATEPART(dd, date).

Examples
DAY('03/12/1998')  -- result is 12


DEGREES
Given an angle in radians, returns the corresponding angle in degrees.

Syntax
DEGREES ( numeric_expression )

Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.

Return Code Values
Returns the same type as numeric_expression.

Examples
DEGREES( PI()/2 )  -- result is 90 ( the number of degrees in PI/2 radians is 90 )


DIFFERENCE
Returns the difference between the SOUNDEX values of two character expressions as an integer.

Syntax
DIFFERENCE ( character_expression , character_expression )

Arguments
character_expression
Is an expression of type char or varchar.

Return Types
int

Remarks
The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4, with 4 indicating the SOUNDEX values are identical.

Examples
In the first part of this example, the SOUNDEX values of two very similar strings are compared, and DIFFERENCE returns a value of 4. In the second part of this example, the SOUNDEX values for two very different strings are compared, and DIFFERENCE returns a value of 0.

DIFFERENCE('Green','Greene')  -- result is 4
DIFFERENCE('Blotchet-Halls', 'Greene') -- result is 0

See Also
SOUNDEX  function


EXP
Returns the exponential value of the given float expression.

Syntax
EXP ( float_expression )

Arguments
float_expression
Is an expression of type float.

Return Types
float

Examples
CAST( ROUND(EXP([NummericField]),0) as INT ) -- calculate EXP, ROUND result and convert it to INT
ROUND( EXP(4),0 ) -- result is 55.0


FLOOR
Returns the largest integer less than or equal to the given numeric expression.

Syntax
FLOOR ( numeric_expression )

Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.

Return Types
Returns the same type as numeric_expression.

Examples
FLOOR([NumericField])
FLOOR(123.45) -- result is 123
FLOOR(-123.45) -- result is -124


FORMAT
The syntax of the format function is the following:

o    Format (expression;format)
  Ex: if field [Field1]=2343.3 then Format([Field1], "###0.00") returns 2343.30
    The format argument is a string of characters with one or more of the following characters:
    

@

A character should appear at this position. If there is no character for this position, a space is inserted. If there are more than one @, they are applied from right to left.

&

Same as @, except that no space is inserted if there is no character at this position.

!

Reverses the order in which @ and & are applied (ie. becomes from left to right).

Converts all characters to lowercase.

Converts all characters to uppercase.


Ex:      
Format(4142345678, "(@@@) @@@-@@@@")=(414) 234-5678
Format(2345678, "(@@@) @@@-@@@@")=(  ) 234-5678
Format(2345678, "!(@@@) @@@-@@@@")=(234) 567-8

    Number formatting:                                                 

(blank)The digit is returned without formatting 

0

A digit is supposed to appear at this position. If there is none, then 0 is displayed. If the format string contains more 0 than the number to be formatted, extra 0s are added in front or at the end.

#

Similar to 0 except that nothing is inserted if no digit is to appear

 

Combined with 0 or #, specifies the number of digits which should appear on each side of the decimal character.

%

Multiplies the number by 100 and adds a % sign.

,

Inserted in series of 0 or #, indicates the thousand separator. A double comma indicates that the number should be divided by 1000.

E-, E+ 

If the format string contains at least a 0 or #, converts the number to scientific notation.


Ex:      Format(2343.3,"0000.00")  = 02343. 3 0
Format(2343.3,"$###,###.00")  = $2,343.00 Format(45,  "+###") = +45


 

GETDATE
Returns the current system date and time.

Syntax
GETDATE ( )

Return Types
datetime

Examples
GETDATE()


GETUTCDATE
Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer.

Syntax
GETUTCDATE()

Return Types
datetime


ISNULL
Replaces NULL with the specified replacement value.

Syntax
ISNULL ( check_expression , replacement_value )

Arguments
check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.

Return Types
Returns the same type as check_expression.

Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.

Examples:

ISNULL( [Field 1],0 ) + ISNULL( [Field 2],0 )

 

LEFT
Returns the part of a character string starting at a specified number of characters from the left.

Syntax
LEFT ( character_expression , integer_expression )

Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or field. character_expression must be of a data type that can be implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.
integer_expression
Is a positive whole number. If integer_expression is negative, a null string is returned.

Return Types
varchar

Examples
LEFT( [Contact], 5 ) -- result is 'Chris' for contact 'Chris Huffman'
LEFT( 'abcdefg', 2 ) -- result is 'ab'

 

LEN
Returns the number of characters of the given string expression, excluding trailing blanks.

Syntax
LEN ( string_expression )

Arguments
string_expression
Is the string expression to be evaluated.

Return Types
int

Examples
LEN( [Contact] ) -- result is 13 for contact 'Chris Huffman'
LEN( 'abcdefg' ) -- result is 7

 

LOG
Returns the natural logarithm of the given float expression.

Syntax
LOG ( float_expression )

Arguments
float_expression
Is an expression of the float data type.

Return Types
float

Examples
LOG(5.175643) -- result is 1.64396
CAST( ROUND( LOG([NumericField]), 0 ) as INT )   -- calculate LOG, ROUND result and convert it to INT

 

LOG10
Returns the base-10 logarithm of the given float expression.

Syntax
LOG10 ( float_expression )

Arguments
float_expression
Is an expression of the float data type.

Return Types
float

Examples
LOG10(5.175643) -- result is 0.7139
CAST( ROUND( LOG10([NumericField]), 0 ) as INT )   -- calculate LOG10, ROUND result and convert it to INT

 

LOWER
Returns a character expression after converting uppercase character data to lowercase.

Syntax
LOWER ( character_expression )

Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

Return Types
varchar

Examples
LOWER( [Contact] ) -- result is 'chris huffman' for contact 'Chris Huffman'
LOWER( 'AbcDefG' ) -- result is 'abcdefg'

 

LTRIM
Returns a character expression after removing leading blanks.

Syntax
LTRIM ( character_expression )

Arguments
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

Return Type
varchar

Examples
LTRIM(  '   abcdefg' ) -- result is  'abcdefg'  
LTRIM( [StringField] )

 

MAX
Returns maximum from 2 numeric values.

Syntax
MAX ( numeric_expression1, numeric_expression2 )

Examples
MAX( [NumericField1], [NumericField2] )
MAX( 12, 76 ) -- the result is: 76
MAX( 7.6, 7.9 )  -- the result is: 7.9


MIN
Returns minimum from 2 numeric values.

Syntax
MIN ( numeric_expression1, numeric_expression2 )

Examples
MIN( [NumericField1], [NumericField2] )
MIN( 12, 76 ) -- the result is: 12
MIN( 7.6, 7.9 )  -- the result is: 7.6


MONTH
Returns an integer that represents the month part of a specified date.

Syntax
MONTH ( date )

Arguments
date
Is an expression returning a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753.

Return Types
int

Remarks
MONTH is equivalent to DATEPART(mm, date).
Always enclose datetime values in quotation marks. For earlier dates, store dates as character data.

Examples
MONTH( [Birth Date] ) -- result is 4 for contact 'Chris Huffman'
MONTH('03/12/1998') -- result is 3

 

NCHAR
Returns the Unicode character with the given integer code, as defined by the Unicode standard.

Syntax
NCHAR ( integer_expression )

Arguments
integer_expression
Is a positive whole number from 0 through 65535. If a value outside this range is specified, NULL is returned.

Return Types
nchar(1)

Examples
NCHAR(101) -- result is 'e'

 

PATINDEX
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

Syntax
PATINDEX ( '%pattern%' , expression )

Arguments
pattern
Is a literal string. Wildcard characters can be used; however, the % character must precede and follow pattern (except when searching for first or last characters). pattern is an expression of the short character data type category.
expression
Is an expression, usually a field that is searched for the specified pattern. expression is of the character string data type category.

Return Types
int

Remarks
If either pattern or expression is NULL, PATINDEX returns NULL.

Examples
PATINDEX('%huffm_n%', [Contact] ) -- result is 7 for contact 'Chris Huffman' ('_' sign is used as wildcard)

 

PI
Returns the constant value of PI.

>Syntax
PI ( )

Return Types
float

Examples
PI()


POWER
Returns the value of the given expression to the specified power.

Syntax
POWER ( numeric_expression , y )

Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
y
Is the power to which to raise numeric_expression. y can be an expression of the exact numeric or approximate numeric data type category.

Return Types
Same as numeric_expression.

Examples
POWER(2, 4) -- result is 16 ( 2 in the power 4 )
CAST( POWER([NumericField],4) as INT )

 

RADIANS
Returns radians when a numeric expression, in degrees, is entered.

Syntax
RADIANS ( numeric_expression )

Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.

Return Types
Returns the same type as numeric_expression.

Examples
CAST( ROUND(RADIANS([NummericField]),0) as INT ) -- calculate RADIANS, ROUND result and convert it to INT
RADIANS(-45.01) -- result is -0.7855

 

RAND
Returns a random float value from 0 through 1.

Syntax
RAND ( [ seed ] )

Arguments
seed
Is an integer expression that gives the seed or start value.

Return Types
float

Examples
RAND(1)

 

REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.

Syntax
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

Arguments
'string_expression1'
Is the string expression to be searched. string_expression1 can be of character or binary data.
'string_expression2'
Is the string expression to try to find. string_expression2 can be of character or binary data.
'string_expression3'
Is the replacement string expression string_expression3 can be of character or binary data.

Return Types
Returns character data if string_expression (1, 2, or 3) is one of the supported character data types. Returns binary data if string_expression (1, 2, or 3) is one of the supported binary data types.

Examples
--This example replaces the string cde in abcdefghi with xxx.
REPLACE( 'abcdefghicde', 'cde', 'xxx' ) -- result is abxxxfghixxx
REPLACE( [Contact] , 'Chris', 'C.' ) -- result is 'C. Huffman' for contact 'Chris Huffman'


REPLICATE
Repeats a character expression for a specified number of times.

Syntax
REPLICATE ( character_expression , integer_expression )

Arguments
character_expression
Is an alphanumeric expression of character data. character_expression can be a constant, variable, or field of either character or binary data.
integer_expression
Is a positive whole number. If integer_expression is negative, a null string is returned.

Return Types
varchar
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to convert explicitly character_expression.

Examples
REPLICATE( [Contact] ,2 ) -- result is 'Chris HuffmanChris Huffman' for contact 'Chris Huffman'
REPLICATE( 'Abc' ,3 ) -- result is 'AbcAbcAbc'


REVERSE
Returns the reverse of a character expression.

Syntax
REVERSE ( character_expression )

Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

Return Types
varchar

Remarks
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

Examples
REVERSE( 'Abc' ) -- result is 'cbA'
REVERSE( [Contact] ) -- result is 'namffuH sirhC' for contact 'Chris Huffman'


RIGHT
Returns the part of a character string starting a specified number of integer_expression characters from the right.

Syntax
RIGHT ( character_expression , integer_expression )

Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
integer_expression
Is the starting position, expressed as a positive whole number. If integer_expression is negative, an error is returned.

Return Types
varchar
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

Examples
RIGHT( [Contact], 7 ) -- result is 'Huffman' for contact 'Chris Huffman'
RIGHT( 'Abc', 2 ) -- result is 'bc'

 

ROUND
Returns a numeric expression, rounded to the specified length or precision.

Syntax
ROUND ( numeric_expression , length [ , function ] )

Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
length
Is the precision to which numeric_expression is to be rounded. When length is a positive number, numeric_expression is rounded to the number of decimal places specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.
function
Is the type of operation to perform. function must be int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Return Types
Returns the same type as numeric_expression.

Remarks
ROUND always returns a value. If length is negative and larger than the number of digits before the decimal point, ROUND returns 0.

Example

Result

ROUND(748.58, -4)

0

ROUND returns a rounded numeric_expression, regardless of data type, when length is a negative number.

Examples

Result

ROUND(748.58, -1)

750.00

ROUND(748.58, -2)

700.00

ROUND(748.58, -3)

1000.00

Examples
This example shows two expressions illustrating that with the ROUND function the last digit is always an estimate.

ROUND(123.9994, 3) -- result is 123.9990
ROUND(123.9995, 3) -- result is 124.0000

B. Use ROUND and rounding approximations
This example shows rounding and approximations.
ROUND(123.4545, 2) -- result is 123.4500
ROUND(123.45, -2)   -- result is 100.00
   
C. Use ROUND to truncate
This example demonstrates the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.
ROUND(150.75, 0) -- result is 151.00
ROUND(150.75, 0, 1) -- result is 150.00
   


RTRIM
Returns a character string after truncating all trailing blanks.

Syntax
RTRIM ( character_expression )

Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

Return Types
varchar

Remarks
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.

Examples
RTRIM(  'abcdefg   ' ) -- result is  'abcdefg'  
RTRIM( [StringField] )

 

SIGN
Returns the positive (+1), zero (0), or negative (-1) sign of the given expression.

Syntax
SIGN ( numeric_expression )

Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types
float

Examples
SIGN( [NummericField] )
SIGN( 123 ) -- result is 1
SIGN( -123 ) -- result is -1
SIGN( 0 ) -- result is 0

 

SIN
Returns the trigonometric sine of the given angle (in radians) in an approximate numeric (float) expression.

Syntax
SIN ( float_expression )

Arguments
float_expression
Is an expression of type float.

Return Types
float

Examples
SIN( [NummericField] )
SIN( 45.175643 ) -- result is 0.929607
          

 

SOUNDEX
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.

Syntax
SOUNDEX ( character_expression )

Arguments
character_expression
Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.

Return Types
char

Remarks
SOUNDEX converts an alpha string to a four-character code to find similar-sounding words or names. The first character of the code is the first character of character_expression and the second through fourth characters of the code are numbers. Vowels in character_expression are ignored unless they are the first letter of the string. String functions can be nested.

Examples
SOUNDEX ('Smith') -- result is 'S530'

 

SPACE
Returns a string of repeated spaces.

Syntax
SPACE ( integer_expression )

Arguments
integer_expression
Is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.

Return Types
char

Remarks
To include spaces in Unicode data, use REPLICATE instead of SPACE.

Examples
'Contact name:' + SPACE( 2 ) + [Contact] 
-- result is 'Contact name:  Chris Huffman' for contact 'Chris Huffman'


 

SQRT
Returns the square root of the given expression.

Syntax
SQRT ( float_expression )

Arguments
float_expression
Is an expression of type float.

Return Types
float

Examples
SQRT(4) -- result is 2.0
CAST( ROUND( SQRT( [NummericField]),0,0) as int )  -- calculate SQRT, ROUND result and convert it to INT


SQUARE
Returns the square of the given expression.

Syntax
SQUARE ( float_expression )

Arguments
float_expression
Is an expression of type float.

Return Types
float

Examples
SQUARE(2) -- result is 4.0
CAST( ROUND( SQUARE( [NummericField]),0,0) as int )  -- calculate SQUARE, ROUND result and convert it to INT


 

STR
Returns character data converted from numeric data.

Syntax
STR ( float_expression [ , length [ , decimal ] ] )

Arguments
float_expression
Is an expression of approximate numeric (float) data type with a decimal point.
length
Is the total length, including decimal point, sign, digits, and spaces. The default is 10.
decimal
Is the number of places to the right of the decimal point.

Return Types
char

Remarks
If supplied, the values for length and decimal parameters to STR should be positive. The number is rounded to an integer by default or if the decimal parameter is 0. The specified length should be greater than or equal to the part of the number before the decimal point plus the number's sign (if any). A short float_expression is right-justified in the specified length, and a long float_expression is truncated to the specified number of decimal places. For example, STR(12,10) yields the result of 12, which is right-justified in the result set. However, STR(1223, 2) truncates the result set to **. String functions can be nested.
Note To convert to Unicode data, use STR inside a CONVERT or CAST conversion function.

Examples
STR([NummericField])
A. Use STR
This example converts an expression consisting of five digits and a decimal point to a six-position character string. The fractional part of the number is rounded to one decimal place.
STR(123.45, 6, 1) -- result is '123.5'
When the expression exceeds the specified length, the string returns ** for the specified length.
STR(123.45, 2, 2) -- result is '**'
Even when numeric data is nested within STR, the result is character data with the specified format.
STR (FLOOR (123.45), 8, 3)-- result is '123.000'
B. Use the STR and CONVERT functions
This example compares the results of STR and CONVERT.
STR(3.147)  -- result is '3'
STR(3.147, 5, 2) -- result is '3.15'
STR(3.147, 5, 3) -- result is '3.147'


 

STUFF
Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax
STUFF ( character_expression , start , length , character_expression )

Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or field of either character or binary data.
start
Is an integer value that specifies the location to begin deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.
length
Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression.

Return Types
Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

Remarks
String functions can be nested.

Examples
This example returns a character string created by deleting three characters from the first string (abcdef) starting at position 2 (at b) and inserting the second string at the deletion point.

STUFF('abcdef', 2, 3, 'ijklmn') -- result is 'aijklmnef'
STUFF( [Contact] , 2, 5, '.') -- result is 'C.Huffman' for contact 'Chris Huffman'


 

SUBSTRING
Returns part of a character, binary, text, or image expression.

Syntax
SUBSTRING ( expression , start , length )

Arguments
expression
Is a character string, binary string, text, image, a field, or an expression that includes a field.
start
Is an integer that specifies where the substring begins.
length
Is an integer that specifies the length of the substring (the number of characters or bytes to return).

Examples
SUBSTRING( 'abcdefg', 3, 2  ) -- result is 'cd'
SUBSTRING( [Contact] , 1, 5 ) -- result is 'Chris' for contact 'Chris Huffman'

 

TAN
Returns the tangent of the input expression.

Syntax
TAN ( float_expression )

Arguments
float_expression
Is an expression of type float or real, interpreted as number of radians.

Return Types
float

Examples
TAN(  PI()/2  )  -- result is 1.63318e+016  
TAN( [NumericField] )


ToNum
Converts an character expression (string) to number.

Syntax
ToNum  ( char_expression )

Arguments
char_expression
Is an expression to convert.

Examples
ToNum( [User 1] )
ToInt( '123'-- result is number 123

 

ToDec
Converts an character expression (string) to decimal. 

Syntax
ToDec  ( char_expression )

Arguments
char_expression 
Is an expression to convert.

Examples
ToDec( [User 1] )
ToDec( '123.7' ) - result is 123.7

 

ToStr
Explicitly converts an numeric expression to string.

Syntax
ToStr ( numeric_expression )

Arguments
numeric_expression
Is an expression to convert.

Examples
ToStr( [NumericField] )
ToStr( 123.7 )


ToInt
Explicitly converts an float expression to integer.

Syntax
ToInt  ( float_expression )

Arguments
float_expression
Is an expression to convert.

Examples
ToInt( [FloatField] )
ToInt( 123.7 ) - result is 123


UNICODE
Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.

Syntax
UNICODE ( 'ncharacter_expression' )

Arguments
'ncharacter_expression'
Is an nchar or nvarchar expression.

Return Types
int

Examples
UNICODE( N'Hello' )  -- result is 72

 

UPPER
Returns a character expression with lowercase character data converted to uppercase.

Syntax
UPPER ( character_expression )

Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or field of either character or binary data.

Return Types
varchar

Remarks
character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.

Examples
UPPER ('Abc') -- result is 'ABC'
UPPER ( [Contact] ) -- result is 'CHRIS HUFFMAN' for contact 'Chris Huffman'

 

YEAR
Returns an integer that represents the year part of a specified date.

Syntax
YEAR ( date )

Arguments
date
An expression of type datetime or smalldatetime.

Return Types
int

Remarks
This function is equivalent to DATEPART(yy, date).

Examples
This example returns the number of the year from the date 03/12/1998.

YEAR( '03/12/1998' ) -- result is 1998
YEAR( [Birth Date] ) -- result is 1964 for contact 'Chris Huffman'

 Information:
How to install addon
How to uninstall addon
Getting started
 On-line help:
User reference
Statements
Operators
Functions
Financial functions
 Tutorials:
Flash demo 1
Flash demo 2
Flash demo 3
Flash demo 4
Examples of calculations
Download free trial
BUY NOW
 Compatible with:
ACT! 7 / 2005
ACT! 8 / 2006
ACT! 9 / 2007
ACT! 10 / 2008
ACT! 11 / 2009
ACT! 12 / 2010
ACT! 13 / 2011
ACT! 14 / 2012
ACT! 15 / 2013
Swiftpage ACT! 16 / 2014
Swiftpage ACT! 17 / 2015
Swiftpage ACT! 18 / 2016
Swiftpage ACT! 19 / 2017
Swiftpage ACT! 20 / 2018
Swiftpage ACT! 21 / 2019
 Screenshots:
Click on these sample screenshots
to see larger views.

ACT! software addons - best plugins for ACT contact management software ( Act! by Sage software : ACT!2005, ACT!2006, ACT!2007, ACT!2008, ACT!2009, Act by Sage Premium for Workgroups ).
Custom addon solutions ( ACT! add ons ) development and CRM (Customer Relationship Management) software training and consulting.