Access level three
1 / 41

Access Level Three - PowerPoint PPT Presentation

  • Uploaded on

Access Level Three. [Functions] & “ And “ & [Expressions]. Parentheses. Arguments. Arguments. Arguments. DateDiff ( interval, date1, date2). Keyword. Numbers For Arguments. The Int function returns the integer portion of a number. The syntax for the Int function is: Int ( arguments )

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Access Level Three' - richelle-lucas

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Access level three

Access Level Three

[Functions] & “ And “ & [Expressions]

Access level three





DateDiff ( interval, date1, date2)


Numbers for arguments
Numbers For Arguments

The Int function returns the integer portion of a number.

The syntax for the Int function is:

Int ( arguments )

Int (210.67) would return 210

Int (2.98) would return 2

Int (-2.98) would return -3

The Round function returns a number rounded to a specified number of decimal places.

Round (12.55, 1) would return 12.6 (rounds up)

Round (12.65, 1) would return 12.6 (rounds down)

Round (12.75, 1) would return 12.8 (rounds up)

Text for arguments
Text For Arguments

The Len function returns the length of the specified string.

Len ("techonthenet") would return 12.

Len ("Wednesday") would return 9.

Len ("apple pie") would return 9.

The Asc function returns the NUMBER code that

represents the specific character.

Asc ("W") would return 87.

Asc ("Wednesday") would return 87.

Asc ("x") would return 120.

Numbers and text for arguments
Numbers and Text For Arguments

  • The DateAdd function returns a date after

  • which a certain time/date interval has been added.

  • DateAdd ('yyyy', 3, #11/22/2003#)

  • would return ’11/22/2006‘

  • DateAdd ('q', 2, #11/22/2003#)

  • would return ’05/22/2004‘

  • DateAdd ('m', 5, #11/22/2003#)

  • would return ’04/22/2004‘

  • DateAdd('yyyy', -1, #11/22/2003#)

  • would return ’11/22/2002'

Field names as arguments
Field Names As Arguments

Int ([Salary] + [RaiseAmount])


Iff ([Salary] + [RaiseAmount]< [Salary Cap], [Salary] + [RaiseAmount], [Salary Cap])

Functions as arguments


Functions As Arguments

(Nested Functions)

Int (Iff ([Salary] * .5 > 100000, [Salary] * .25, [Salary] * .35))

Function As Argument

The datediff function
The DateDiff Function

  • The DateDiff function returns the difference between two date values, based on the interval that you specify.

  • The syntax for the DateDiff function is:

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

  • interval is the interval of time to use to calculate the difference between date1 and date2.(see next slide)

  • date1 and date2 are the two dates to calculate the difference between.

Valid intervals for datediff
Valid Intervals for DateDiff

All values must be enclosed in quotes in the function.

The iff function
The IFF Function

  • The IIF function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

  • The syntax for the iif function is:

    • iif ( condition, value_if_true, value_if_false )

  • condition is the value that you want to test.

  • value_if_true is the value that is returned if condition evaluates to TRUE.

  • value_if_false is the value that is return if condition evaluates to FALSE.

The iff function1
The IFF Function

The Function…

…would return…

"large" if the value in the Qty field is greater than 10

“small” if the value is 10 or less

The iff function nested iff
The IFF Function – Nested IFF

This function would return…

Large if Qty is 101 or greater

Medium Qty is between 51 and 100

Small if Qty is 50 or less

The int function
The INT Function

  • The Int function returns the integer portion of a number.

  • The syntax for the Int function is:

  • Int ( expression )

  • expression is a numeric expression whose integer portion is returned.

The int function1
The INT Function

  • Examples

  • Int (210.67) would return 210

  • Int (2.98) would return 2

  • Int (-2.98) would return -3

  • Note: If the expression is negative, the Int function will return the first negative number that is less than or equal to the expression.

The sqr function
The SQR Function

  • The Sqr() function is used to calculate the square root of a double-precision number.

  • Its syntax is:

  • Sqr(number)

  • This function takes one argument and it must be a number. After the calculation, the function returns the square root of the number.

The sqr function1
The SQR Function


sqr(100) returns 10

sqr(25) returns 25

sqr(55) returns ?

sqr([total]) returns the square root of the value being stored in the field named total

The len function
The LEN Function

  • The Len function returns the length of the specified string.

  • The syntax for the Len function is:

  • Len ( text )

  • text is the string to return the length for.

The len function1
The LEN Function


Len ("techonthenet") would return 12

Len ("Wednesday") would return 9

Len ("apple pie") would return 9

Len (“”) would return 0

Note: the text string must be enclosed in double quotes, especially if it contains a space or any special characters.

The operator
The & Operator

Used to concatenate multiple strings into a single string

string_1 &  string_2 &  string_n


"new" & "ark“

would return "newark“

"Tech on the" & " Net!“

would return "Tech on the Net!“

"This " & "is " & "a test“

would return "This is a test"

Text must be enclosed in double quotes. You can concatenate field names text strings or expressions

Double declining balance ddb
Double Declining Balance (DDB)

Used to calculate the depreciating value of an asset

The syntax is…

DDB(cost, salvage, life, period)

DDB($15000, $1200, 4, 3)

DDB([OriginalPrice], [EOL-Value], [HowLongIWillKeepIt], [Now - PurDate]

Double declining balance ddb1
Double Declining Balance (DDB)

DDB(cost, salvage, life, period)

Cost = How much you paid for it

Salvage = How much it is worth at the end of its useful life

Life = How long the item will last, the useful lifetime of the item

Period = The year* for which you want to calculate depreciation for a given item.

* If an item has a life of 5 years, you can calculate depreciation for the first year (1), second year (2), third year (3), etc.

Access level three

Age: IIf(DateDiff("yyyy",[PurchaseDate], Date()) > [Lifetime], "Too Old", DateDiff("yyyy", [PurchaseDate], Date()))

Access level three

CurrYearDepreciation: IIf([Age]="Too Old", 0, DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

The pmt function
The PMT Function DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

  • The PMT function calculates the total monthly (or other period) payment (principal + interest) for any given loan amount

  • The syntax for the PMT function is:

  • Pmt(Rate, NPeriods, PresentValue, FutureValue, PaymentType)

The pmt function1
The PMT Function DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

  • Rate is the interest rate per period

    • 5% annual interest rate = 5%/12 for monthly payment

    • 5% annual interest rate = 5%/6 for bi-monthly payments

    • 5% annual interest rate = 5%/2 for semi-annual payments

  • Nper is the number of periods

    • For a 10 year loan…

      • The Nper is 120 if you are making monthly payments on the loan

      • The Nper is 10 if you are making annual payments

The pmt function2
The PMT Function DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

  • PV is present value, the amount of the loan (after down payment)

    • If you are borrowing money, this value should be negative

    • If you are collecting money (you loan money to another person) this value should be positive

  • FV is future value, the balance after the last payment

    • This argument is optional. If it left blank Access assumes 0.

  • Type or Due determines when payment is due.

    • 0 = End of the Month

    • 1 = Beginning of the Month

    • Argument is optional

The pmt function3
The PMT Function DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

  • Examples

    • Pmt(12.5%, 28, $1450, 0, 1) = -$59.97

    • Pmt(12.5%, 28, -$1450, 0, 1) = $59.97

    • Pmt([InterestRate], [LengthOfLoan [LoanAmount])

    • Pmt([InterestRate]/12, [LengthOfLoan], [LoanAmount])

The future value of an investment
The Future Value of an Investment DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

To calculate the future value of an investment, use the FV() function.

The syntax of this function is:

FV(Rate, Periods, Payment, PresentValue, PaymentType)

PaymentType is optional

The number of periods of an investment
The Number of Periods of an Investment DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

To calculate the number of periods of an investment or a loan, use the NPer() function.

The syntax of this function is:

NPer(Rate, Payment, PresentValue, FutureValue, PaymentType);

Amount paid as interest during a period
Amount Paid As Interest During a Period DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

The IPmt() function is used to calculate the amount paid as interest on a loan during a period of the lifetime of a loan or an investment.

The syntax of this function is:

IPmt(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

The amount paid as principal
The Amount Paid as Principal DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

The PPmt() function calculates the actual amount that applies to the balance of the loan.

The syntax of this function is:

PPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

Present value of loan or investment
Present Value of Loan or Investment DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

The PV() function calculates the total amount that future investments are worth currently.

The syntax of this function is:

PV(Rate, NPeriods, Payment, FutureValue, PaymentType)

Understanding period nper and rate
Understanding Period, Nper, and Rate DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

Math and trig functions
Math and Trig Functions DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

  • Abs(number)

  • Int(number)

  • Sgn(number)

  • Sqr(number)

  • Atn(number)

  • Log(number)

  • Sin(angle)

  • Cos(angle)

  • Tan(angle)

Informational functions
Informational Functions DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

  • IsNull

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

    • Syntax

      • IsNull(expression)

  • Nz

    • return a value when a variant is null.

    • The syntax for the Nz function is:

      • Nz ( variant, [ value_if_null ] )

      • Nz (varName, "n/a") - would return the value 'n/a' if the variable varName contained a null value.

Informational functions1
Informational Functions DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

  • IsError

    • Returns a Boolean value indicating whether an expression is an error value.

    • Syntax

      • IsError(expression)

  • Typename

    • Returns a String that provides information about a variable.

    • Syntax

      • TypeName(varname)

Informational functions2
Informational Functions DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

  • IsNumeric

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

    • Syntax

      • IsNumeric(expression)

Strings returned by typename
Strings Returned By Typename DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

The replace function
The Replace Function DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

The Replace function replaces a sequence of characters in a string with another set of characters (a number of times).

The syntax for the Replace function is:

Replace ( string1, find, replacement, [start, [count, [compare]]] )

The start, count, and compare arguments are optional

The replace function1
The Replace Function DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))

  • Examples:

  • Replace("alphabet", "bet", "hydro") would return "alphahydro"

  • Replace ("alphabet", "a", "e") would return "elphebet“

  • Replace("alphabet", "a", "e", 2) would return "lphebet"

  • Replace("alphabet", "a", "e", 1, 1) would return "elphabet"

The start, count, and compare arguments are optional

Functions you should know

Date DDB([PurchaseValue], [SalvageValue],[Lifetime],[Age]))√









FV √















Functions You Should Know



  • AVG √

  • COUNT √

  • MAX √

  • MIN √


  • VARP

  • CHAR


  • LEFT

  • LEN √


  • MID



  • TRIM


  • TRIM

  • INT √

  • LOG √

  • RAND √

  • SUM √

  • TAN √

  • ISNULL √

  • ISNA



  • NZ √

  • TYPE √


√ = covered in this workshop