Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Project functions for custom fields in Project desktop

These functions are used within formulas in order to calculate the values for custom fields.

Tip: To enter a formula into a custom field for Project 2007, on the Tools menu, point to Customize, and then click Fields. In the Custom Fields dialog box, click Formula. For Project 2010, click the Project tab, and in the Properties group, click Custom Fields. In the Custom Fields dialog box, click Formula.

Overview of Project functions

When you use Microsoft Project, you might need to work with values that do not reside directly in fields. Formulas enable you to work with fields to create these new values.

The following examples illustrate how you can use formulas with custom fields:

  • When added to a custom number field, the following formula returns a numerical value that indicates the number of days between the current date and the finish date of the tasks in your schedule:

DateDiff("d",NOW(),[Finish])

  • When added to a custom text field, the following formula returns a value of "No baseline," "Overbudget by 20% or more," or "Under budget":

    Switch(Len(CStr([Baseline Finish]))<3, "No baseline", ([Cost]+1)/ ([Baseline Cost]+1)>1.2,"Overbudget by 20% or more", ([Cost]+1)/([Baseline Cost]+1)>1, "Overbudget",True,"Under budget")
    The CStr function in the above formula works only if the [Baseline Finish] field is defined.

Conversion functions

Asc

Returns an Integer representing the character code that corresponds to the first letter in a string.

Syntax

Asc( string )

string  Any valid string expression.

CBool

Coerces an expression to data type Boolean.

Syntax

CBool( expression )

expression  Any valid string or numeric expression.

CByte

Coerces an expression to data type Byte.

Syntax

CByte( expression )

expression  Any Integer from 0 to 255.

CCur

Coerces an expression to data type Currency.

Syntax

CCur( expression )

expression  Any numeric expression from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.

CDate

Coerces an expression to data type Date.

Syntax

CDate( expression )

expression  Any valid date expression.

CDbl

Coerces an expression to data type Double.

Syntax

CDbl( expression )

expression  Any numeric expression from -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.

CDec

Coerces an expression to data type Decimal.

Syntax

CDec( expression )

expression  Any numeric expression from +/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is +/-7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001.

Chr

Returns a String containing the character associated with the specified character code.

Syntax

Chr( charcode )

charcode  A Long that identifies a character.

CInt

Coerces an expression to data type Integer.

Syntax

CInt( expression )

expression  Any numeric expression from -32,768 to 32,767; fractions are rounded.

CLng

Coerces an expression to data type Long.

Syntax

CLng( expression )

expression  Any numeric expression from -2,147,483,648 to 2,147,483,647; fractions are rounded.

CSng

Coerces an expression to data type Single.

Syntax

CSng( expression )

expression  Any numeric expression -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.

CStr

Coerces an expression to data type String.

Syntax

CStr( expression )

expression  Any valid string or numeric expression.

CVar

Coerces an expression to data type Variant.

Syntax

CVar( expression )

expression  Same range as Double for numerics. Same range as String for non-numerics.

DateSerial

Returns a Variant (Date) for a specified year, month, and day.

Syntax

DateSerial( year, month, day )

year  Required; Integer. Number between 100 and 9999, inclusive, or a numeric expression.

month  Required; Integer. Any numeric expression.

day  Required; Integer. Any numeric expression.

DateValue

Returns a Variant (Date).

Syntax

DateValue( date )

date  Required; normally a string expression representing a date from January 1, 100, through December 31, 9999. However, date can also be any expression that can represent a date, a time, or both a date and time, in that range.

Day

Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month.

Syntax

Day( date)

date  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a date. If date contains Null, Null is returned.

Hex

Returns a String representing the hexadecimal value of a number.

Syntax

Hex( number )

number  Required; any valid string or numeric expression.

Hour

Returns a Variant (Integer) specifying a whole number between 0 and 23, inclusive, representing the hour of the day.

Syntax

Hour( time )

time  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a time. If time contains Null, Null is returned.

Minute

Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the minute of the hour.

Syntax

Minute( time )

time  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a time. If time contains Null, Null is returned.

Month

Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.

Syntax

Month( date)

date  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a time. If date contains Null, Null is returned.

Oct

Returns a Variant (String) representing the octal value of a number.

Syntax

Oct( number )

number  Required; any valid string or numeric expression.

ProjDateConv

Converts a value to a date.

Syntax

ProjDateConv( expression, dateformat )

expression  Required; Variant. The expression to convert to a date.

dateformat  Optional; Long. The date format default is pjDateDefault, but you can substitute one of the following pjDateFormat constants (date format applied 9/25/07 at 12:33 P.M.):

  • pjDateDefault: The default format. Set on the View tab in the Options dialog box (Tools menu).

  • pjDate_mm_dd_yy_hh_mmAM: 9/25/07 12:33 PM

  • pjDate_mm_dd_yy: 9/25/07

  • pjDate_mm_dd_yyyy: 9/25/2007

  • pjDate_mmmm_dd_yyyy_hh_mmAM: September 25, 2007 12:33 PM

  • pjDate_mmmm_dd_yyyy: September 25, 2007

  • pjDate_mmm_dd_hh_mmAM: Sep 25 12:33 PM

  • pjDate_mmm_dd_yyy: Sep 25, '07

  • pjDate_mmmm_dd: September 25

  • pjDate_mmm_dd: Sep 25

  • pjDate_ddd_mm_dd_yy_hh_mmAM: Tue 9/25/07 12:33 PM

  • pjDate_ddd_mm_dd_yy: Tue 9/25/07

  • pjDate_ddd_mmm_dd_yyy: Tue Sep 25, '07

  • pjDate_ddd_hh_mmAM: Tue 12:33 PM

  • pjDate_mm_dd: 9/25

  • pjDate_dd: 25

  • pjDate_hh_mmAM: 12:33 PM

  • pjDate_ddd_mmm_dd: Tue Sep 25

  • pjDate_ddd_mm_dd: Tue 9/25

  • pjDate_ddd_dd: Tue 25

  • pjDate_Www_dd: W40/2

  • pjDate_Www_dd_yy_hh_mmAM: W40/2/07 12:33 PM

ProjDurConv

Converts an expression to a duration value in the specified units.

Syntax

ProjDurConv( expression, durationunits )

expression  Required; Variant. The expression to convert to a duration.

durationunits  Optional; Long. The units used to express the duration. If durationunits is not specified, the default value is the type of units specified in the Duration is entered in option on the Schedule tab of the Options dialog box (Tools menu). The durationunits can be one of the following pjFormatUnit constants:

  • pjMinutes: pjElapsedMinutes

  • pjHours: pjElapsedHours

  • pjDays: pjElapsedDays

  • pjWeeks: pjElapsedWeeks

  • pjMonths: pjElapsedMonths

  • pjMinutesEstimated: pjElapsedMinutesEstimated

  • pjHoursEstimated: pjElapsedHoursEstimated

  • pjDaysEstimated: pjElapsedDaysEstimated

  • pjWeeksEstimated: pjElapsedWeeksEstimated

  • pjMonthsEstimated: pjElapsedMonthsEstimated

Second

Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the second of the minute.

Syntax

Second( time )

time  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a time. If time contains Null, Null is returned.

Str

Returns a Variant (String) representation of a number.

Syntax

Str( number )

number  Required; Long containing any valid numeric expression.

StrConv

Returns a Variant (String) converted as specified.

Syntax

StrConv( string, conversion, LCID )

string  Required; string expression to be converted.

conversion  Required; Integer. The sum of values specifying the type of conversion to perform.

LCID  Optional; the LocaleID, if different from the system LocaleID. (The system LocaleID is the default.)

TimeSerial

Returns a Variant (Date) containing the time for a specific hour, minute, and second.

Syntax

TimeSerial( hour, minute, second )

hour  Required; Variant (Integer). Number between 0 (12:00 A.M.) and 23 (11:00 P.M.), inclusive, or a numeric expression.

minute  Required; Variant (Integer). Any numeric expression.

second  Required; Variant (Integer). Any numeric expression.

TimeValue

Returns a Variant (Date) containing the time.

Syntax

TimeValue( time)

time  Required; normally a string expression representing a time from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.), inclusive. However, time can also be any expression that represents a time in that range. If time contains Null, Null is returned.

Val

Returns the numbers contained in a string as a numeric value of appropriate type.

Syntax

Val( string )

string  Required; any valid string expression.

Weekday

Returns a Variant (Integer) containing a whole number representing the day of the week.

Syntax

Weekday( date[, firstdayofweek] )

date  Required; Variant, numeric expression, string expression of any combination, that can represent a date. If date contains Null, Null is returned.

firstdayofweek  Optional; a constant that specifies the first day of the week. If not specified, Sunday is assumed.

Year

Returns a Variant (Integer) containing a whole number representing the year.

Syntax

Year( date )

date  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a date. If date contains Null, Null is returned.

Date/Time functions

CDate

Coerces an expression to data type Date.

Syntax

CDate( expression )

expression  Any valid date expression.

Date

Returns a Variant (Date) containing the current system date.

Syntax

Date

DateAdd

Returns a Variant (Date) containing a date to which a specified time interval has been added.

Syntax

DateAdd( interval, number, date )

interval  Required; String expression that is the interval of time you want to add, such as "m" or "d" .

number  Required; numeric expression that is 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  Required; Variant (Date) or literal representing date to which the interval is added.

DateDiff

Returns a Variant (Long) specifying the number of time intervals between two specified dates.

Syntax

DateDiff( interval, date1, date2[, firstdayofweek[, firstweekofyear]] )

interval  Required; string expression that is the interval of time you use to calculate the difference between date1 and date2.

date1, date2  Required; Variant (Date). Two dates you want to use in the calculation.

firstdayofweek  Optional; a constant that specifies the first day of the week. If not specified, Sunday is assumed.

firstweekofyear  Optional; a constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

DatePart

Returns a Variant (Integer) containing the specified part of a given date.

Syntax

DatePart( interval, date[, firstdayofweek[, firstweekofyear]] )

interval  Required; string expression that is the interval of time you want to return.

date  Required; Variant (Date) value that you want to evaluate.

firstdayofweek  Optional; a constant that specifies the first day of the week. If not specified, Sunday is assumed.

firstweekofyear  Optional; a constant that specifies the first week of the year. If not specified, the first week is assumed to be in the week in which January 1 occurs.

DateSerial

Returns a Variant (Date) for a specified year, month, and day.

Syntax

DateSerial( year, month, day )

year  Required; Integer. Number between 100 and 9999, inclusive, or a numeric expression.

month  Required; Integer. Any numeric expression.

day  Required; Integer. Any numeric expression.

DateValue

Returns a Variant (Date).

Syntax

DateValue( date)

date  Required; normally a string expression representing a date from January 1, 100, through December 31, 9999. However, date can also be any expression that can represent a date, a time, or both a date and time, in that range.

Day

Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month.

Syntax

Day( date)

date  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a date. If date contains Null, Null is returned.

Hour

Returns a Variant (Integer) specifying a whole number between 0 and 23, inclusive, representing the hour of the day.

Syntax

Hour( time )

time  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a time. If time contains Null, Null is returned.

IsDate

Returns a Boolean value indicating whether an expression can be converted to a date.

Syntax

IsDate( expression )

expression  Required; any Variant containing a date expression or string expression recognizable as a date or time.

Minute

Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the minute of the hour.

Syntax

Minute( time)

time  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a time. If time contains Null, Null is returned.

Month

Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.

Syntax

Month( date)

date  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a time. If date contains Null, Null is returned.

Now

Returns a Variant (Date) specifying the current date and time according your computer's system date and time.

Syntax

Now

ProjDateAdd

Adds a duration to a date to return a new date.

Syntax

ProjDateAdd( date, duration, calendar )

date  Required; Variant. The original date to which duration is added.

duration  Required; Variant. The duration to add to date.

calendar  Optional; String. The calendar to use when calculating the new date. If calendar is not specified, the default for the current resource is the resource calendar, or for the current task, the task calendar or the standard calendar if there is no task calendar. For Project Server, the standard calendar will be used, regardless of which calendar is specified in the calendar string. When you use this function in a formula created in Project Web App and compare it with the same formula created in Project Professional, you should test to ensure the results are what you expect.

Note:  To subtract seven days from a specified date, the following formula works correctly on Project Professional 2010: ProjDateAdd("9/24/2010", "-7d"). However, when you run the same formula on Project Server 2010, the result is 9/24/2010, not 9/17/2010. For formulas that work consistently on Project Professional 2010 and Project Server 2010, you should avoid negative parameters for the ProjDateAdd and ProjDateSub functions.

ProjDateConv

Converts a value to a date.

Syntax

ProjDateConv( expression, dateformat )

expression  Required; Variant. The expression to convert to a date.

dateformat  Optional; Long. The date format default is pjDateDefault, but you can substitute one of the following pjDateFormat constants (date format applied 9/25/07 at 12:33 P.M.):

  • pjDateDefault: The default format. Set on the View tab in the Options dialog box (Tools menu).

  • pjDate_mm_dd_yy_hh_mmAM: 9/25/07 12:33 PM

  • pjDate_mm_dd_yy: 9/25/07

  • pjDate_mm_dd_yyyy: 9/25/2007

  • pjDate_mmmm_dd_yyyy_hh_mmAM: September 25, 2007 12:33 PM

  • pjDate_mmmm_dd_yyyy: September 25, 2007

  • pjDate_mmm_dd_hh_mmAM: Sep 25 12:33 PM

  • pjDate_mmm_dd_yyy: Sep 25, '07

  • pjDate_mmmm_dd: September 25

  • pjDate_mmm_dd: Sep 25

  • pjDate_ddd_mm_dd_yy_hh_mmAM: Tue 9/25/07 12:33 PM

  • pjDate_ddd_mm_dd_yy: Tue 9/25/03

  • pjDate_ddd_mmm_dd_yyy: Tue Sep 25, '07

  • pjDate_ddd_hh_mmAM: Tue 12:33 PM

  • pjDate_mm_dd: 9/25

  • pjDate_dd: 25

  • pjDate_hh_mmAM: 12:33 PM

  • pjDate_ddd_mmm_dd: Tue Sep 25

  • pjDate_ddd_mm_dd: Tue 9/25

  • pjDate_ddd_dd: Tue 25

  • pjDate_Www_dd: W40/2

  • pjDate_Www_dd_yy_hh_mmAM: W40/2/07 12:33 PM

ProjDateDiff

Returns the duration between two dates in minutes.

Syntax

ProjDateDiff( date1, date2, calendar )

date1  Required; Variant. The date used as the beginning of the duration.

date2  Required; Variant. The date used as the end of the duration.

calendar  Optional; String. The calendar to use when calculating the duration. If calendar is not specified, the default for the current resource is the resource calendar, or for the current task, the task calendar (or the standard calendar if there is no task calendar For Project Server, the standard calendar will be used, regardless of which calendar is specified in the calendar string. When you use this function in a formula created in Project Web App and compare it with the same formula created in Project Professional, you should test to ensure the results are what you expect.

ProjDateSub

Returns the date that precedes another date by a specified duration.

Syntax

ProjDateSub( date, duration, calendar )

date  Required; Variant. The original date from which duration is subtracted.

duration  Required; Variant. The duration to subtract from date.

calendar  Optional; String. The calendar to use when calculating the date difference. If calendar is not specified, the default for the current resource is the resource calendar, or for the current task, the task calendar (or the standard calendar if there is no task calendar). For Project Server, the standard calendar will be used, regardless of which calendar is specified in the calendar string. When you use this function in a formula created in Project Web App and compare it with the same formula created in Project Professional, you should test to ensure the results are what you expect.

Note:  To subtract seven days from a specified date, the following formula works correctly on Project Professional 2010: ProjDateAdd("9/24/2010", "-7d"). However, when you run the same formula on Project Server 2010, the result is 9/24/2010, not 9/17/2010. For formulas that work consistently on Project Professional 2010 and Project Server 2010, you should avoid negative parameters for the ProjDateAdd and ProjDateSub functions.

ProjDateValue

Returns the date value of an expression.

Syntax

ProjDateValue( expression )

expression  Required; Variant. The expression to be represented as a date.

ProjDurConv

Converts an expression to a duration value in the specified units.

Syntax

ProjDurConv( expression, durationunits )

expression  Required; Variant. The expression to convert to a duration.

durationunits  Optional; Long. The units used to express the duration. If durationunits is not specified, the default value is the type of units specified in the Duration is entered in option on the Schedule tab of the Options dialog box (Tools menu). The durationunits can be one of the following pjFormatUnit constants:

  • pjMinutes: pjElapsedMinutes

  • pjHours: pjElapsedHours

  • pjDays: pjElapsedDays

  • pjWeeks: pjElapsedWeeks

  • pjMonths: pjElapsedMonths

  • pjMinutesEstimated: pjElapsedMinutesEstimated

  • pjHoursEstimated: pjElapsedHoursEstimated

  • pjDaysEstimated: pjElapsedDaysEstimated

  • pjWeeksEstimated: pjElapsedWeeksEstimated

  • pjMonthsEstimated: pjElapsedMonthsEstimated

ProjDurValue

Returns the number of minutes in a duration.

Syntax

ProjDurValue( duration )

duration  Required; Variant. The duration to be expressed in minutes.

Second

Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the second of the minute.

Syntax

Second( time )

time  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a time. If time contains Null, Null is returned.

Time

Returns a Variant (Date) indicating the current system time.

Syntax

Time

Timer

Returns a Single representing the number of seconds elapsed since midnight.

Syntax

Timer

TimeSerial

Returns a Variant (Date) containing the time for a specific hour, minute, and second.

Syntax

TimeSerial( hour, minute, second)

hour  Required; Variant (Integer). Number between 0 (12:00 A.M.) and 23 (11:00 P.M.), inclusive, or a numeric expression.

minute  Required; Variant (Integer). Any numeric expression.

second  Required; Variant (Integer). Any numeric expression.

TimeValue

Returns a Variant (Date) containing the time.

Syntax

TimeValue( time)

time  Required; normally a string expression representing a time from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.), inclusive. However, time can also be any expression that represents a time in that range. If time contains Null, Null is returned.

Weekday

Returns a Variant (Integer) containing a whole number representing the day of the week.

Syntax

Weekday( date[, firstdayofweek] )

date  Required; Variant, numeric expression, string expression of any combination, that can represent a date. If date contains Null, Null is returned.

firstdayofweek  Optional; a constant that specifies the first day of the week. If not specified, Sunday is assumed.

Year

Returns a Variant (Integer) containing a whole number representing the year.

Syntax

Year( date)

date  Required; any Variant, numeric expression, or string expression, or any combination, that can represent a date. If date contains Null, Null is returned.

General functions

Choose

Selects and returns a value from a list of arguments.

Syntax

Choose( index, choice-1[, choice-2, ... [, choice-n]])

index  Required; numeric expression or field that results in a value between 1 and the number of available choices.

choice  Required; Variant expression containing one of the possible choices.

IIf

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

Syntax

IIf( expr, truepart, falsepart )

expr  Required; Variant expression you want to evaluate.

truepart  Required; value or expression to be returned if the corresponding expression is True.

falsepart  Required; value or expression to be returned if the corresponding expression isFalse.

IsNumeric

Returns a Boolean value indicating whether an expression can be evaluated as a number.

Syntax

IsNumeric( expression)

expression  Required; Variant containing a numeric expression or string expression.

IsNull

Returns a Boolean value that indicates whether an expression contains no valid data (Null).

Syntax

IsNull( expression )

expression  Required; Variant containing a numeric expression or string expression.

Switch

Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Syntax

Switch( expr-1, value-1[, expr-2, value-2, ... [, expr-n,value-n]] )

Math functions

Abs

Returns a value of the same type that is passed to it specifying the absolute value of a number.

Syntax

Abs( number )

number  Required; any valid numeric expression. If number contains Null, Null is returned; if it is an uninitialized variable, zero is returned.

Atn

Returns a Double specifying the arc tangent of a number.

Syntax

Atn( number )

number  Required; Double or any valid numeric expression.

Cos

Returns a Double specifying the cosine of an angle.

Syntax

Cos( number )

number  Required; Double or any valid numeric expression that expresses an angle in radians.

Exp

Returns a Double specifying e (the base of natural logarithms) raised to a power.

Syntax

Exp( number )

number  Required; Double or any valid numeric expression.

Fix

Returns the integer portion of a number. If the number is negative, returns the first negative integer equal to or greater than number.

Syntax

Fix( number )

number  Required; Double or any valid numeric expression. If number contains Null, Null is returned.

Int

Returns the integer portion of a number. If the number is negative, returns the first negative integer less than or equal to number.

Syntax

Int( number )

number  Required; Double or any valid numeric expression. If number contains Null, Null is returned.

Log

Returns a Double specifying the natural logarithm of a number.

Syntax

Log( number )

number  Required; Double or any valid numeric expression greater than zero.

Rnd

Returns a Single containing a random number.

Syntax

Rnd( number )

number  Required; Single or any valid numeric expression.

Sgn

Returns a Variant (Integer) indicating the sign of a number.

Syntax

Sgn( number )

number  Required; any valid numeric expression.

Return values are as follows:

  • If the number is greater than zero, Sgn returns 1.

  • If the number is equal to zero, Sgn returns 0.

  • If the number is less than zero, Sgn returns -1.

Sin

Returns a Double specifying the sine of an angle.

Syntax

Sin( number )

number  Required; Double or any valid numeric expression that expresses an angle in radians.

Sqr

Returns a Double specifying the square root of a number.

Syntax

Sqr( number )

number  Required; Double or any valid numeric expression equal to or greater than zero.

Tan

Returns a Double specifying the tangent of an angle.

Syntax

Tan( number )

number  Required; Double or any valid numeric expression that expresses an angle in radians.

Text functions

Asc

Returns an Integer representing the character code that corresponds to the first letter in a string.

Syntax

Asc( string )

String  Any valid string expression.

Chr

Returns a String containing the character associated with the specified character code.

Syntax

Chr( charcode )

charcode  A Long that identifies a character.

Format

Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.

Syntax

Format( expression[, format[, firstdayofweek[, firstweekofyear]]] )

expression  Required; any valid expression.

format  Optional; a valid named or user-defined format expression.

firstdayofweek  Optional; a Constant that specifies the first day of the week.

firstweekofyear  Optional; a Constant that specifies the first week of the year.

Instr

Returns a Variant (Long) specifying the position of the first occurrence of one string within another.

Syntax

Instr( [start, ]string1, string2[, compare] )

start  Optional; numeric expression that sets the starting position for each search. If omitted, search begins at the first character position. If start contains Null, an error occurs. The start argument is required if compare is specified.

string1  Required; string expression being searched.

string2  Required; string expression sought.

compare  Optional; specifies the type of string comparison. If compare is Null, an error occurs. If compare is omitted, the Optioncompare setting determines the type of comparison.

LCase

Returns a String that has been converted to lowercase.

Syntax

LCase( string )

string   Required; any valid string expression. If string contains Null, Null is returned.

Left

Returns a Variant (String) containing a specified number of characters from the left side of a string.

Syntax

Left( string, length )

string  Required. String expression from which the leftmost characters are returned. If string contains Null, Null is returned.

length  Required; Variant (Long). Numeric expression indicating how many characters to return. If 0, a zero-length string ("") is returned. If greater than or equal to the number of characters in string, the entire string is returned.

Len

Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.

Syntax

Len( string, varname )

string  Any valid string expression. If string contains Null, Null is returned.

varname  Any valid variable name. If varname contains Null, Null is returned. If varname is a Variant, Len treats it the same as a String and always returns the number of characters it contains.

LTrim

Returns a Variant (String) containing a copy of a specified string without leading spaces.

Syntax

LTrim( string )

string  Required; any valid string expression. If string contains Null, Null is returned.

Mid

Returns a Variant (String) containing a specified number of characters from a string.

Syntax

Mid( string, start[, length] )

string  Required; string expression from which characters are returned. If string contains Null, Null is returned.

start  Required; Long. Character position in string at which the part to be taken begins. If start is greater than the number of characters in string, Mid returns a zero-length string ("").

length  Optional; Variant (Long). Number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start), all characters from the start position to the end of the string are returned.

Right

Returns a Variant (String) containing a specified number of characters from the right side of a string.

Syntax

Right( string, length )

string  Required; string expression from which the rightmost characters are returned. If string contains Null, Null is returned.

length  Required; Variant (Long). Numeric expression indicating how many characters to return. If 0, a zero-length string ("") is returned. If equal to or greater than the number of characters in string, the entire string is returned.

RTrim

Returns a Variant (String) containing a copy of a specified string without trailing spaces.

Syntax

RTrim( string )

string  Required; any valid string expression. If string contains Null, Null is returned.

Space

Returns a Variant (String) consisting of the specified number of spaces.

Syntax

Space( number )

number  Required; number of spaces you want in the string.

StrComp

Returns a Variant (Integer) indicating the result of a string comparison.

Syntax

StrComp( string1, string2[, compare] )

string1  Required; any valid string expression.

string2  Required; any valid string expression.

compare  Optional; specifies the type of string comparison. If the compare argument is Null, an error occurs.

StrConv

Returns a Variant (String) converted as specified.

Syntax

StrConv( string, conversion, LCID )

string  Required; string expression to be converted.

conversion  Required; Integer. The sum of values specifying the type of conversion to perform.

LCID  Optional; the LocaleID, if different from the system LocaleID. (The system LocaleID is the default.)

String

Returns a Variant (String) containing a repeating character string of the length specified.

Syntax

String( number, character )

number  Required; Long. Length of the returned string. If number contains Null, Null is returned.

character  Required; Variant. Character code specifying the character or string expression whose first character is used to build the return string. If character contains Null, Null is returned.

Trim

Returns a Variant (String) containing a copy of a specified string without leading or trailing spaces.

Syntax

Trim( string )

string  Required; any valid string expression. If string contains Null, Null is returned.

UCase

Returns a Variant (String) containing the specified string, converted to uppercase.

Syntax

UCase( string )

string  Required; any valid string expression. If string contains Null, Null is returned.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×