Function Expressions
ABS(nExpression)
Definition / Description
Returns the absolute value of the specified numeric expression.
The scripting equivalent function is Math.Abs(Value).
Syntax
ABS(numeric field)
Where:
- numeric field is a numeric field or expression which results in a numeric value
Example
If field = 2, ABS(field) = 2
If field = -2, ABS(field) = 2
ALLTRIM(Expression (, nFlags) (, cParseChar (, cParseChar2 (, ...))))
Definition / Description
Removes all leading and trailing spaces or parsing characters from the specified character expression, or all leading and trailing zero (0) bytes from the specified binary expression.
The scripting equivalent function is String.Trim.
Syntax
ALLTRIM(Expression [, nFlags] [, cParseChar [, cParseChar2 [, ...]]])
Where:
- Expression specifies an expression of Character or Varbinary type to remove leading and trailing spaces or 0 bytes from, respectively. Note: The ALLTRIM() function truncates the Expression to approximately 600 characters.
-nFlags specifies if trimming is case-sensitive when one or more parse characters are included. Trimming is case-sensitive if nFlags is zero or is omitted. Trimming is case-insensitive if nFlags 1.
-cParseChar [, cParseChar2 [, ...]] specifies one or more character strings that are trimmed from the beginning and end of cExpression. If cParseChar is not included, then leading and trailing spaces or 0 bytes are removed from Expression. The maximum number of strings is 23.
AT(cSearchExpression, cExpressionSearched (, nOccurrence))
Definition / Description
Searches a character expression for the occurrence of another character expression and returns the position of the searched for character expression.
Syntax
AT(cSearchExpression, cExpressionSearched {, nOccurrence})
Where:
- cSearchExpression specifies the character expression to search for in cExpressionSearched.
-cExpressionSearched specifies the character expression to search for cSearchExpression. Both cSearchExpression and cExpressionSearched can be memo fields of any size.
-nOccurrence specifies which occurrence of cSearchExpression to search for in cExpressionSearched. By default, AT( ) searches for the first occurrence of cSearchExpression (nOccurrence = 1).
Example
AT('na', 'banana', 1) = 3 --> banana
AT('na', 'banana', 2) = 5 --> banana
BETWEEN(eTestValue, eLowValue, eHighValue)
Definition / Description
Determines whether the value of an expression is inclusively between the values of two expressions of the same type.
Syntax
BETWEEN(eTestValue, eLowValue, eHighValue)
Where:
- eTestValue - Specifies an expression to evaluate.
- eLowValue - Specifies the lower value in the range.
- eHighValue - Specifies the upper value in the range.
Example
Dates: BETWEEN(TRANS(datefield), '03/01/2021', '03/16/2021') --> Result: {all dates in between and including March 1, 2021 - March 16, 2021}Where NumbersTest: {1, 3, 5, 7}; BETWEEN(NumbersTest, 3, 5) --> Result: {3, 5}
Where AlphaTest: {A, B, C, D}; BETWEEN(AlphaTest, 'B', 'D') --> Result: {B, C, D}
CAST(eExpression AS cDataType ((,nFieldWidth (, nPrecision)))(NULL / NOT NULL))
Definition / Description
Converts an expression from one data type to another.
The scripting equivalent function is to use standard casting with parenthesis or Convert functions.
Syntax
CAST(eExpression AS cDataType ((,nFieldWidth (, nPrecision)))(NULL / NOT NULL))
Where:
- cExpression - Field or expression to be converted to a different data type
- cDataType - Target or new data type (Currency: Y, Currency; Date: D, Date; DateTime: T, DateTime; Double: B, Double; Floating: F, Float; General: G, General; Integer: I, Int, Integer; Logical: L, Logical; Memo: M, Memo; Numeric: N, Num, Numeric; Varbinary: Q, Varbinary; Varchar: V, Varchar)
- nFieldWidth - (Optional) for numerics, the number of characters to display
- nPrecision - (Optional) number of places to display to the right of the decimal
- NULL / NOT NULL - (Optional) specifies whether to permit null values in the field.
CDOW(dExpression / tExpression)
Definition / Description
Returns the day of the week from a given Date or DateTime expression.
Syntax
CDOW(dExpression / tExpression)
Where:
- dExpression - Specifies the date from which CDOW( ) returns the day.
- tExpression - Specifies the datetime from which CDOW( ) returns the day.
Example
Assuming date_field = 3/18/2021 and is a Thursday, CDOW(date_field) = Thursday
CEILING(nExpression)
Definition / Description
Returns the next highest integer that is greater than or equal to the specified numeric expression. The CEILING function is used to round up to the nearest integer.
Syntax
CEILING(nExpression)
Where:
- nExpression - Specifies the number whose next highest integer CEILING( ) returns.
Example
If Expression = 7; CEILING(exp) = 7
If Expression = 7.5; CEILING(exp) = 8
CHR(nANSICode)
Definition / Description
Returns the character associated with the specified numeric ANSI code.
Syntax
ACHR(nANSICode)
Where:
- nANSICode - Specifies a number between 0 and 255 whose equivalent ANSI character CHR( ) returns. Use ASC( ) to return the ANSI value for a specified character.
Example
CHR(49) = 1
CMONTH(dExpression / tExpression)
Definition / Description
Returns the name of the month from a given date or DateTime expression.
Syntax
CMONTH(dExpression / tExpression)
Where:
- dExpression - Specifies the date expression from which CMONTH( ) returns the name of the month.
- tExpression - Specifies the DateTime expression from which CMONTH( ) returns the name of the month.
Example
If date_field = 03/18/2021, CMONTH(date_field) = March
CTOD(cExpression)
Definition / Description
Converts a character expression to a date expression.
Syntax
CTOD(cExpression)
Where:
- cExpression - Specifies a character expression. CTOD() can create ambiguous Date values and generates a compilation error when SET STRICTDATE is set to 2. To create unambiguous Date values, use the DATE( ) function.
Example
If date_field = "March 18, 2021", CTOD(date_field) = 03/18/2021
DATE((nYear, nMonth, nDay))
Definition / Description
Returns the current system date, which is controlled by the operating system, or creates a year 2000-compliant Date value.
Syntax
DATE((nYear, nMonth, nDay))
Where:
- nYear - Specifies the year returned in the year 2000-compliant Date value. nYear can be a value from 100 to 9999.
- nMonth - Specifies the month returned in the year 2000-compliant Date value. nMonth can be a value from 1 to 12.
- nDay - Specifies the day returned in the year 2000-compliant Date value. nDay can be a value from 1 to 31.
Examples
DATE() = current system date as MM/DD/YYYY
DATE(2020, 4, 15) = 04/15/2020
- Adding a Julian Date column to a report grid: PADLstr((jo_planstart- DATE(YEAR(jo_planstart), 1, 1? + 1)),3,'0')+'-'+RIGHTstr(year(jo_planstart?),2)
or
Julian Date: <<ALLT(RIGHT(STR(YEAR(fi_lotdate),4,0),2)+PADL(ALLT(STR(fi_lotdate-DATE(YEAR(fi_lotdate),1,1)+1,3,0)),3,'0'))>> . This expression will generate in the YYDDD format. If you want the date in the YDDD format, use the following expression: Julian Date: <<ALLT(RIGHT(STR(YEAR(fi_lotdate),4,0),1)+PADL(ALLT(STR(fi_lotdate-DATE(YEAR(fi_lotdate),1,1)+1,3,0)),3,'0'))>>
DAY(dExpression / tExpression)
Definition / Description
Returns the numeric day of the month for a given Date or DateTime expression.
Syntax
DAY(dExpression / tExpression)
Where:
- dExpression - Specifies a date from which DAY( ) returns a day of the month. dExpression can be a date literal, a Date-type variable, an array element, or a date field.
- tExpression - Specifies a date or time from which DAY( ) returns a day of the month. dExpression can be a DateTime literal, a DateTime-type variable, an array element, or a DateTime field.
Example
DAY(03/04/2021) = 4
DMY(dExpression / tExpression)
Definition / Description
Returns a character expression in day-month-year format (for example, 31 May 1998) from a Date or DateTime expression. The month name isn't abbreviated.
Syntax
DMY(dExpression / tExpression)
Where:
- dExpression - Specifies the Date expression from which DMY( ) returns a character string in day-month-year format.
- tExpression -Specifies the DateTime expression from which DMY( ) returns a character string in day-month-year format.
Example
DMY(03/04/2021) = 4 March 2021
DOCEXISTS(cTable, nRecord, cExpression)
Definition / Description
Determines whether a document is attached to the record for the specified table. Generally used as a "Print When" expression in report layouts to govern whether or not to print a specific field on the report.
Syntax
DOCEXISTS(cTable, nRecord, cExpression)
Where:
- cTable - Specifies the table the record belongs to.
- nRecord -Specifies the record identifier.
- cExpression - Specifies which document to check for.
Example
!DOCEXISTS("dmprod", pr_id, "Cofa")
DOCEXISTS("dmprod", pr_id, "Cofa")
Where dmprod is the table the document exists on. Based on the pr_id (the item's ID number). Cofa is the name of the document.
DOW(dExpression / tExpression (, nFirstDayOfWeek))
Definition / Description
Returns a numeric day-of-the-week value from a Date or DateTime expression.
Syntax
DOW(dExpression / tExpression (, nFirstDayOfWeek))
Where:
- dExpression - Specifies the Date expression from which DOW( ) returns the day number.
- tExpression - Specifies the DateTime expression from which DOW( ) returns the day number.
- nFirstDayOfWeek - Specifies the first day of the week with one of the following values: 1 [Sunday, also is default when nFirstDayOfWeek is omitted] 2 [Monday] 3 [Tuesday] 4 [wednesday] 5 [Thursday] 6 [Friday] 7 [Saturday]
Example
Assume 03/31/2021 is a Tuesday
DOW(03/31/2021) = 3
DTOC(dExpression / tExpression (, 1))
Definition / Description
Returns a Character-type date from a Date or DateTime expression.
Syntax
DTOC(dExpression / tExpression (, 1))
Where:
- dExpression - Specifies a Date-type variable, array element, or field for which DTOC( ) returns a Character-type date.
- tExpression - Specifies a DateTime-type variable, array element, or field for which DTOC( ) returns a Character-type date.
Example
If datefield = 03/13/2021, DTOC(datefield) = 03/13/2021
DTOS(dExpression / tExpression)
Definition / Description
Returns a character-string date in a yyyy/mm/dd format from a specified Date or DateTime expression.
Syntax
DTOS(dExpression / tExpression)
Where:
- dExpression - Specifies the Date expression DTOS( ) converts to an eight-digit character string.
- tExpression - Specifies the DateTime expression DTOS( ) converts to an eight-digit character string.
Example
If date_field = 03/13/2021, DTOS(date_field) = 20210313
EMPTY(eExpression)
Definition / Description
Determines whether an expression evaluates to empty.
Syntax
EMPTY(eExpression)
Where:
- eExpression - Specifies the expression that EMPTY( ) evaluates. Can be specified by Character, Numeric, Date, Varbinary, Blob, or Logical type as well as Memo name or General field in open table.
Example
If field(x) is NULL; EMPTY(field(x)) = "Yes"
If field(x) = 'text in this field'; EMPTY(field(x)) = "No"
EVALUATE(cExpression)
Definition / Description
Evaluates a character expression and returns the result. This is often used with system constants that transform a complex expression into a string formatted number for a label.
Syntax
EVALUATE(cExpression)
Where:
- cExpression - Specifies the expression to evaluate. Can be a literal character string, or a valid Visual FoxPro expression, variable, array element, or field of any data type, enclosed in quotation marks and under 255 characters.
Example
Set system Constant SSCC =
'00' + LEFT(gu_SSCC_Vendor_Number, 8) + PADL(CAST(fi_lotnum AS VARCHAR), 9, '0') + CAST((ROUND((4 + 3 * (VAL(SUBSTR(gu_SSCC_Vendor_Number, 1, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 3, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 5, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 7, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 1, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 3, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 5, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 7, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 9, 1))) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 2, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 4, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 6, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 8, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 2, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 4, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 6, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 8, 1))) / 10, 0) * 10) - (3 * (VAL(SUBSTR(gu_SSCC_Vendor_Number, 1, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 3, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 5, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 7, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 1, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 3, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 5, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 7, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 9, 1))) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 2, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 4, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 6, 1)) + VAL(SUBSTR(gu_SSCC_Vendor_Number, 8, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 2, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 4, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 6, 1)) + VAL(SUBSTR(PADL(CAST(fi_lotnum AS VARCHAR), 9, '0'), 8, 1))) AS VARCHAR)
Based on the parameters and variables associated with the item / order / job to be calculated by the system constant, EVALUATE (or EVAL) (SSCC) = 10021130702777
F BARCODE(f Fontname, f Fontsize, f Expression)
Definition / Description
This function is available only for DEACOM Classic.
Creates a barcode for the given expression that is readable by the DEACOM Warehouse Management System. Note that this expression must be used as a top-level expression. This means that it cannot be used within another expression (IIF(F_BARCODE(...),...,...). F_BARCODE's role is to format an expression rather than apply logic to an expression the way a traditional function does.
Syntax
F_BARCODE(f_Fontname, f_Fontsize, f_Expression)
Where:
- f_Fontname- The type of barcode being created. Options are: 3of9 UPCA ean itf14rect[1-7] itf14tb[1-7] gs1128[1-7] Use the numbers 1-7 at the end of the ITF-14 and GS1-128 barcode names to specify the height of the font. If nothing is specified, the font will default to a height of 3. Please see the WMS > Barcode Setup page for an example and more information on the GS1-128 setup.
- f_Fontsize - The size of the barcode that will be generated.
- f_Expression - Specifies the information that the barcode will contain.
FLOOR(nExpression)
Definition / Description
Returns the nearest integer that is less than or equal to the specified numeric expression. The FLOOR function is used to round down to the nearest integer.
Syntax
FLOOR(nExpression)
Where:
- cExpression - Specifies the expression to evaluate. Can be a literal character string, or a valid Visual FoxPro expression, variable, array element, or field of any data type, enclosed in quotation marks and under 255 characters.
Example
If Exp = 37.9, FLOOR(Exp) = 37
IF Exp = 50, FLOOR(Exp) = 50
GETVAR ("variable name")
Definition / Description
Returns the value of a specified system variable, the name of which must be in quotes ("name"). This function returns a string containing the value stored in the variable if everything works fine, an empty string if the variable name does not exist, and invalid if the variable name is of the wrong syntax (for example, it contains forbidden characters).
Syntax
GETVAR ("variable name")
Where:
- Variable name - Specifies the valid system variable for which a value will be returned.
Example
IIF(GETVAR("CountNum") = 0, SETVAR("CountNum",1), GETVAR("CountNum") + 1)
IIF(lExpression, eExpression1, eExpression2)
Definition / Description
Returns one of two values depending on the value of a logical expression.
Syntax
IIF(lExpression, eExpression1, eExpression2)
Where:
- lExpression - Specifies the logical expression that IIF( ) evaluates.
- eExpression1, eExpression2 - If lExpression evaluates to True (.T.), eExpression1 is returned and eExpression2 is not evaluated. If lExpression evaluates to False (.F.) or null (.NULL.), eExpression2 is returned and eExpression1 is not evaluated.
Example
If Exp = 3, IIF(Exp < 5, "small", "big") = small
If Exp = 7, IIF(Exp < 5, "small", "big") = big
INLIST(eExpression1, eExpression2 (, eExpression3 ...))
Definition / Description
Determines whether an expression matches another expression in a set of expressions.
Syntax
INLIST(eExpression1, eExpression2 (, eExpression3 ...))
Where:
- eExpression1 - Specifies the expression INLIST( ) searches for in the set of expressions.
- eExpression2(, eExpression3...) - Specifies the se of expressions to search. Must include at least one expression, can include up to 25. All expressions in set of expressions must be of the same data type.
Example
If Exp = 2, INLIST(Exp, 1, 2, 3) = Yes
IF Exp = 2.5, INLIST(Exp, 1, 2, 3) = No
ISALPHA(cExpression)
Definition / Description
Determines whether the leftmost character in a character expression is alphabetic.
The scripting equivalent function is String.IsAlpha.
Syntax
ISALPHA(cExpression)
Where:
- cExpression - Specifies the character expression that ISALPHA( ) evaluates. Any characters after the first character in cExpression are ignored.
Example
If Exp = A1A, ISALPHA(Exp) = Yes
If Exp = 1AA, ISALPHA(Exp) = No
ISDIGIT(cExpression)
Definition / Description
Determines whether the leftmost character of the specified character expression is a digit (0 through 9).
The scripting equivalent function is String.IsDigit.
Syntax
ISDIGIT(cExpression)
Where:
- cExpression - Specifies the character expression that ISDIGIT( ) tests. Any characters after the first character in cExpression are ignored.
Example
If Exp = A1A, ISDIGIT(Exp) = No
If Exp = 1AA, ISDIGIT(Exp) = Yes
LEFT(cExpression, nExpression)
Definition / Description
Returns a specified number of characters from a character expression, starting with the leftmost character.
The scripting equivalent function is String.Left.
Syntax
LEFT(cExpression, nExpression)
Where:
- cExpression - Specifies the character expression that LEFT( ) returns characters from.
- nExpression - Specifies the number of characters returned from the character expression. If nExpression is greater than the length of cExpression, all of the character expression is returned. If nExpression is negative or 0, LEFT( ) returns an empty string. LEFT( ) uses a starting position of 1 and is identical to SUBSTR( ).
Example
If Exp = Test Name, LEFT(Exp, 6) = Test N
If Exp = Test Name, LEFT(Exp, 2) = Te
LEN(cExpression)
Definition / Description
Determines the number of characters in a character expression, indicating the length of the expression.
The scripting equivalent function is Length property on the string.
Syntax
LEN(cExpression)
Where:
- cExpression - Specifies the character expression for which LEN( ) returns the number of characters.
Example
If Exp = Test Name, LEN(Exp) = 9
LIKE(cExpression1, cExpression2)
Definition / Description
Determines if a character expression matches another character expression. The match is case sensitive.
The scripting equivalent function is String.IsRegexMatch.
Syntax
LIKE(cExpression1, cExpression2)
Where:
- cExpression1 - Specifies the character expression that LIKE( ) compares with cExpression2. cExpression1 can contain the wildcards such as * and ?. The question mark (?) matches any single character in cExpression2 and the asterisk (*) matches any number of characters. You can mix any number of wildcards in any combination in cExpression1.
- cExpression2 - Specifies the character expression LIKE( ) compares with cExpression1. cExpression2 must match cExpression1 letter for letter in order for LIKE( ) to return true (.T.).
Example
If Exp = Test; LIKE('Test', Exp) = Yes
If Exp = Test; LIKE('test', Exp) = No
LOWER(cExpression)
Definition / Description
Returns a specified character expression in lowercase letters.
The scripting equivalent function is String.ToLower.
Syntax
LOWER(cExpression)
Where:
- cExpression - Specifies the character expression LOWER( ) converts.
Example
If Exp = Test This #123; LOWER (Exp) = test this #123
MAX(eExpression1, eExpression2 (, eExpression3 ...))
Definition / Description
Evaluates a set of expressions and returns the expression with the maximum value. Applies to numeric fields only.
The scripting equivalent function is Math.Max(L, R).
Syntax
MAX(eExpression1, eExpression2 (, eExpression3 ...))
Where:
- eExpression1, eExpression2[, eExpression3...] - Specify the expressions from which you want MAX( ) to return the expression with the highest value. All the expressions must be of the same data type.
Example
MAX(1, 3, 5) = 5
MIN(eExpression1, eExpression2 (, eExpression3 ...))
Definition / Description
Evaluates a set of expressions and returns the expression with the minimum value. Applies to numeric fields only.
The scripting equivalent function is Math.Min(L, R).
Syntax
MIN(eExpression1, eExpression2 (, eExpression3 ...))
Where:
- eExpression1, eExpression2[, eExpression3...] - Specify the set of expressions from which you want MIN( ) to return the expression with the lowest value. All the expressions must be of the same type.
Example
MIN(1, 3, 5) = 1
MOD(nDividend, nDivisor)
Definition / Description
Divides one numeric expression by another numeric expression and returns the remainder.
The scripting equivalent function is Modulus Operator (%).
Syntax
MOD(nDividend, nDivisor)
Where:
- nDividend - Specifies the dividend. The number of decimal places in nDividend determines the number of decimal places in the return value.
- nDivisor - Specifies the divisor. A positive number is returned if nDivisor is positive, and a negative number is returned if nDivisor is negative. A nDivisor value of zero will result in an error.
Example
MOD(15, 7) = 1
MOD(7, 15) = 7
MONTH(dExpression / tExpression)
Definition / Description
Returns the number of the month for a given Date or DateTime expression.
The scripting equivalent function is DateTime.Month.
Syntax
MONTH(dExpression / tExpression)
Where:
- dExpression - Specifies the Date expression for which you want MONTH( ) to return the month number.
- tExpression - Specifies the DateTime expression for which you want MONTH( ) to return the month number.
Example
If Exp = 03/18/2021, MONTH(Exp) = 3
PADx(eExpression, nResultSize (, cPadCharacter))(x = L or R or C)
Definition / Description
Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both. NOTE Padding with blank spaces only works in report layouts; grids will automatically trim off "extra" spaces.
The scripting equivalent function is String.PadCenter or String.PadRight or String.PadLeft.
Syntax
PADx(eExpression, nResultSize [, cPadCharacter])[x = L or R or C]
Where:
- eExpression - Specifies the expression to be padded. This expression can be any expression type except a logical expression or a general or picture field.
- nResultSize - Specifies the total number of characters in the expression after it is padded.
-cPadCharacter - Specifies the value to use for padding. This value is repeated as necessary to pad the expression to the specified number of characters. If you omit cPadCharacter, spaces (ASCII character 32) are used for padding.
Example
If exp 1 = abc, exp 2 = 123 and exp1 + exp 2 = abc123, then
PADR(exp1, 7, 'z') + exp2 = abczzzz123
PADC(exp1, 7, 'z') + exp2 = zzabczz123
PROPER(nExpression)
Definition / Description
Returns a string expression with the first letter of each word capitalized.
The scripting equivalent function is String.ToProper.
Syntax
PROPER(nExpression)
Where:
- nExpression – Specifies a character expression (alphanumeric). Note that numbers and symbols are unchanged by this function.
Example
If Exp = see Spot Run; PROPER(Exp) = See Spot Run
If Exp = ABC-DEF, Inc; PROPER(Exp) = Abc-Def, Inc
If Exp = BLUE m45CY-h9x345\12LBS; PROPER(Exp) = Blue M45cy-H9x345\12Lbs
RAT(cSearchExpression, cExpressionSearched (, nOccurrence))
Definition / Description
Returns the numeric position of the last (rightmost) occurrence of a character string within another character string. The arguments of the function are case sensitive.
The scripting equivalent function is String.LastIndexOf.
Syntax
RAT(cSearchExpression, cExpressionSearched (, nOccurrence))
Where:
- cSearchExpression - Specifies the character expression that RAT( ) looks for in cExpressionSearched. The character expression can refer to a memo field of any size.
- cExpressionSearched - Specifies the character expression that RAT( ) searches. The character expression can refer to a memo field of any size.
- nOccurrence - Specifies which occurrence, starting from the right and moving left, of cSearchExpression RAT( ) searches for in cExpressionSearched. By default, RAT( ) searches for the last occurrence of cSearchExpression (nOccurrence = 1). If nOccurrence is 2, RAT( ) searches for the next to last occurrence, and so on.
Example
If Exp = '123 test', RAT('t', TRANS(Exp), 1) = 8If Exp = '123 test', RAT('t', TRANS(Exp), 2) = 5
RECNO((nWorkArea / cTableAlias))
Definition / Description
Returns the current record number in the current or specified table.
Syntax
RECNO([nWorkArea / cTableAlias])
Where:
- nWorkArea - Specifies the work area number for a table open in another work area. RECNO( ) returns 0 if a table isn't open in the work area you specify.
- cTableAlias - Specifies the table alias for a table open in another work area.
RIGHT(cExpression, nCharacters)
Definition / Description
Returns the specified number of rightmost characters from a character string.
The scripting equivalent function is String.Right.
Syntax
RIGHT(cExpression, nCharacters)
Where:
- cExpression - Specifies the character expression whose rightmost characters are returned.
- nCharacters - Specifies the number of characters returned from the character expression. RIGHT( ) returns the entire character expression if nCharacters is greater than the length of cExpression. RIGHT( ) returns an empty string if nCharacters is negative or 0.
Example
If Exp = '123abc', RIGHT(Exp, 2) = bc
ROUND(nExpression, nDecimalPlaces)
Definition / Description
Returns a numeric expression rounded to a specified number of decimal places.
The scripting equivalent function is Math.Round(Value[, Decimals, Bankers]).
Syntax
ROUND(nExpression, nDecimalPlaces)
Where:
- nExpression - Specifies the numeric expression whose value is to be rounded.
- nDecimalPlaces - Specifies the number of decimal places nExpression is rounded to. If nDecimalPlaces is negative, ROUND( ) returns a whole number containing zeros equal in number to nDecimalPlaces to the left of the decimal point. For example, if nDecimalPlaces is –2, the first and second digits to the left of the decimal point in the value are 0.
Example
If Exp = 507.525, ROUND(Exp, 1) = 507.5
If Exp = 507.525, ROUND(Exp, 0) = 508
If Exp = 507.525, ROUND(Exp, -1) = 510
SETVAR ("(variable name)", (value))
Definition / Description
Sets the specified value to the specified variable, the name of which must be in quotes ("name"). This function returns true if everything works fine, false if value cannot be assigned to the variable name, and invalid if the variable name or value is of the wrong syntax (for example, variable name contains forbidden characters).
The scripting equivalent function is Variables.
Syntax
SETVAR ("<variable name>", <value>)
Where:
- Variable name - Specifies the valid system variable for which the value will be modified.
- Value - Specifies the value to which the variable will be modified.
Example
IIF(GETVAR("CountNum") = 0, SETVAR("CountNum",1), GETVAR("CountNum") + 1)
SPACE(nSpaces)
Definition / Description
Returns a character string composed of a specified number of spaces. NOTE: in the grid only a single space will display, though the number of spaces are inserted into the string
The scripting equivalent function is PADR(, 10).
Syntax
SPACE(nSpaces)
Where:
- nSpaces - Specifies the number of spaces that SPACE( ) returns. The maximum value of nSpaces is limited only by memory in Microsoft Visual FoxPro.
Example
'a' + SPACE(3) + 'b' = 'a b'
SQRT(nExpression)
Definition / Description
Returns the square root of the specified numeric expression.
The scripting equivalent function is Math.Sqrt(Value).
Syntax
SQRT(nExpression)
Where:
- nExpression - Specifies the numeric expression SQRT( ) evaluates. nExpression cannot be negative.
Example
If Exp = 9, SQRT(Exp) = 3
STR(nExpression (, nLength (, nDecimalPlaces)))
Definition / Description
Returns the character equivalent of a numeric expression.
The scripting equivalent function is Convert.ToString.
Syntax
STR(nExpression [, nLength [, nDecimalPlaces]])
Where:
- nExpression - Specifies the numeric expression to evaluate.
- nLength - Specifies the length of the character string returned. If nLength is omitted, nLength defaults to 10 characters. If the expression contains a decimal point, the length includes one character for the decimal point and one character for each digit in the character string.
- nDecimalPlaces - Specifies the number of decimal places in the character string returned. To specify the number of decimal places using nDecimalPlaces, you must include nLength. If nDecimalPlaces is omitted, the number of decimal places defaults to zero (0). Visual FoxPro contains a limit of 16 digits of precision in numeric calculations.
Example
If Exp = 10.55, STR(Exp) = 11
If Exp = 10.55, STR(Exp, 10, 2) = 10.55
If Exp = 10.55, STR(Exp, 10, 3) = 10.550
STRTRAN(cSearched, cExpressionSought (, cReplacement) (, nStartOccurrence) (, nNumberOfOccurrences) (, nFlags))
Definition / Description
Searches a character expression or memo field for a second character expression or memo field and replaces each occurrence with a third character expression or memo field. You can specify where the replacement begins and how many replacements are made.
The scripting equivalent function is String.Replace.
Syntax
STRTRAN(cSearched, cExpressionSought [, cReplacement] [, nStartOccurrence] [, nNumberOfOccurrences] [, nFlags])
Where:
- cSearched - Specifies the character expression to search. cSearched can be a memo field.
- cExpressionSought - Specifies the character expression to search for in cSearched. The search is case-sensitive. cExpressionSought can be a memo field.
- [, cReplacement] - Specifies the character expression to replace every occurrence of cSearchFor in cSearched. Omitting cReplacement replaces every occurrence of cExpressionSought with an empty string.
- [, nStartOccurrence] - Specifies which occurrence of cExpressionSought to replace first. For example, if nStartOccurrence is 4, replacement begins with the fourth occurrence of cExpressionSought in cSearched and the first three occurrences of cExpressionSought remain unchanged. Omitting nStartOccurrence defaults the occurrence where replacement begins to the first occurrence of cExpressionSought.
- [, nNumberOfOccurrences] - Specifies the number of occurrences of cExpressionSought to replace. Omitting nNumberOfOccurrences replaces all occurrences of cExpressionSought, starting with the occurrence specified with nStartOccurrence.
- [, nFlags] - Specifies the case-sensitivity of a search according to the following values. To specify only the nFlags setting along with the required parameters, specify –1 for the optional parameters you want disregarded.
Example
If Exp = banana, STRTRAN(Exp, 'a', '?') = 'b?n?n?'
If Exp = banana, STRTRAN(Exp, 'a', '?', 1) = 'ban?n?'
If Exp = banana, STRTRAN(Exp, 'a', '?', 2) = 'banan?'
If Exp = banana, STRTRAN(Exp, 'a', '?', 1, 1) = 'ban?na'
SUBSTR(cExpression, nStartPosition (, nCharactersReturned))
Definition / Description
Returns a character string from the given character expression or memo field, starting at a specified position in the character expression or memo field and continuing for a specified number of characters. NOTE: extra spaces are not added onto the end
The scripting equivalent function is String.SubString.
Syntax
SUBSTR(cExpression, nStartPosition [, nCharactersReturned])
Where:
- cExpression - Specifies the character expression or memo field from which the character string is returned.
- nStartPosition - Specifies the position in the character expression or memo field cExpression from where the character string is returned. The first character of cExpression is position 1. If nStartPosition is greater than the number of characters in cExpression, SUBSTR( ) returns an empty string ("").- nCharactersReturned - Specifies the number of characters to return from cExpression. If you omit nCharactersReturned, characters are returned until the end of the character expression is reached.
Example
If Exp = '123 Test', SUBSTR(Exp, 2, 4) = '23 T'
If Exp = '123 Test', SUBSTR(Exp, 4, 7) = 'Test'
TRANSFORM(eExpression, (cFormatCodes))
Definition / Description
Returns a character string from an expression in a format determined by a format code. Notes: 1. Using TRANSFORM( ) with a large negative number that contains a decimal might result in numeric overflow when that number is passed in as a memory variable. You should use the STR( ) function instead with such numbers. 2. In situations where users wish to pad a number with zeros, they have to set the cFormatCodes as follows "@L999" - This will turn the "1" into "001".
The scripting equivalent function is String.Transform.
Syntax
TRANSFORM(eExpression, [cFormatCodes]) or TRANS(eExpression, [cFormatCodes])
Where:
- eExpression - Specifies the expression to format.
- cFormatCodes - Specifies one or more format codes that determine how to format the expression.
| cFormatCode | Description |
|---|---|
|
@B |
Left-justifies Numeric data within the display region. |
|
@C |
Appends a CR to positive Currency or Numeric values to indicate a credit. |
|
@D |
Converts Date and DateTime values to the current SET DATE format. |
|
@E |
Converts Date and DateTime values to a BRITISH date format. |
|
@L |
Pads numeric data with leading zeroes. |
|
@R |
Mask characters include: 9 or #В В В Represents a character or number. !В В В Converts lower-case letters to uppercase letters. NOTE: Visual FoxPro does not display specified mask characters that exceed the converted string length of the passed expression. |
|
@T |
Trims leading and trailing spaces from character values. |
|
@X |
Appends DB to negative currency or numeric values to indicate a debit. |
|
@YL |
Use Long Date system setting. |
|
@YS |
Use Short Date system setting. |
|
@Z |
If 0, converts currency or numeric values to spaces. |
|
@( |
Encloses negative currency or numeric values in parentheses. |
|
@^ |
Converts currency or numeric values to scientific notation. |
|
@0 |
Converts numeric or currency values to their hexadecimal equivalents. The numeric or currency value must be positive and less than 4,294,967,296. |
|
@! |
Converts an entire character string to uppercase. |
|
@$ |
Adds the current currency symbol specified by SETCURRENCY to currency and numeric values. By default, the symbol is placed immediately before or after the value. However, the currency symbol and its placement (specified with SET CURRENCY), the separator character (specified with SET SEPARATOR) and the decimal character (specified with SET POINT) can all be changed. |
|
X |
Specifies the width of character values. For example, if cFormatCodes is 'XX', 2 characters are returned. |
|
Y |
Converts logical True (.T.) and False (.F.) values to Y and N, respectively. |
|
! |
Converts a lowercase character to uppercase in the corresponding position in a character string. |
|
. |
Specifies the decimal point position in currency and numeric values. |
|
, |
Separates digits to the left of the decimal point in currency and numeric values. |
|
9 |
Specifies width of numeric values. For example, if cFormatCodes is '999.99', the numeric characters are formatted with three characters to the left of the decimal point, and two characters to the right. |
TRIM(cExpression (, nFlags) (, cParseChar (, cParseChar2 (, ...))))
Definition / Description
Removes all trailing spaces or parsing characters from the specified character expression, or all trailing zero (0) bytes from the specified binary expression.
Syntax
TRIM(cExpression [, nFlags] [, cParseChar [, cParseChar2 [, ...]]])
Where:
- cExpression - Specifies an expression of Character or Varbinary type to remove all trailing spaces or 0 bytes from, respectively.
- nFlags - Specifies if trimming is case-sensitive when one or more parse characters (cParseChar, cParseChar2, … are included. Trimming is case-sensitive if nFlags is zero or is omitted. Trimming is case-insensitive if nFlags 1.
- cParseChar [, cParseChar2 [, ...]] - Specifies one or more character strings that are trimmed from the end of cExpression. If cParseChar isn't included, then trailing spaces or 0 bytes are removed from Expression. Maximum strings permitted in cParseChar is 23.
TTOC(tExpression (, 1 / 2 / 3))
Definition / Description
Converts a DateTime expression to a Character value with the specified format. This function works best with DateTime fields.
The scripting equivalent function is Utility.FormatTime.
Syntax
TTOC(tExpression [, 1 | 2 | 3])
Where:
- tExpression - Specifies a DateTime expression to convert into a character string. If tExpression contains only a time, Visual FoxPro adds the default date of 12/30/1899 to tExpression. If tExpression contains only a date, Visual FoxPro adds a default time of 12:00:00 AM (if SET HOURS is 12) or 00:00:00 (if SET HOURS is 24) to tExpression to produce a valid DateTime value. 1 - Returns tExpression as a character string in a format suitable for indexing. The character string returned has a 14-character format, yyyymmddhhmmss, that is not affected by the SET CENTURY, the SET HOURS, or SET SECONDS commands. 2 - Returns tExpression as a character string containing only the time portion of tExpression. To control whether to include the seconds of the time portion in the character string, set the SET SECONDS and SET DATE commands. The SET HOURS command determines if AM or PM is appended to the time portion returned. Note: If SET DATE is set to LONG or SHORT, the format of the time setting in the Windows Control Panel determines the format of the character string. 3 - Returns tExpression as a character string with the XML DateTime format. The character string returned has a 19-character yyyy-mm-ddThh:mm:ss format that is not affected by the settings of the SET CENTURY, SET HOURS, or SET SECONDS commands.
LTRIM(Expression (, nFlags) (, cParseChar (, cParseChar2 (, ...))))
Definition / Description
Removes all leading spaces or parsing characters from the specified character expression, or all leading zero (0) bytes from the specified binary expression.
The scripting equivalent function is String.Ltrim.
Syntax
LTRIM(Expression [, nFlags] [, cParseChar [, cParseChar2 [, ...]]])
Where:
- cExpression - Specifies an expression of Character or Varbinary type to remove leading spaces or 0 bytes from, respectively.
- nFlags - Specifies if trimming is case-sensitive when one or more parse characters (cParseChar, cParseChar2, … are included. Trimming is case-sensitive if nFlags is zero or is omitted. Trimming is case-insensitive if nFlags 1.
- cParseChar [, cParseChar2 [, ...]] - Specifies one or more character strings that are trimmed from the beginning of cExpression. If cParseChar isn't included, then leading spaces or 0 bytes are removed from Expression. Maximum strings permitted in cParseChar is 23.
RTRIM(Expression (, nFlags) (, cParseChar (, cParseChar2 (, ...))))
Definition / Description
Removes all trailing spaces or parsing characters from the specified character expression, or all leading zero (0) bytes from the specified binary expression.
The scripting equivalent function is String.Rtrim.
Syntax
RTRIM(Expression [, nFlags] [, cParseChar [, cParseChar2 [, ...]]])
Where:
- cExpression - Specifies an expression of Character or Varbinary type to remove all trailing spaces or 0 bytes from, respectively.
- nFlags - Specifies if trimming is case-sensitive when one or more parse characters (cParseChar, cParseChar2, … are included. Trimming is case-sensitive if nFlags is zero or is omitted. Trimming is case-insensitive if nFlags 1.
- cParseChar [, cParseChar2 [, ...]] - Specifies one or more character strings that are trimmed from the end of cExpression. If cParseChar isn't included, then leading spaces or 0 bytes are removed from Expression. Maximum strings permitted in cParseChar is 23.
UPPER(cExpression)
Definition / Description
Returns the specified character expression in uppercase.
The scripting equivalent function is String.ToUpper.
Syntax
Upper(cExpression)
Where:
- cExpression Specifies the character expression UPPER( ) converts to uppercase.
Example
If Name = 123 Test, UPPER(Name) = 123 TEST
VAL(cExpression)
Definition / Description
Returns a numeric or currency value from a character expression composed of numbers.
The scripting equivalent function is Convert.ToInteger.
Syntax
VAL(numeric field)
Where:
- numeric field Specifies a character expression composed of up to 16 numbers. Rounding occurs if more than 16 numbers are included in cExpression. If the first character of cExpression is a dollar sign ($),VAL( ) returns a currency value. In all other situations VAL( ) returns a numeric value
Example
If field = 2, VAL(field) = 2
YEAR(dExpression / tExpression)
Definition / Description
Returns the year from the specified date or datetime expression.
The scripting equivalent function is DateTime.Year.
Syntax
YEAR(dExpression / tExpression)
Where:
- dExpression Specifies a date expression from which YEAR( ) returns the year. dExpression can be a function that returns a date, or a Date-type memory variable, array element, or field. It can also be a literal date string, such as {^1998-06-06}.- tExpression Specifies a datetime expression from which YEAR( ) returns the year.
Example
Set Date = 06/01/2021, YEAR(Date) = 2021