Abs is a math function that returns a value that represents the absolute value of the number. That is, the magnitude of the number without the positive or negative sign. For example, Abs(-5) would return 5.

Syntax

Abs(number)

Argument

Number (required)

This is the numeric expression you are evaluating. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

Asc is a conversion function used to convert a string to its Ascii code. For example, Asc("A") would return 65 because 65 is the Ascii code for the uppercase letter A. If you pass a whole word to the Asc function, it will only return the Ascii code for the first letter of the word.

Syntax

Asc(String)

Argument

String (required)

This is the string you are evaluating. If the string you are passing to the function contains no characters, the function will fail and produce a runtime error.

Atn is a math function that allows you to calculate the arctangent of a number.

Syntax

Atn(number)

Argument

Number (required)

This is the numeric expression you are evaluating.

The Choose function is a program flow function that allows you to return a value from a list of choices based on a given position. For instance: Choose(3, "Microsoft", "Access", "Data", "Analysis") would return "Data" This is because word "Data" is in the third position in the list of values.

Syntax

Choose(PositionNumber, List of Values Separated by Commas)

Arguments

PositionNumber (required)

This is the numeric expression or field that results in a value between 1 and the number of available choices. If this argument's value is less than 1 or greater than the number of choices in the function, a Null value will be returned.

List of Values Separated by Commas (required)

This is a variant expression that contains a list of one or more values.

Chr is a conversion function used to convert an Ascii code to a string. For example, Chr(65) would return "A".

Syntax

Chr(Number)

Arguments

Number (required)

This is the number value that represents an Ascii character code. If the number you are passing to the function is not a valid Ascii character code, the function will fail and produce a runtime error.

The Cos function is a math function that allows you to calculate the cosine of an angle.

Syntax

Cos(number)

Arguments

Number (required)

This is the numeric expression that represents an angle in radians.

The Date function returns today's date based on your PC's current system date. The Date function is key to performing any analysis that involves a time comparison in relation to today's date. There are no required arguments for this function; to use it, simply enter: Date().

The DateAdd function returns a date to which a specified interval has been added. In other words, the DateAdd function allows you calculate a date by adding 30 days to it, subtracting 3 weeks from it, adding 4 months to it, or so on. For example:

Syntax

DateAdd(Interval, Number, Date)

Arguments

Interval (required)

This is the interval of time that you want to use. The intervals available are:

Number (required)

This is the number of intervals to add. A positive number will return a date in the future, while a negative number will return a date in the past.

Date (required)

This is the date value with which you are working. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

The DateDiff function returns the difference between two dates based on a specified time interval. For example, DateDiff('yyyy', #5/16/1972#, #5/16/2005#) returns 33 because there is a difference of 33 years between the two dates.

Syntax

DateDiff(Interval, Date1, Date2, FirstDayOfTheWeek, FirstWeekOfTheYear)

Arguments

Interval (required)

This is the interval of time that you want to use. The intervals available are:

Date1 (required)

This is one of the two dates you want to calculate the difference between. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

Date2 (required)

This is one of the two dates you want to calculate the difference between. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

FirstDayOfTheWeek (optional)

This specifies which day you want to count as the first day of the week. Enter 1 in this argument to make the first day Sunday, 2 for Monday, 3 for Tuesday, and so on. If this argument is omitted, the first day is a Sunday by default.

FirstWeekOfTheYear (optional)

This specifies the first week of the year. In most cases, you would omit this argument. This uses the first week that includes January 1 as the default. However, you can alter this setting by using one of the following values.

The DatePart function allows you to evaluate a date and return a specific interval of time represented in that date. For example, DatePart("q",#6/4/2004#) returns 2 (as in second quarter), the quarter represented in that date.

Syntax

DatePart(Interval, ValidDate, FirstDayOfTheWeek, FirstWeekOfTheYear)

Arguments

Interval (required)

This is the interval of time want to use. The intervals available are:

ValidDate (required)

This is the date value with which you are working. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

FirstDayOfTheWeek (optional)

This specifies which day you want to count as the first day of the week. Enter 1 in this argument to make the first day Sunday, 2 for Monday, 3 for Tuesday, and so on. If this argument is omitted, the first day is a Sunday by default.

FirstWeekOfTheYear (optional)

This specifies the first week of the year. In most cases, you would omit this argument. This uses the first week that includes January 1 as the default. However, you can alter this setting by using one of the following values.

The DateSerial function allows you to construct a date value by combining given year, month, and day components. This function is perfect for converting disparate values that, together represent a date, into an actual date. For example, DateSerial(2004, 4, 3) would return April 3, 2004.

Syntax

DateSerial(Year, Month, Day)

Arguments

Year (required)

Any number or numeric expression from 100 to 9999

Month (required)

Any number or numeric expression

Day (required)

Any number or numeric expression

The DateValue function allows you to convert any string or expression that represents a valid date, time, or both into a date value. For Example, DateValue("October 31, 2004") would return 10/31/2004.

Syntax

DateValue(Expression)

Arguments

Expression (required)

Any string or valid expression that can represent a valid date, time, or both

Day is a conversion function that converts a valid date to a number from 1 to 31, representing the day of the month for a given date. For example, Day(#5/16/1972#) would return 16.

Syntax

Day(ValidDate)

Arguments

ValidDate (required)

This is any value that can represent a valid date. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

DDB is a financial function that calculates the depreciation of an asset for a specific period using the double-declining balance method or another specified method.

Syntax

DDB(Cost, Salvage, Life, Period, Factor)

Arguments

Cost (required)

This is the initial cost of the asset; must be a positive number.

Salvage (required)

This is the value of the asset at the end of its useful life; must be a positive number.

Life (required)

This is the length of the useful life of the asset.

Period (required)

This is the period for which asset depreciation is calculated.

Factor (optional)

This is the rate at which the balance declines. The default setting for this argument is the double-declining method (a factor of 2).

Domain aggregate functions allow you to extract and aggregate statistical information from an entire dataset (a domain). These functions differ from an Aggregate query in that an Aggregate query will group data before evaluating the values, while a domain aggregate function will evaluate the values for the entire dataset. There are 12 different domain aggregate functions, but they all have the same Syntax.

Syntax

("Field Name]","[Dataset Name]", "[Criteria]")

Arguments

Field Name (required)

This expression identifies the field containing the data with which you are working. This argument must be in quotes.

Dataset Name (required)

This expression identifies the table or query you are working with; also known as the domain. This argument must be in quotes.

Criteria (optional)

This expression is used to restrict the range of data on which the domain aggregate function is performed. If omitted, the domain aggregate function is performed against the entire dataset. This argument must be in quotes.

Additional Remarks

The 12 different domain aggregate functions are:

DSum

The DSum function returns the total sum value of a specified field in the domain.

DSum("[Sales_Amount]", "[TransactionMaster]") would give you the total sum of sales amount in the TransactionMaster table.

DAvg

The DAvg function returns the average value of a specified field in the domain.

DAvg("[Sales_Amount]", "[TransactionMaster]") would give you the average sales amount in the TransactionMaster table.

DCount

The DCount function returns the total number of records in the domain.

DCount("*", "[TransactionMaster]") would give you the total number of records in the TransactionMaster table.

DLookup

The DLookup function returns the first value of a specified field that matches the criteria you define within the DLookup function. If you don't supply criteria, the DLookup function returns a random value in the domain. DLookup functions are particularly useful when you need to retrieve a value from an outside dataset.

DLookUp("[Last_Name]","[Employee_Master]","[Employee_Number]='42620'") would return the value in the Last_Name field of the record where the Employee_Number is '42620'.

DMin, DMax

The DMin and DMax would return the minimum and maximum values in the domain respectively.

DMin("[Sales_Amount]", "[TransactionMaster]") would return the lowest sales amount in the Transactionmaster, while

DMin("[Sales_Amount]", "[TransactionMaster]") would return the highest.

DFirst, DLast

The DFirst and DLast would return the first and last values in the domain respectively.

DFirst("[Sales_Amount]", "[TransactionMaster]") would return the first sales amount in the Transactionmaster while

DLast("[Sales_Amount]", "[TransactionMaster]") would return the last.

DStdev, Dstdevp, DVar, Dvarp

You can use the DStdev and the DStdevp to return the standard deviation across a population sample and a population, respectively. The Dvar and the Dvarp similarly returns the variance across a population sample and a population, respectively.

Exp is a math function that raises the base of natural logarithm's (2.718282) number to a power you specify.

Syntax

Exp(Number)

Arguments

Number (required)

This is the numeric expression used as the power to raise 2.718282.

FormatCurrency is a conversion function that converts an expression to a currency using the currency symbol defined by your computer's regional settings.

Syntax

FormatCurrency(Number,TrailingDigits,LeadingDigits,NegativeParens,Group)

Arguments

Number (required)

This is the number value you want to convert. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

TrailingDigits (optional)

This is the number of digits to the right of the decimal you want displayed.

LeadingDigits (optional)

This indicates whether a leading zero is displayed for fractional values. The settings for this argument are −1 for True, 0 for False, or −2 to use the computer's regional/default settings.

NegativeParens (optional)

This specifies if negative values should be wrapped in parentheses. The settings for this argument are −1 for True, 0 for False, or −2 to use the computer's regional settings FormatNumber.

Group (optional)

This indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. The settings for this argument are −1 for True, 0 for False, or −2 to use the computer's regional settings.

The FormatDateTime function is a conversion function that converts an expression to a date or time.

Syntax

FormatDateTime(Date,NamedFormat)

Arguments

Date (required)

This is the date/time expression you want to convert. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

NamedFormat (optional)

This is the format code specifying the date/time format you would like to use. The settings for this argument are as follows:

FormatNumber is a conversion function that converts a numeric expression to a formatted number.

Syntax

FormatNumber(Number,TrailingDigits,LeadingDigits,NegativeParens,Group)

Arguments

Number (required)

This is the number value you want to convert. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

TrailingDigits (optional)

This is the number of digits to the right of the decimal you want displayed.

LeadingDigits (optional)

This indicates whether a leading zero is displayed for fractional values. The settings for this argument are −1 for True, 0 for False, or −2 to use the computer's regional/default settings.

NegativeParens (optional)

This specifies whether negative values should be wrapped in parentheses. The settings for this argument are −1 for True, 0 for False, or −2 to use the computer's regional settings.

Group (optional)

This indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. The settings for this argument are −1 for True, 0 for False, or −2 to use the computer's regional settings.

FormatPercent is a conversion function that converts a numeric expression to a formatted percentage with a trailing percent (%) character.

Syntax

FormatPercent(Number,TrailingDigits,LeadingDigits,NegativeParens,Group)

Arguments

Number (required)

This is the number value you want to convert. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

TrailingDigits (optional)

This is the number of digits to the right of the decimal you want displayed.

LeadingDigits (optional)

This indicates whether a leading zero is displayed for fractional values. The settings for this argument are 1 for True, 0 for False, or 2 to use the computer's regional settings.

NegativeParens (optional)

This specifies if negative values should be wrapped in parentheses. The settings for this argument are 1 for True, 0 for False, or 2 to use the computer's regional settings.

Group (optional)

This indicates whether or not numbers are grouped using the group delimiter specified in the computer's regional settings. The settings for this argument are 1 for True, 0 for False, or 2 to use the computer's regional settings.

FV is a financial function that allows you to calculate an annuity's future value. An annuity is a series of fixed cash payments normally made against a loan over a period of time.

Syntax

FV(Rate, PaymentPeriods, PaymentAmount, PresentValue, Type)

Arguments

Rate (required)

This is the stated interest rate per period.

PaymentPeriods (required)

This is the total number of payment periods in the annuity.

PaymentAmount (required)

This is the payment amount, usually consisting of principal and interest.

PresentValue (optional)

This is the present value of future payments. If omitted, 0 is assumed.

Type (optional)

This argument specifies when payments are due. A value of 0 means that payments are due at the end of the payment period, while a value of 1 means that payments are due at the beginning of the payment period. If omitted, 0 is assumed.

Hour is a conversion function that converts a valid time to a number from 0 to 23, representing the hour of the day. For example, Hour(#9:30:00 PM#) would return 21.

Syntax

Hour(ValidTime)

Arguments

ValidTime (required)

This is any combination of values that can represent valid time. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

IIf is a program flow function allows you to create an If ... Then ... Else statement, returning one value if a condition evaluates to true, and another value if it evaluates to false.

Syntax

IIf(Expression, TrueAnswer, FalseAnswer)

Arguments

Expression (required)

This is the expression you want to evaluate.

TrueAnswer (required)

This is the value to return if the expression is true.

FalseAnswer (required)

This is the value to return if the expression is false.

InStr is a text function that searches for a specified string in another string and returns its position number. For example: InStr("Alexander, Mike","x") would return 4 because the "x" is character number 4 in this string.

Syntax

InStr(Start, SearchString, FindString, Compare)

Arguments

Start (optional)

This is the character number to start the search; default is 1.

SearchString (required)

This is the string to be searched.

FindString (required)

This is the string to search for.

Compare (optional)

This specifies the type of string comparison.

Additional Remarks

The Compare argument can have the following values:

−1

Performs a comparison using the setting of the Option Compare statement

0

Performs a binary comparison

1

Performs a textual comparison

2

(Microsoft Access only) Performs a comparison based on information in your database

InStrRev is a text function that searches for a specified string in another string and returns its position number from the end of the string.

Syntax

InstrRev(SearchString, FindString, Start, Compare)

Arguments

SearchString (required)

This is the string to be searched.

FindString (required)

This is the string to search for.

Start (optional)

This is character number to start the search; default is 1.

Compare (optional)

This specifies the type of string comparison.

Additional Remarks

The Compare argument can have the following values:

−1

Performs a comparison using the setting of the Option Compare statement

0

Performs a binary comparison

1

Performs a textual comparison

2

(Microsoft Access only) Performs a comparison based on information in your database

IPmt is a financial function that allows you to calculate the interest paid within a specified period during the life of an annuity. An annuity is a series of fixed cash payments normally made against a loan over a period of time.

Syntax

IPmt(Rate, Period, PaymentPeriods, PresentValue, FutureValue, Type)

Arguments

Rate (required)

This is the average interest rate per period.

Period (required)

This is the specified payment period in question.

PaymentPeriods (required)

This is the total number of payment periods in the annuity.

PresentValue (required)

This is the present value of future payments.

FutureValue (optional)

This is the future value or final balance on a loan or an investment upon making the last payment. If omitted, 0 is assumed.

Type (optional)

This argument specifies when payments are due. A value of 0 means that payments are due at the end of the payment period, while a value of 1 means that payments are due at the beginning of the payment period. If omitted, 0 is assumed.

IRR is a financial function that calculates the internal rate of return based on serial cash flow, payments, and receipts.

Syntax

IRR(IncomeValues, Guess)

Arguments

IncomeValues (required)

These values make up an array that represents the periodic cash flow values. Within this array, there must be at least one negative number and one positive number.

Guess (optional)

This argument allows you to estimate the percent of total investment that will be returned. If this omitted, 10 percent is used.

IsError is an inspection function that determines if an expression evaluates as an error. This function returns a True or False answer.

Syntax

IsError(Expression)

Arguments

Expression (required)

This is any value or expression. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

IsNull is an inspection function that determines whether a value contains no valid data. This function returns a True or False answer.

Syntax

IsNull(Expression)

Arguments

Expression (required)

This is any value or expression. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

IsNumeric is an inspection function that determines whether an expression evaluates as a numeric value. This function returns a True or False answer.

Syntax

IsNumeric(Expression)

Arguments

Expression (required)

This is any value or expression. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

The LCase function converts a string to lowercase letters.

Syntax

LCase(String)

Arguments

String (required)

This is the string to be converted. In a query environment, you can use the name of a field to specify that you are converting all the row values of that field.

The Left function returns a specified number of characters starting from the left - most character of the string. For example, Left("Nowhere", 3) would return "Now".

Syntax

Left(String, NumberOfCharacters)

Arguments

String (required)

This is the string to be evaluated. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

NumberofCharacters (required)

This is the number of characters you want returned. If this argument is greater than or equal to the number of characters in string, the entire string is returned.

The Len function returns a number identifying the number of characters in a given string. This function is quite useful when you need to dynamically determine the length of a string. For instance, Len("Alexander") would return 9.

Syntax

Len(String or Variable)

Arguments

String or Variable (required)

This is the string or variable to be evaluated. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

The Log function is a math function that calculates the natural logarithm of a number.

Syntax

Log(Number)

Arguments

Number (required)

This is the numeric expression that is to be evaluated; must be greater than zero.

The Mid function returns a specified number of characters starting from a specified character position. The required Syntax for the Mid Function are: The text you are evaluating, the starting position, and the number of characters you want returned. For example: Mid("Lonely", 2, 3) captures three characters starting from character number 2 in the string, returning "one".

Syntax

Mid(String, StartPosition, NumberOfCharacters)

Arguments

String (required)

This is the string to be evaluated. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

StartPosition (required)

This is the position number of the character you want to start your capture.

NumberofCharacters (required)

This is the number of characters you want returned. If this argument is greater than or equal to the number of characters in string, the entire string is returned.

The Minute function converts a valid time to a number from 0 to 59, representing the minute of the hour. For example, Minute(#9:30:00 PM#) would return 30.

Syntax

Minute(ValidTime)

Arguments

ValidTime (required)

This is any combination of values that can represent valid time. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field

MIRR is a financial function that calculates the internal rate of return based on serial cash flow, payments, and receipts financed at different rates.

Syntax

MIRR(IncomeValues, FinanceRate, ReinvestRate)

Arguments

IncomeValues (required)

These values make up an array that represents the periodic cash flow values. Within this array, there must be at least one negative number and one positive number.

FinanceRate (required)

This is the interest rate paid as the cost of investing. The values of this argument must be represented as decimal values.

ReinvestRate (required)

This is the interest rate received on gains from cash reinvestment. The values of this argument must be represented as decimal values.

The Month function converts a valid date to a number from 1 to 12, representing the month for a given date. For example, Month(#5/16/1972#) would return 5.

Syntax

Month(ValidDate)

Arguments

ValidDate (required)

This is any value that can represent a valid date. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

The MonthName function converts a numeric month designation (1 to 12) to a month name. For instance, MonthName(8) would return August. Values less than 1 or greater than 12 will cause an error.

Syntax

MonthName(NumericMonth, Abbreviated)

Arguments

NumericMonth (required)

This is a number from 1 to 12 that represents a month. 1 represents January, 2 represents February, and so on.

Abbreviated (optional)

This specifies whether the month is abbreviated or not. If this argument is omitted, the month is not abbreviated. Enter 1 to return abbreviated months.

The Now function returns today's date and time based on your PC's current system date and time. There are no required arguments for this function; to use it, simply enter Now().

The NPer function is a financial function that specifies the number of periods for an annuity based on periodic, fixed payments at a fixed interest rate. An annuity is a series of fixed cash payments normally made against a loan over a period of time.

Syntax

NPer(Rate, PaymentAmount, PresentValue, FutureValue, Type)

Arguments

Rate (required)

This is the stated interest rate per period.

PaymentAmount (required)

This is the payment amount, usually consisting of principal and interest.

PresentValue (required)

This is the present value of future payments and receipts.

FutureValue (optional)

This is the future value or final balance on a loan or an investment upon making the last payment. If omitted, 0 is assumed.

Type (optional)

This argument specifies when payments are due. A value of 0 means that payments are due at the end of the payment period, while a value of 1 means that payments are due at the beginning of the payment period. If omitted, 0 is assumed.

NPV is a financial function that calculates the net present value or the current value of a future series of payments and receipts based on serial cash flow, payments, receipts, and a discount rate.

Syntax

NPV(DiscountRate, IncomeValues)

Arguments

DiscountRate (required)

This is the discount rate received over the length of the period. The values of this argument must be represented as decimal values.

IncomeValues (required)

These values make up an array that represents the periodic cash flow values. Within this array, there must be at least one negative number and one positive number.

The NZ function allows you to tell Access to recognize Null values as another value, preventing your null values from propagating through an expression.

Syntax

NZ(Variant, ValueIfNull)

Arguments

Variant (required)

This is the data you are working with.

ValueIfNull (required in the query environment)

This is the value you want returned if the Variant is null.

Partition is a database function that identifies the particular range in which a number falls and returns a string describing that range. This function is useful when you need to create a quick and easy frequency distribution.

Syntax

Partition(Number, Range Start, Range Stop, Interval)

Arguments

Number (required)

This is the number you are evaluating. In a query environment, you typically use the name of a field to specify that you are evaluating all the row values of that field.

Range Start (required)

This is a whole number that is to be the start of the overall range of numbers. Note that this number cannot be less than zero.

Range Stop (required)

This is a whole number that is to be the end of the overall range of numbers. Note that this number cannot be equal to or less than the Range Start.

Interval (required)

This is a whole number that is to be the span of each range in the series from Range Start to Range Stop. Note that this number cannot be less than one.

Pmt is a financial function that calculates the payment for an annuity based on periodic, fixed payments at a fixed interest rate. An annuity is a series of fixed cash payments normally made against a loan over a period of time.

Syntax

Pmt(Rate, PaymentPeriods, PresentValue, FutureValue, Type)

Arguments

Rate (required)

This is the average interest rate per period.

PaymentPeriods (required)

This is the total number of payment periods in the annuity.

PresentValue (required)

This is the present value of future payments and receipts.

FutureValue (optional)

This is the future value or final balance on a loan or an investment upon making the last payment. If omitted, 0 is assumed.

Type (optional)

This argument specifies when payments are due. A value of 0 means that payments are due at the end of the payment period, while a value of 1 means that payments are due at the beginning of the payment period. If omitted, 0 is assumed.

PPmt is a financial function that allows you to calculate the principal payment for a specified period during the life of an annuity. An annuity is a series of fixed cash payments normally made against a loan over a period of time.

Syntax

PPmt(Rate, Period, PaymentPeriods, PresentValue, FutureValue, Type)

Arguments

Rate (required)

This is the average interest rate per period.

Period (required)

This is the specified payment period in question.

PaymentPeriods (required)

This is the total number of payment periods in the annuity.

PresentValue (required)

This is the present value of future payments and receipts.

FutureValue (optional)

This is the future value or final balance on a loan or an investment upon making the last payment. If omitted, 0 is assumed.

Type (optional)

This argument specifies when payments are due. A value of 0 means that payments are due at the end of the payment period, while a value of 1 means that payments are due at the beginning of the payment period. If omitted, 0 is assumed.

PV is a financial function that allows you to calculate an annuity's present value. An annuity is a series of fixed cash payments normally made against a loan over a period of time.

Syntax

PV(Rate, PaymentPeriods, PaymentAmount, FutureValue, Type)

Arguments

Rate (required)

This is the average interest rate per period.

PaymentPeriods (required)

This is the total number of payment periods in the annuity.

PaymentAmount (required)

This is the payment amount, usually consisting of principal and interest.

FutureValue (optional)

This is the future value or final balance on a loan or an investment upon making the last payment. If omitted, 0 is assumed.

Type (optional)

This argument specifies when payments are due. A value of 0 means that payments are due at the end of the payment period, while a value of 1 means that payments are due at the beginning of the payment period. If omitted, 0 is assumed.

Rate is a financial function that allows you to calculate the interest rate per period for an annuity. An annuity is a series of fixed cash payments normally made against a loan over a period of time.

Syntax

Rate(Periods, PaymentAmount, PresentValue, FutureValue, Type, Guess)

Arguments

Periods (required)

This is the total number of payment periods in the annuity.

PaymentAmount (required)

This is the payment amount, usually consisting of principal and interest.

PresentValue (required)

This is the present value of future payments and receipts.

FutureValue (optional)

This is the future value or final balance on a loan or an investment upon making the last payment. If omitted, 0 is assumed.

Type (optional)

This argument specifies when payments are due. A value of 0 means that payments are due at the end of the payment period, while a value of 1 means that payments are due at the beginning of the payment period. If omitted, 0 is assumed.

Guess (optional)

This argument allows you to estimate the percent of total investment that will be returned. If this omitted, 10 percent is used.

Replace allows you to replace a specified substring with another substring. This function has the same effect as the "Find and Replace" functionality. For example, Replace("Pear", "P", "B") would return "Bear".

Syntax

Replace(String, Find, Replace, Start, Count, Compare)

Arguments

String (required)

The full string you are evaluating. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

Find (required)

The substring you need to find and replace.

Replace (required)

The substring used as the replacement.

Start (optional)

The position within substring to begin the search; default is 1.

Count (optional)

Number of occurrences to replace; default is all occurrences.

Compare (optional)

The kind of comparison to use.

Additional Remarks

The Compare argument can have the following values:

−1

Performs a comparison using the setting of the Option Compare statement.

0

Performs a binary comparison

1

Performs a textual comparison

2

Microsoft Access only. Performs a comparison based on information in your database.

The Right function returns a specified number of characters starting from the right most character of the string. For example, Left("Nowhere", 4) would return "here".

Syntax

Right(String, NumberOfCharacters)

Arguments

String (required)

This is the string to be evaluated. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

NumberofCharacters (required)

This is the number of characters you want returned. If this argument is greater than or equal to the number of characters in string, the entire string is returned.

Rnd is a math function that generates and returns a random number that is greater than or equal to 0 but less than 1.

Syntax

Rnd(number)

Arguments

Number (optional)

This numeric expression determines how the random number is generated.

Additional Remarks

If the Number argument is omitted from the function

The next random number in the sequence is generated.

If the Number argument is less than zero

The same number is generated every time.

If the Number argument is greater than zero

The next random number in the sequence is generated.

If the Number argument equals zero

The most recently generated number is returned.

Round is a math function that allows you to round a number to a specified number of decimal places. For example, Round(456.7276) returns 457.

Syntax

Round(Number, DecimalPlaces)

Arguments

Number (required)

This is the numeric expression you want to evaluate. In a query environment, you typically use the name of a field to specify that you are evaluating all the row values of that field.

DecimalPlaces (optional)

This is the number of places to the right of the decimal included in the rounding. If omitted, the Round function returns an integer with zero decimal places.

The Second function converts a valid time to a number from 0 to 59, representing the seconds of the minute. For example, Second(#9:00:35 PM#) would return 35.

Syntax

Second(ValidTime)

Arguments

ValidTime (required)

This is any combination of values that can represent valid time. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

Sgn is a math function that returns an integer code associated with the sign of a given number. If the given number is less than zero (has a negative designation), the Sgn function returns −1. If the given number equals zero, the Sgn function returns 0. If the given number is greater than zero (has a positive designation), the Sgn function returns 1.

Syntax

Sgn(number)

Arguments

Number (required)

This is the numeric expression you are evaluating.

Sin is a math function that allows you to calculate the sine of an angle.

Syntax

Sin(Number)

Arguments

Number (required)

This is any numeric expression that expresses an angle in radians.

SLN is a financial function that calculates the straight-line depreciation of an asset for one period.

Syntax

SLN(Cost, Salvage, Life)

Arguments

Cost (required)

This is the initial cost of the asset; must be a positive number.

Salvage (required)

This is the value of the asset at the end of its useful life; must be a positive number.

Life (required)

This is the length of the useful life of the asset.

The Space function allows you to create a string with a specified number of spaces to a string. This function comes in handy when you need to clear data in fixed-length strings. For example, you can use the Space function within an expression such as Space(5) & "Access". This would change the string "Access" to "Access".

Syntax

Space(Number)

Arguments

Number (required)

This is the number of spaces to include in the string.

SQL aggregate functions are the most commonly used functions in Access. These functions perform either mathematical calculations or value evaluations against a given expression. These functions are typically used in a query environment where the Expression argument refers to a field in a table where you are evaluating all the row values of that field.

Syntax

Sum(Expression)

Sum calculates the total value of the all the records in the designated field or grouping. This function is typically used with the following data types: AutoNumber, Currency, Date/Time, and Number.

Avg(Expression)

Avg calculates the Average of all the records in the designated field or grouping. This function is typically used with the following data types: AutoNumber, Currency, Date/Time, and Number.

Count(Expression)

Count simply counts the number of entries within the designated field or grouping. This function works with all data types.

StDev(Expression)

StDev calculates the standard deviation across all records within the designated field or grouping. This function will only work with the following data types: AutoNumber, Currency, Date/Time, and Number.

Var(Expression)

Var calculates the amount by which all the values within the designated field or grouping vary from the average value of the group. This function will only work with the following data types: AutoNumber, Currency, Date/Time, and Number.

Min(Expression)

Min returns the value of the record with the lowest value in the in the designated field or grouping. This function will only work with the following data types: AutoNumber, Currency, Date/Time, Number, and Text.

Max(Expression)

Max returns the value of the record with the highest value in the in the designated field or grouping. This function will only work with the following data types: AutoNumber, Currency, Date/Time, Number, and Text.

First(Expression)

First returns the value of the first record in the designated field or grouping. This function works with all data types.

Last(Expression)

Last returns the value of the last record in the designated field or grouping. This function works with all data types.

Sqr is a math function that calculates the square root of a given number.

Syntax

Sqr(Number)

Arguments

Number (required)

This is the numeric expression you are evaluating.

Str is a conversion function that converts a numeric value into a string representation of the number. For instance, Str(2304) would return "2304". Note that positive numbers converted with Str always have a leading space to represent the positive sign. Negative numbers have a negative sign as the leading character.

Syntax

Str(Number)

Arguments

Number (required)

This is the number you want to convert to a string. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

The StrConv function allows you to convert a string to a specified conversion setting such as uppercase, lowercase, or proper case. For example, StrConv("my text",3) would be converted to proper case, reading "My Text".

Syntax

StrConv(String, ConversionType, LCID)

Arguments

String (required)

This is the string to be converted. In a query environment, you can use the name of a field to specify that you are converting all the row values of that field.

ConversionType (required)

The conversion type specifies how to convert the string. The following constants identify the conversion type.

LCID (optional)

This is the LocaleID you want to use. The system LocaleID is the default.

The String function allows you to return a character string of a certain length. For example, String(4, "0") would return "0000".

Syntax

String(LengthOfString, StringCharacter)

Arguments

LengthOfString (required)

This is the number of times you want to repeat the StringCharacter.

StringCharacter (required)

This is the character that will make up your string. If you enter a series of characters, only the first character will be used.

The StrReverse function returns an expression in reverse order. For instance, StrReverse("ten") returns "net". This works with numbers too; StrReverse(5432) returns 2345.

Syntax

StrReverse(Expression)

Arguments

Expression (required)

This is the expression that contains the characters you want reversed.

Switch is a program flow function that allows you to evaluate a list of expressions and return the value associated with the expression determined to be true. To use the Switch function, you must provide a minimum of one expression and one value.

Syntax

Switch(Expression, Value)

Arguments

Expression (required)

This is the expression you want to evaluate.

Value (required)

This is the value to return if the expression is true.

Additional Remarks

To evaluate multiple expressions, simply add another Expression and Value to the function. For example: Switch(Expression1, Value 1, Expression2, Value2, Expression3, Value3).

When the Switch function is executed, each expression is evaluated. If an expression evaluates to true, the value that follows that expression is returned. If more than one expression is true, the value for the first true expression is returned.

SYD is a financial function that calculates the sum-of-years' digits depreciation of an asset for a specified period.

Syntax

SYD(Cost, Salvage, Life, Period)

Arguments

Cost (required)

This is the initial cost of the asset; must be a positive number.

Salvage (required)

This is the value of the asset at the end of its useful life; must be a positive number.

Life (required)

This is the length of the useful life of the asset.

Period (required)

This is the period for which asset depreciation is calculated.

Tan is a math function that allows you to calculate the tangent of an angle.

Syntax

Tan(number)

Arguments

Number (required)

This is any numeric expression that expresses an angle in radians.

The Time function returns today's time based on your PC's current system time. This function is ideal for time stamping transactions. There are no required arguments for this function; to use it, simply enter: Time().

The TimeSerial function essentially builds a time value based on the given hour, minute, and second components. Keep in mind that this function works on a 24-hour clock, so the expression TimeSerial(18,30,0) would return 6:30:00 PM. This function is perfect for converting disparate strings that represent a time when combined, into an actual time.

Syntax

TimeSerial(Hour, Minute, Second)

Arguments

Hour (required)

This is any number or numeric expression that has a value between 0 and 23, inclusive. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field; this is true for all the Syntax in this function.

Minute (required)

This is any number or numeric expression. If the number specified for this argument exceeds the normal range for minutes in an hour, the function increments the hour as appropriate. For instance, TimeSerial(7,90,00) would return 8:30:00 AM.

Second (required)

This is any number or numeric expression. If the number specified for this argument exceeds the normal range for seconds in a minute, the function increments the minutes as appropriate. For instance, TimeSerial(7,10, 75) would return 7:11:15 AM.

The TimeValue function converts a string representation of a time to an actual time value. For instance, TimeValue("4:20:37 PM") would return 4:20:37 PM. The function also works on a 24-hour clock.

Syntax

TimeValue(String)

Arguments

String (required)

This is any string or expression that represents a time ranging from 0:00:00 and 23:59:59. The string can be either a 12-hour clock entry, or a 24-hour clock entry. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

The Trim function effectively removes both the leading and trailing spaces from a string. The LTrim function removes only the leading spaces, while the RTrim function removes only the trailing spaces. These functions come in handy when cleaning up data received from a mainframe source.

Syntax

Trim(String)LTrim(String)RTrim(String)

Arguments

String (required)

This is the string you are working with. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

TypeName is an inspection function that returns the type information of a variable. For instance, TypeName("Michael") would return "String".

Syntax

TypeName(Variable)

Arguments

Variable (required)

This is the variable you want to evaluate. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

Additional Remarks

The string returned by the TypeName function can be any one of the following:

Object type

An object whose type is objecttype

Byte

A byte value

Integer

An Integer type

Long

A long integer type

Single

A single-precision floating-point number

Double

A double-precision floating-point number

Currency

A currency value

Decimal

A decimal value

Date

A date value

String

A string type

Boolean

A boolean value

Error

An error value

Empty

Variable has not been initialized

Null

Variable contains no valid data; a Null value

Object

An object

Unknown

An object whose type is unknown

Nothing

An object variable that does not refer to an object

The UCase function converts a string to uppercase letters.

Syntax

UCase(String)

Arguments

String (required)

This is the string to be converted. In a query environment, you can use the name of a field to specify that you are converting all the row values of that field.

Val is a conversion function that extracts the numeric part of a string. For instance, Val("5400 Legacy Drive") would return 5400. One caveat to the Val function is that it stops reading the string as soon as it hits a textual character. Therefore, the number you are extracting needs to be at the beginning of the string.

Syntax

Val(String)

Arguments

String (required)

This is the string you want to evaluate. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

VarType is an inspection function that returns the subtype code associated with a variant's character type. For instance, VarType("Michael") would return 8 because this is the subtype code for a string.

Syntax

VarType(Variant)

Arguments

Variant (required)

This is the variant you want to evaluate. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

Additional Remarks

The following is a list of the subtype codes that the VarType function can return.

0

Empty (uninitialized)

1

Null (no valid data)

2

Integer

3

Long integer

4

Single-precision floating-point number

5

Double-precision floating-point number

6

Currency value

7

Date value

8

String

9

Object

10

Error value

11

Boolean value

12

Variant (used only with arrays of variants)

13

Data access object

14

Decimal value

17

Byte value

36

Variants that contain user-defined types

8192

Array

The Weekday function returns a number from 1 to 7 representing the day of the week for a given date. 1 represents Sunday, 2 represents Monday, and so on. For example, Weekday (#12/31/1997#) will return 4.

Syntax

Weekday(ValidDate, FirstDayOfTheWeek)

Arguments

ValidDate (required)

This is any value that can represent a valid date. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.

FirstDayOfTheWeek (optional)

This specifies which day you want to count as the first day of the week. Enter 1 in this argument to make the first day Sunday, 2 for Monday, 3 for Tuesday, and so on. If this argument is omitted, the first day is a Sunday by default.

The WeekdayName function converts a numeric weekday designation (1 to 7) to a weekday name. For instance, WeekdayName(7) would return Saturday. Values less than 1 or greater than 7 will cause an error.

Syntax

WeekdayName(WeekdayNumber, Abbreviated, FirstDayOfTheWeek)

Arguments

WeekdayNumber (required)

This is a number from 1 to 7 that represents a weekday. 1 represents Sunday, 2 represents Monday, and so on.

Abbreviated (optional)

This specifies whether the weekday is abbreviated or not. If this argument is omitted, the weekday is not abbreviated. Enter 1 for this argument to return abbreviated weekdays.

FirstDayOfTheWeek (optional)

This specifies which day you want to count as the first day of the week. Enter 1 in this argument to make the first day Sunday, 2 for Monday, 3 for Tuesday, and so on. If this argument is omitted, the first day is a Sunday by default.

The Year function returns a whole number representing the year for a given date. For example, Year(#5/16/1972#) would return 1972.

Syntax

Year(ValidDate)

Arguments

ValidDate (required)

This is any value that can represent a valid date. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.