Supported Functions Index

• Aggregate Functions
• Mathematical Functions
• String Functions
• Date and Time Functions
• Conversion Functions
• Domain Functions
• Pocket Outlook Functions
• Other Functions

Please note: the version of SprintDB Pro required by the function. Version 1.5 and higher will not work in version 1.0.

Click here for a summary of New Features in versions 1.5 - 1.7x.

Version 2.0+ features are covered here.

Aggregate Functions

AVG, MAX, MIN, RECCOUNT, RECNO

AVG (expression)

Returns the average of the values in a group.

Examples

AVG (table1.amount)

AVG (table1.sales/&form_field1) WHERE table1.dept='sales'

MAX (expression)

Returns the maximum value in the expression.

Examples

MAX (table1.amount)

MAX (table1.sales1+table1.sales2) WHERE table1.sales1>1000

MIN (expression)

Returns the minimum value in the expression.

Examples

MIN (table1.amount*&form_field1)

MIN (table1.sales1+table1.sales2) WHERE table1.sales1>1000

RECCOUNT ( [form_id] )

Returns the number or records.

form_id: 0 - MainForm, 1 - Subform1, 2 - Subform2, ...
You can omit the form_id parameter. It's 0 (MainForm) by default.

RECNO ( [form_id] )

Returns the current record number.

form_id: 0 - MainForm, 1 - Subform1, 2 - Subform2, ...
You can omit the form_id parameter. It's 0 (MainForm) by default.

< Top>
 

Mathematical Functions

ABS, ACOS, ASIN, ATAN, CEIL, COS, COSH, EXP, FLOOR, LN, LOG, POWER, ROUND, SIN, SINH, TAN, TANH, TRUNC

ABS (n)

Returns the absolute, positive value of n.

Example

ABS (-1.0)

ABS (table1.number_field1)

ACOS (n)

Returns the arccosine of n. n is a angle in radians.

Example

ACOS (.3) - returns 1.266104

ASIN (n)

Returns the arcsine of n. n is a angle in radians.

Example

ASIN (.3) - returns 0.304693

ATAN (n)

Returns the arctangent of n. n is a angle in radians.

Example

ATAN (.3) - returns 0.291457

CEIL (n)

Returns the smallest integer greater than or equal to n.

Example

CEIL (12.8) - returns 13

COS (n)

Returns the cosine of n. n is a angle in radians.

Example

COS (180*3.14159265359/180) - returns -1

COSH (n)

Returns the hyperbolic cosine of n. n is a angle in radians.
 

EXP (n)

Returns the exponential value of n.

Example

EXP (4) - returns 54.59815

FLOOR (n)

Returns the largest integer less than or equal to n.

Example

FLOOR (15.7) - returns 15

LN (n)

Returns the natural logarithm of n.

Example

LN (5.175643) - returns 1.64396

LOG (m,n)

Returns the base-m logarithm of the n.

Example

LOG (10,100) - returns 2

POWER (m,n)

Returns the value of m raised to the power of n.

Example

POWER (3,2) - returns 9

ROUND (n,m)

Returns the value of n, rounded to the specified length m. When m is a negative number, n is rounded on the left side of the decimal point, as specified by m.

Examples

ROUND (15.193,1) - returns 15.2

ROUND (15.193,-1) - returns 20

SIN (n)

Returns the trigonometric sine of n. n is a angle in radians.

Example

SIN(30*3.14159265359/180) - return 0.5

SINH (n)

Returns the hyperbolic sines of n.

Example

SINH (1) - returns 1.17520119

SQRT (n)

Returns the square-root of n.

Example

SQRT (26) - returns 5.09901951

TAN (n)

Returns the tangent of n. n is a angle in radians.

Example

TAN (3.14159265359/4) - returns 1

TANH (n)

Returns the hyperbolic tangent of n. n is a angle in radians.
 

TRUNC (n,m)

Returns the value of n, truncated to the specified length m. When m is a negative number, n is truncated on the left side of the decimal point, as specified by m.

Examples

TRUNC (15.79,1) - returns 15.7

TRUNC (15.79,-1) - returns 10
<Top>
 

String Functions

INITCAP, INSTR, LEFT, LENGTH, LOWER, LTRIM, REPLACE, RIGHT, RTRIM, SUBSTR, UPPER

INITCAP (char_expr)

Capitalizes the first character of each word and makes the rest of each word lower case.

Example

INITCAP ('abc '+'DEF') - returns "Abc Def"

INSTR (char_expr1,char_expr2[,n[,m]])

Searches for the mth occurrence of charexpr2 after position n of charexpr1 and return the position. n and m can be omitted and default is 1. If n is negative, INSTR searches from the end of charexpr1. If search fails, returns 0.

Examples

INSTR ('CORPORATE FLOOR','OR',3,2) - returns 14

INSTR ('CORPORATE FLOOR','OR',-3,2) - returns 2

LEFT (char_expr,int_expr)

Returns the first int_expr characters from char_expr.

Examples

LEFT ('hello',3) - returns "hel"

LEFT (table1.char_field1,5)

LENGTH (char_expr)

Returns the number of characters.

Examples

LENGTH ('hello') - returns 5

LENGTH (table1.field1)

LOWER (char_expr)

Returns char_expr in lowercase letters.

Example

LOWER ('Hello!') - returns "hello!"

LTRIM (char_expr)

Returns char_expr with leading blanks removed.

Example

LTRIM (' hello') - returns "hello"

REPLACE (char_expr1,char_expr2,char_expr3)

Replaces all occurrences of char_expr2 in char_expr1 with char_expr3.

Example

REPLACE ('abcdefghicde','cde','xxx') - returns "abxxxfghixxx"

RIGHT (string, length)

Returns a specified number of characters from the right side of a string.

Example

RIGHT("hello",3) - returns "llo".

RTRIM (char_expr)

Returns char_expr with trailing blanks removed.

Example

RTRIM ('hello ') - returns "hello"

SUBSTR (char_expr1,int_expr2[,int_expr3])

Returns int_expr3 characters from int_expr2 position of char_expr1. If int_expr3 is ommited, characters are returned until the end of char_expr1. If int_expr2 is negative, the position is counted from the end of char_expr1. The first character of string is position 1.

Examples

SUBSTR ('ABCDEFG',3,4) - returns "CDEF"

SUBSTR ('ABCDEFG',-5,4) - returns "CDEF"

UPPER (char_expr)

Returns char_expr in uppercase letters.

Example

UPPER ('Hello!') - returns "HELLO!"

< Top>
 

Date and Time Functions

ADD_DATE, ADD_MONTHS, DAY, DATEADD, DATEDIFF, DATEPART, DATESET, GetDayOfWeek, GetDayOfWeekStr, GetMonthName, LAST_DAY, MONTH, SYSDATE, SYSDATETIME, SYSTIME, YEAR, DIFF_TIME

ADD_DATE (datetime, interval)

Returns a date to which a specified time interval has been added. Interval is a string expression( "[+/-][nnd][nnh][nnm][nns]" ).

(nn=number, d=Day, h=Hour, m=Minute, s=Second)

Examples

ADD_DATE('10/1/01 12:30:40','1d1h1m1s') - returns '10/2/01 13:31:41'.

ADD_DATE('10/1/01 12:30:40','-1h' ) - returns '10/1/01 11:30:40'.

ADD_MONTHS (d,n)

Adds n months to the specified date.

Example

ADD_MONTHS (TO_DATE('4/15/01'),3) - returns "7/15/01"

DAY (d)

Returns the day of the specified date.

Example

DAY (TO_DATE('4/15/01')) - returns 15

DATEADD (interval, number, date)

Returns a date to which a specified time interval has been added.

Arguments:

Interval: the interval of time you want to add. y - year, m - month, d - day, h - hour, n - minute, s - second

Number: the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).

Date: date to which the interval is added.

Examples

DATEADD(d,10,"02/10/2002")

DATEADD(m,3,!Table1.Date1)

DATEADD(s,300,SYSDATETIME())

DATEDIFF (interval, date1, date2)

Returns the number of time intervals between two specified dates.

Arguments:

Interval: the interval of time you want to calculate the difference between date1 and date2. y - year, m - month, d - day, h - hour, n - minute, s - second

date1, date2: two dates you want to use in the calculation.

Example

DATEDIFF(m,"11/10/2001","02/10/2002")

DATEDIFF(s,!Table1.Date1,SYSDATETIME())

DATEPART (interval, date)

Returns a number containing the specified part of a gived date.

Arguments:

Interval: the interval of time you want to return. y - year, m - month, d - day, h - hour, n - minute, s - second

Date: date that you want to evaluate.

Example

DATEPART(h,SYSDATETIME())

DATESET (interval, number, date)

Returns a date to which a specified time interval has been set.

Arguments:

Interval: the interval of time you want to add. y - year, m - month, d - day, h - hour, n - minute, s - second

number: the number of intervals you want to set.

Date: date that you want to evaluate.

Examples

DATESET(m,9,"03/01/2001") returns "09/01/2001".

DATESET(d,9,"03/01/2001") returns "03/09/2001".

GetDayOfWeek (date) [Version 1.5 and above]

Returns the day of the week. 1 = sunday, 2 = monday, and so on.

Arguments:

Date: a date expression.

Examples

GetDayOfWeek( SYSDATE() )
GetDayOfWeek( "03/01/2001" )

GetDayOfWeekStr (date[,type]) [Version 1.5 and above]

Arguments:

Date: a date expression.
Type: "Long" returns the long name for the day of the week. "Short" returns the abbreviated name for the day of the week. Default is "Short".

Examples

GetDayOfWeekStr (SYSDATE())
GetDayOfWeekStr ("05/05/2002", Long) returns "Sunday"
GetDayOfWeekStr ("05/05/2002", Short) returns "Sun"

GetMonthName ( Date, Long/Short ) [Version 1.6 and above]

Returns the name of the month in long/short format.

Example

Dim( a )
SetVar( @a, GetMonthName(!table1.date1, Short ) )

LAST_DAY (d)

Returns the last day of the specified month.

Example

LAST_DAY (TO_DATE('4/15/01')) - returns 30

MONTH (d)

Returns the month of the specified date.

Example

MONTH (TO_DATE('4/15/01')) - returns 4

SYSDATE ()

Returns the system date in your locale date format.

Example

SYSDATE ()

SYSDATETIME ()

Returns the system date and time in your locale date/time format.

Example

SYSDATETIME ()

SYSTIME ()

Returns the system time in your locale time format.

Example

SYSTIME ()

YEAR (d)

Returns the year of the specified date.

Example

YEAR (TO_DATE('4/15/01')) - returns 2001

DIFF_TIME (datetime2,datetime1)

Returns the elapsed time in seconds, from datetime2 to datetime1.

Example

DIFF_TIME ('10/1/01 12:30:40','10/1/01 12:30:30') - returns 10

<Top >
 

Conversion Functions

ASC, CHR, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTR

ASC (string) [Version 1.5 and above]

Returns the ASCII value of the first character in a string expression.

Example

ASC( "hello" ) - returns 104

CHR (ASCII) [Version 1.5 and above]

Returns the character associated with the specified ASCII value.

Example

CHR( 65 ) - returns 'A'

TO_CHAR (n)

Convert a number into a string.

Example

'ABC'+TO_CHAR(4)+'DEF' - returns "ABC4DEF"

TO_DATE (s)

Converts a string into a date.

Example

TO_DATE ('4/15/01')

TO_NUMBER (s)

Converts a string into a number.

TO_NUMBER ('10'+'5')+1 - returns 106

TO_TIMESTR (sec)

Converts seconds into a string.

Example

TO_TIMESTR (4214) - returns "01:10:14"

< Top>
 

Other Functions

_ToExpr, FILEDIALOG, FileExists, IIF, GET_FIELD, INPUTBOX, InputComboBox, InputDatePicker, GetDBFilePath, GetRegistryInt, GetRegistryStr, RECNO, RGB, TIMERID, GetClipboard, GetDeviceID, GetGUID, GetFilePath, GetFileName, GetOwnerName, CreateFont, GetDataType(tablefield), InputPwdBox, InputNumber(title,default),GetPlatform

_ToExpr() [Version 1.7 and above]

Returns the result of the specified string or numeric expression.

Syntax
_ToExpr(str)

Arguments:

Str: String that contains any string expression or numeric expression. It can be any form control or text type table field that contains expression.

Example

_ToExpr( "3+4" ) returns 7.
_ToExpr( " 'SprintDB '+'Pro' " ) returns "SprintDB Pro"
_ToExpr( &edit1 )
_ToExpr( !0!table1.field1 )
 

FILEDIALOG (type, filter) [Version 1.5 and above]

Returns the full path of the selected file.

Arguments:

Type: "Open" constructs a File Open Dialog box and "Save" constructs a File Save As Dialog box.
Filter: a series of string pairs that specify filters you can apply to the file. If you specify file filters, only selected files will appear in the File list box.

Examples

FILEDIALOG( Open, "*.bmp, *.jpg" )
FILEDIALOG( Open, "*.txt;*.csv, *.doc" )
FILEDIALOG( Save, "*.bmp,*.jpg" )

GetDataType (tablefield) [Version 2.0 and above]

Returns its field type. Field type is one of the followings: "Text", "Integer", "Memo", "SmallInt", "Double", "DateTime", "Yes/No", "OLEObject".

Examples

GetDataType(table1.field1)

GetRegistryInt (section, entry, default) [Version 1.5 and above]

Retrives the value of an integer from an specified registry entry.

Arguments:

Section: a string expression that specifies the section containing the entry.
Entry: a string expression that specifies the entry.
Default: the default integer value to return if cannot find the the specified entry.

Examples

GetRegistryInt( "myConfig", "interval", 5 )

GetRegistryStr (section, entry, default) [Version 1.5 and above]

Retrives the value in string from an specified registry entry.

Arguments:

Section: a string expression that specifies the section containing the entry.
Entry: a string expression that specifies the entry.
Default: the default string value to return if the specified entry cannot be found.

Example

GetRegistryStr( "myConfig", "bmpPath", "\My Documents" )

IIF (expr,truePart,falsePart)

Returns one of two parts, depending on the evaluation of an expression.

Examples

IIF (&Amount > 100, "Large", "Small" )
IIF (&Amount > 100, &Amount*3.4, &Amount * !Table1.Field1)
IIF (!Table1.Field1<>0, !Table1.Field1,
IIF (!Table1.Field2<>0, !Table1.Field2, !Table1.Field3))

InputComboBox (Prompt,Title,ComboSource[,DefaultValue]) [Version 1.7 and above]

Displays a prompt in a dialog box, waits for the user to input text or select a text
from the built-in combo box control, and return the text.

Arguments:

Prompt: String expression displayed as the message in the dialog box.
Title : String expression displayed in the title bar of the dialog box.
ComboSource: record source of the combo box control.

To specify an SQL Statement, enclose the statement with: ",'.

To specify a value list, enclose it with: {}.

DefaultValue: String expression displayed in the dateTime picker as the default
response if no other input is provided.

Examples 

InputComboBox("Choose a country","Country","SELECT myTable.Country FROM myTable")
InputComboBox("Choose a country","Country",{Germany,USA,Mexico})

InputDatePicker (Prompt,Title[,DefaultValue]) [Version 1.7 and above]

Displays a prompt in a dialog box, waits for the user to input DateTime
using the built-in DateTime picker, and returns the DateTime in string format.

Arguments:

Prompt: String expression displayed as the message in the dialog box.
Title: String expression displayed in the title bar of the dialog box.
DefaultValue: String expression displayed in the dateTime picker as the default
response if no other input is provided.

Examples

InputDatePicker("Enter a date","Date")
InputDatePicker("Enter a date","Date",SYSDATETIME())

InputNumber(title,default) [Version 2.0 and above]

Returns a number typed by the user, using the built-in numeric key pad.

Syntax:
InputNumber(title,default)

Arguments:

Title: String expression displayed in the title bar of the numeric key pad.
Default: Number displayed as the default response if no other input is provided. You can omit this argument.

Example

Dim(number)
SetVar( @number, InputNumber("Enter a number") )

InputPwdBox () [Version 2.0 and above]

Almost the same as InputBox() , but it can be used for password input.

GET_FIELD (skip, field, formid)

Returns the value of a field in the record set of a specified Main/SubForm

Arguments:

Skip: Relative record position.
0 – The value of the field in the current record.
Positive – The value of the field in the next n-th record.
Negative – The value of the field in the previous n-th record.
Field: The name of the field.
Formid: 0 – MainForm, 1 – SubForm1, 2 – SubForm2, n – SubFormN.

Examples

GET_FIELD (2, Table1.Field1, 0)
GET_FIELD (-1, Table1.Field1, 1)

GetDeviceID() [Version 1.7 and above]

Returns the device ID of your Pocket PC.
 

GetGUID() [Version 1.7 and above]

Returns a GUID (Globally Unique ID).
 

GetOwnerName() [Version 1.7 and above]

Returns the Owner Name of a Pocket PC.
 

INPUTBOX (prompt, title)

Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing the contents of the text box.

Arguments:

Prompt: String expression displayed as the message in the dialog box.
Title: String expression displayed in the title bar of the dialog box.

Examples

&control1 + INPUTBOX ("Enter a string: ","Input")
SetFormSource (MainForm, {"SELECT * FROM Customers WHERE Customers.CompanyName = INPUTBOX ("Enter a company name ","Search") )

RECNO ()

Returns the current record number.
 

RGB (Red, Green, Blue) [Version 1.5 and above]

Returns a whole number representing an RGB color value.

Arguments:

RED: number in the range 0-255 representing the red component of the color.
GREEN: number in the range 0-255 representing the green component of the color.
BLUE: number in the range 0-255 representing the blue component of the color.

Example

RGB (36,91,29) returns 1923876

TIMERID () [Version 1.5 and above]

Returns the Timer ID to identify the current Timer ID in the OnTimer event.
 

GetClipboard () [Version 1.6 and above]

Retrieves data from the System Clipboard.

Example

Dim( a )
SetVar( @a, GetClipboard() )

GetFilePath ( full_path_of_a_file ) [Version 1.6 and above]

Returns the directory path of the specified file path.
 

GetFileName ( full_path_of_a_file ) [Version 1.6 and above]

Returns the file name of the specified file path.

Example

GetFilePath( "\My Documents\aa.bmp" ) will return "\My Documents".
GetFileName( "\My Documents\aa.bmp" ) will return "aa.bmp"

Dim( fullpath )
SetVar( @fullpath, FILEDIALOG(Open,"*.bmp,*.jpg") )
Dim( filepath )
Dim( filename )
SetVar( @filepath, GetFilePath(@fullpath) )
SetVar( @filename, GetFileName(@fullpath) )

<Top>
 

Domain Functions

DLOOKUP, DRECCOUNT, DMAX, DMIN, DAVG, DSUM

Overview
You can use the domain functions to get the value of a particular field from a specified set of records (a Domain). Use the domain functions in a macro, or a calculated control on a form.

Argument Description
Expr: a field name whose value you want to use.
Domain: a table name.
Criteria: an optional expression used to restrict the range of data on which a domain function is performed. Criteria is equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the domain function evaluates expr against the entire domain.

DLOOKUP (expr, domain[, criteria])

Returns a value from a field in a specified set of records called the domain. If no record satisfies criteria or if domain contains no records, the DLOOKUP function returns a Null.

Examples

DLOOKUP (Customers.PhoneNumber, Customers, Customers.ID=2)

DLOOKUP (Customers.PhoneNumber, Customers, Customers.ID=&customerControl)

DRECCOUNT (expr, domain[, criteria])

Returns the number of records that are in a specified set of records (a domain). If no record satisfies criteria or if domain contains no records, the DRECCOUNT function returns 0.

Examples

DRECCOUNT (OrderDetails.ID,OrderDetails, OrderDetails.ProductID=3 AND OrderDetails.Quantity>&control1 )

DRECCOUNT (OrderDetails.ID, OrderDetails, OrderDetails.OrderID=!Orders.OrderID)

DMAX (expr, domain[, criteria])

Returns the maximum value in a specified set of records (a domain). If no record satisfies criteria or if domain contains no records, the DMAX function returns a Null.

Example

DMAX (OrderDetails.Quantity, OrderDetails, OrderDetails.ProductID=3)

DMIN (expr, domain[, criteria])

Returns the minimum value in a specified set of records (a domain). If no record satisfies criteria or if domain contains no records, the DMIN function returns a Null.

Example

DMIN (OrderDetails.Quantity, OrderDetails, OrderDetails.ProductID=&control1)

DAVG (expr, domain[, criteria])

Returns the average of a set of values in specified set of records (a domain). If no record satisfies criteria or if domain contains no records, the DAVG function returns a Null value.

Example

DAVG (OrderDetails.Quantity, OrderDetails, OrderDetails.ProductID=3)

DSUM (expr, domain[, criteria])

Returns the sum of a set of values in a specified set of records (a domain).

If no record satisfies criteria or if domain contains no records, the DSUM function returns a Null.

Example

DSUM (OrderDetails.Quantity, OrderDetails, OrderDetails.ProductID=3 )

<Top>

Pocket Outlook Functions [Version 2.0 and above]

PolContactCount() , PolContactOID(), PolContactRead()
Columns in the Pocket Outlook Contact Database

PolContacCount()

Returns the number of contacts in contact database.

Example

Dim(count)

SetVar( @count, POLContactCount() )

PolContactOID(index)

Returns the Object Identifier of an item in contact database.

Arguments:

index: index of the item. If you omit the index argument, it will return the OID of the current item. Returns 0 if there is an error.

Example

Dim(oid)

//Retrieve the Object ID for the first item in contact database.
SetVar( @oid, PolContactOID( 1 ) )

...

//Retrieve the Object ID for the second item in contact database.
SetVar( @oid, PolContactOID( 2 ) )-> oid for the second item in contact database.   

PolContactRead(oid,column)

Returns a column in an item.

Arguments:

Oid: object identifier for an item.

Column: specify a column. Refer to the available columns section.

Example

//Retrieve the Object ID for the first item in contact database.

Dim(oid)

SetVar( @oid, PolContactOID( 1 ) )

//Retrieve the first name of the first item.

Dim(fname)

SetVar( @fname, PolContactRead(@oid,"FirstName") )