Examples of expressions : Ms Access

Examples of expressions

Applies To: Access 2007

This article provides examples of expressions. An expression is a combination of mathematical or logical operators, constants, functions, table fields, controls, and properties that evaluates to a single value. You can use expressions to calculate values, validate data, and set a default value for a field or control.

Note: Although this article provides basic steps for creating expressions, it is not a comprehensive guide for using the tools that Microsoft Office Access 2007 provides for creating expressions. For more information about creating expressions, see the article Create an expression.

In this article

Understand expressions

Examples of expressions used in forms and reports

Examples of expressions used in queries and filters

Examples of default value expressions

Examples of field validation rule expressions

Examples of macro condition expressions

Understand expressions

In Office Access 2007, the term expression is synonymous with formula. An expression consists of a number of possible elements that you can use, alone or in combination, to produce a result. Those elements include:

• Identifiers — the names of table fields or controls on forms or reports, or the properties of those fields or controls
  
• Operators, such as + (plus) or - (minus)
  
• Functions, such as SUM or AVG
  
• Constants  — values that do not change — such as strings of text, or numbers that are not calculated by an expression.
  

You can use expressions in a number of ways — to perform a calculation, retrieve the value of a control, or supply criteria to a query, just to name a few.
For more information about how and where to use expressions, see the article Create an expression.
Top of Page

Examples of expressions used in forms and reports

The tables in this section provide examples of expressions that calculate a value in a control located on a form or report. To create a calculated control, you enter an expression in the ControlSource property of the control, instead of in a table field or query.
The following steps explain how to enter an expression in a text box control on an existing form or report.

Create a calculated control

1. In the Navigation Pane, right-click the form or report that you want to change and click Design View on the shortcut menu.
   
2. On the form or report, right-click the text box control that you want to change (not the label associated with the text box), and then click Properties on the shortcut menu.
   
3. If necessary, click either the All tab or the Data tab. Both tabs provide the Control Source property.
   
4. Click the box next to the Control Source property and type your expression. For example, you can copy and paste an expression from the Expression column in the table in the following section.
   
5. Close the property sheet.
   

Expressions that combine or manipulate text

The expressions in the following table use the & (ampersand) and the + (plus) operators to combine text strings, built-in functions to manipulate a text string, or otherwise operate on text to create a calculated control.

Expression	Result
="N/A"	Displays N/A.
=[FirstName] & " " & [LastName]	Displays the values that reside in table fields called FirstName and LastName. In this example, the & operator is used to combine the FirstName field, a space character (enclosed in quotation marks), and the LastName field.
=Left([ProductName], 1)	Uses the Left function to display the first character of the value of a field or control called ProductName.
=Right([AssetCode], 2)	Uses the Right function to display the last 2 characters of the value in a field or control called AssetCode.
=Trim([Address])	Uses the Trim function to display the value of the Address control, removing any leading or trailing spaces.
=IIf(IsNull([Region]), [City] & " " & [PostalCode], [City] & " " & [Region] & " " & [PostalCode])	Uses the IIf function to display the values of the City and PostalCode controls if the value in the Region control is null; otherwise, it displays the values of the City, Region, and PostalCode controls, separated by spaces.
=[City] & (" " + [Region]) & " " & [PostalCode]	Uses the + operator and null propagation to display the values of the City and PostalCode controls if the value in the Region field or control is null; otherwise, it displays the values of the City, Region, and PostalCode fields or controls, separated by spaces. Null propagation means that if any component of an expression is null, the entire expression is also null. The + operator supports null propagation; the & operator does not.

Expressions in headers and footers

You use the Page and the Pages properties to display or print page numbers in forms or reports. The Page and Pages properties are available only during printing or print preview, so they do not appear on the property sheet for the form or report. Typically, you use these properties by placing a text box in the header or footer section of the form or report, and then using an expression, such as the ones shown in the following table.
For more information about using headers and footers in forms and reports, see the article Insert page numbers into a form or report.

Expression	Example result
=[Page]	1
="Page " & [Page]	Page 1
="Page " & [Page] & " of " & [Pages]	Page 1 of 3
=[Page] & " of " & [Pages] & " Pages"	1 of 3 Pages
=[Page] & "/" & [Pages] & " Pages"	1/3 Pages
=[Country/region] & " - " & [Page]	UK - 1
=Format([Page], "000")	001
="Printed on: " & Date()	Printed on: 12/31/07

Expressions that perform arithmetic operations

You can use expressions to add, subtract, multiply, and divide the values in two or more fields or controls. You can also use expressions to perform arithmetic operations on dates. For example, suppose you have a Date/Time table field named RequiredDate. In the field, or in a control bound to the field, the expression =[RequiredDate] - 2 returns a date/time value equal to two days before the current values in the RequiredDate field.

Expression	Result
=[Subtotal]+[Freight]	The sum of the values of the Subtotal and Freight fields or controls.
=[RequiredDate]-[ShippedDate]	The interval between the date values of the RequiredDate and ShippedDate fields or controls.
=[Price]*1.06	The product of the value of the Price field or control and 1.06 (adds 6 percent to the Price value).
=[Quantity]*[Price]	The product of the values of the Quantity and Price fields or controls.
=[EmployeeTotal]/[CountryRegionTotal]	The quotient of the values of the EmployeeTotal and CountryRegionTotal fields or controls.

Note: When you use an arithmetic operator (+, -, *, and /) in an expression, and the value of one of the controls in the expression is null, the result of the entire expression will be null — this is known as Null propagation. If any records in one of the controls that you use in the expression might have a null value, you can avoid Null propagation by converting the null value to zero by using the Nz function — for example, =Nz([Subtotal])+Nz([Freight]).

For more information about the function, see the article Nz Function.

Expressions that refer to values in other fields or controls

Sometimes, you need a value that exists somewhere else, such as in a field or control on another form or report. You can use an expression to return the value from another field or control.
The following table lists examples of expressions that you can use in calculated controls on forms.

Expression	Result
=Forms![Orders]![OrderID]	The value of the OrderID control on the Orders form.
=Forms![Orders]![Orders Subform].Form![OrderSubtotal]	The value of the OrderSubtotal control on the subform named Orders Subform on the Orders form.
=Forms![Orders]![Orders Subform]![ProductID].Column(2)	The value of the third column in ProductID, a multiple-column list box on the subform named Orders Subform on the Orders form. (Note that 0 refers to the first column, 1 refers to the second column, and so on.)
=Forms![Orders]![Orders Subform]![Price] * 1.06	The product of the value of the Price control on the subform named Orders Subform on the Orders form and 1.06 (adds 6 percent to the value of the Price control).
=Parent![OrderID]	The value of the OrderID control on the main or parent form of the current subform.

The expressions in the following table show some ways to use calculated controls on reports. The expressions reference the Report property.
For more information about that property, see the article Report Property.

Expression	Result
=Report![Invoice]![OrderID]	The value of a control called "OrderID" in a report called "Invoice."
=Report![Summary]![Summary Subreport]![SalesTotal]	The value of the SalesTotal control on the subreport named Summary Subreport on the Summary report.
=Parent![OrderID]	The value of the OrderID control on the main or parent report of the current subreport.

Expressions that count, sum, and average values

You can use a type of function called an aggregate function to calculate values for one or more fields or controls. For example, you can calculate a group total for the group footer in a report, or an order subtotal for line items on a form. You can also count the number of items in one or more fields or calculate an average value.
The expressions in the following table show some of the ways to use functions such as Avg, Count, and Sum.

Expression	Description
=Avg([Freight])	Uses the Avg function to display the average of the values of a table field or control named "Freight."
=Count([OrderID])	Uses the Count function to display the number of records in the OrderID control.
=Sum([Sales])	Uses the Sum function to display the sum of the values of the Sales control.
=Sum([Quantity]*[Price])	Uses the Sum function to display the sum of the product of the values of the Quantity and the Price controls.
=[Sales]/Sum([Sales])*100	Displays the percentage of sales, determined by dividing the value of the Sales control by the sum of all the values of the Sales control. Note: If you set the Format property of the control to Percent, do not include *100 in the expression.

For more information about using aggregate functions and totaling the values in field and columns, see the articles Sum data by using a query, Count data by using a query, Count the rows in a datasheet, and Display column totals in a datasheet.

Expressions that count, sum, and look up values selectively by using domain aggregate functions

You use a type of function called a domain aggregate function when you need to sum or count values selectively. A "domain" consists of one or more fields in one or more tables, or one or more controls on one or more forms or reports. For example, you can match the values in a table field with the values in a control on a form.

Expression	Description
=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = " & Forms("Suppliers")("[SupplierID]"))	Uses the DLookup function to return the value of the ContactName field in the Suppliers table where the value of the SupplierID field in the table matches the value of the SupplierID control on the Suppliers form.
=DLookup("[ContactName]", "[Suppliers]", "[SupplierID] = " & Forms![New Suppliers]![SupplierID])	Uses the DLookup function to return the value of the ContactName field in the Suppliers table where the value of the SupplierID field in the table matches the value of the SupplierID control on the New Suppliers form.
=DSum("[OrderAmount]", "[Orders]", "[CustomerID] = 'RATTC'")	Uses the DSum function to return the sum total of the values in the OrderAmount field in the Orders table where the CustomerID is RATTC.
=DCount("[Retired]","[Assets]","[Retired]=Yes")	Uses the DCount function to return the number of Yes values in the Retired field (a Yes/No field) in the Assets table.

Expressions that manipulate and calculate dates

Tracking dates and times is a fundamental database activity. For example, you can calculate how many days have elapsed since the invoice date to age your accounts receivable. You can format dates and times in numerous ways, as shown in the following table.

Expression	Description
=Date()	Uses the Date function to display the current date in the form of mm-dd-yy, where mm is the month (1 through 12), dd is the day (1 through 31), and yy is the last two digits of the year (1980 through 2099).
=Format(Now(), "ww")	Uses the Format function to display the week number of the year for the current date, where ww represents weeks 1 through 53.
=DatePart("yyyy", [OrderDate])	Uses the DatePart function to display the four-digit year of the value of the OrderDate control.
=DateAdd("y", -10, [PromisedDate])	Uses the DateAdd function to display a date that is 10 days before the value of the PromisedDate control.
=DateDiff("d", [OrderDate], [ShippedDate])	Uses the DateDiff function to display the number of days' difference between the values of the OrderDate and ShippedDate controls.
=[InvoiceDate] + 30	Uses arithmetic operations on dates to calculate the date 30 days after the date in the InvoiceDate field or control.

Conditional expressions that return one of two possible values

The example expressions in the following table use the IIf function to return one of two possible values. You pass the IIf function three arguments: The first argument is an expression that must return a True or False value. The second argument is the value to return if the expression is true, and the third argument is the value to return if the expression is false.

Expression	Description
=IIf([Confirmed] = "Yes", "Order Confirmed", "Order Not Confirmed")	Uses the IIf (Immediate If) function to display the message "Order Confirmed" if the value of the Confirmed control is Yes; otherwise, it displays the message "Order Not Confirmed."
=IIf(IsNull([Country/region]), " ", [Country])	Uses the IIf and IsNull functions to display an empty string if the value of the Country/region control is null; otherwise, it displays the value of the Country/region control.
=IIf(IsNull([Region]), [City] & " " & [PostalCode], [City] & " " & [Region] & " " & [PostalCode])	Uses the IIf and IsNull functions to display the values of the City and PostalCode controls if the value in the Region control is null; otherwise, it displays the values of the City, Region, and PostalCode fields or controls.
=IIf(IsNull([RequiredDate]) Or IsNull([ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate])	Uses the IIf and IsNull functions to display the message "Check for a missing date" if the result of subtracting ShippedDate from RequiredDate is null; otherwise, it displays the interval between the date values of the RequiredDate and ShippedDate controls.

Top of Page

Examples of expressions used in queries and filters

This section contains examples of expressions that you can use to create a calculated field in a query or to supply criteria to a query. A calculated field is a column in a query that results from an expression. For example, you can calculate a value, combine text values such as first and last names, or format a portion of a date.
You use criteria in a query to limit the records that you work with. For example, you can use the Between operator to supply a starting and ending date and limit the results of your query to orders that were shipped between those dates.
The following sections explain how to add a calculated field to a query and provide examples of expressions for use in queries.

Add a calculated field in query Design view

1. In the Navigation Pane, right-click the query that you want to change and click Design View on the shortcut menu.
   
2. Click the Field cell in the column where you want to create the calculated field. You can enter a name for the field followed by a colon, or you can type your expression. If you do not enter a name, Access adds Exprn:, where n is a sequential number.
   
3. Type your expression.
   -or-
   On the Design tab, in the Query Setup group, click Builder to start the Expression Builder.
   For more information about using the Expression Builder, see the article Create an expression.
   

Expressions that manipulate text in a query or filter

The expressions in the following table use the & and + operators to combine text strings, use built-in functions to operate on a text string, or otherwise operate on text to create a calculated field.

Expression	Description
FullName: [FirstName] & " " & [LastName]	Creates a field called FullName that displays the values in the FirstName and LastName fields, separated by a space.
Address2: [City] & " " & [Region] & " " & [PostalCode]	Creates a field called Address2 that displays the values in the City, Region, and PostalCode fields, separated by spaces.
ProductInitial:Left([ProductName], 1)	Creates a field called ProductInitial, and then uses the Left function to display, in the ProductInitial field, the first character of the value in the ProductName field.
TypeCode: Right([AssetCode], 2)	Creates a field called TypeCode, and then uses the Right function to display the last two characters of the values in the AssetCode field.
AreaCode: Mid([Phone],2,3)	Creates a field called AreaCode, and then uses the Mid function to display the three characters starting with the second character of the value in the Phone field.

Expressions that perform arithmetic operations in calculated fields

You can use expressions to add, subtract, multiply, and divide the values in two or more fields or controls. You can also perform arithmetic operations on dates. For example, suppose you have a Date/Time field called RequiredDate. The expression =[RequiredDate] - 2 returns a Date/Time value equal to two days before the value in the RequiredDate field.

Expression	Description
PrimeFreight: [Freight] * 1.1	Creates a field called PrimeFreight, and then displays freight charges plus 10 percent in the field.
OrderAmount: [Quantity] * [UnitPrice]	Creates a field called OrderAmount, and then displays the product of the values in the Quantity and UnitPrice fields.
LeadTime: [RequiredDate] - [ShippedDate]	Creates a field called LeadTime, and then displays the difference between the values in the RequiredDate and ShippedDate fields.
TotalStock: [UnitsInStock]+[UnitsOnOrder]	Creates a field called TotalStock, and then displays the sum of the values in the UnitsInStock and UnitsOnOrder fields.
FreightPercentage: Sum([Freight])/Sum([Subtotal]) *100	Creates a field called FreightPercentage, and then displays the percentage of freight charges in each subtotal. This expression uses the Sum function to total the values in the Freight field, and then divides those totals by the sum of the values in the Subtotal field. To use this expression, you must convert your select query into a Totals query because you need to use the Total row in the design grid, and you must set the Total cell for this field to Expression. For more information about creating a Totals query, see the article Sum data by using a query. If you set the Format property of the field to Percent, do not include *100.

For more information about using aggregate functions and totaling the values in field and columns, see the articles Sum data by using a query, Count data by using a query, Count the rows in a datasheet, and Display column totals in a datasheet.

Expressions that manipulate and calculate with dates in calculated fields

Nearly all databases store and track dates and times. You work with dates and times in Access by setting the date and time fields in your tables to the Date/Time data type. Access can perform arithmetic calculations on dates; for example, you can calculate how many days have elapsed since the invoice date to age your accounts receivable.

Expression	Description
LagTime: DateDiff("d", [OrderDate], [ShippedDate])	Creates a field called LagTime, and then uses the DateDiff function to display the number of days between the order date and ship date.
YearHired: DatePart("yyyy",[HireDate])	Creates a field called YearHired, and then uses the DatePart function to display the year each employee was hired.
MinusThirty: Date( )- 30	Creates a field called MinusThirty, and then uses the Date function to display the date 30 days prior to the current date.

Expressions that count, sum, and average values by using SQL aggregate or domain aggregate functions

The expressions in the following table use SQL (Structured Query Language) functions that aggregate or summarize data. You often see these functions (for example, Sum, Count, and Avg) referred to as aggregate functions.
In addition to aggregate functions, Access also provides "domain" aggregate functions that you use to sum or count values selectively. For example, you can count only the values within a certain range or look up a value from another table. The set of domain aggregate functions includes the DSum Function, the DCount Function, and the DAvg Function.
To calculate totals, you will often need to create a totals query. For example, to summarize by group, you need to use a Totals query. To enable a Totals query from the query design grid, click Totals on the View menu.

Expression	Description
RowCount:Count(*)	Creates a field called RowCount, and then uses the Count function to count the number of records in the query, including records with null (blank) fields.
FreightPercentage: Sum([Freight])/Sum([Subtotal]) *100	Creates a field called FreightPercentage, and then calculates the percentage of freight charges in each subtotal by dividing the sum of the values in the Freight field by the sum of the values in the Subtotal field. (This example uses the Sum function.) Note: You must use this expression with a Totals query. If you set the Format property of the field to Percent, do not include *100. . For more information about creating a Totals query, see the article Sum data by using a query.
AverageFreight: DAvg("[Freight]", "[Orders]")	Creates a field called AverageFreight, and then uses the DAvg function to calculate the average freight on all orders combined in a Totals query.

Expressions for working with fields with missing information (fields with null values)

The expressions shown here work with fields with potentially missing information, such as those containing null (unknown or undefined) values. You frequently encounter null values, such as an unknown price for a new product or a value that a coworker forgot to add to an order. The ability to find and process null values can be a critical part of database operations, and the expressions in the following table demonstrate some common ways to deal with null values.

Expression	Description
CurrentCountryRegion:IIf(IsNull([CountryRegion]), " ", [CountryRegion])	Creates a field called CurrentCountryRegion, and then uses the IIf and IsNull functions to display an empty string in that field when the CountryRegion field contains a null value; otherwise, it displays the contents of the CountryRegion field.
LeadTime: IIf(IsNull([RequiredDate] - [ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate])	Creates a field called LeadTime, and then uses the IIf and IsNull functions to display the message "Check for a missing date" if the value in either the RequiredDate field or the ShippedDate field is null; otherwise, it displays the date difference.
SixMonthSales: Nz([Qtr1Sales]) + Nz([Qtr2Sales])	Creates a field called SixMonthSales, and then displays the total of the values in the Qtr1Sales and Qtr2Sales fields by first using the Nz function to convert any null values to zero.

Expression that uses a subquery to create a calculated field

You can use a nested query, also called a subquery, to create a calculated field. The expression in the following table is one example of a calculated field that results from a subquery.

Expression	Description
Cat: (SELECT [CategoryName] FROM [Categories] WHERE [Products].[CategoryID]=[Categories].[CategoryID])	Creates a field called Cat, and then displays the CategoryName, if the CategoryID from the Categories table is the same as the CategoryID from the Products table.

Expressions that define criteria and limit the records in the result set

You can use an expression to define criteria for a query. Access then returns only those rows that match the criteria. The steps in this section provide basic information about adding criteria to a query, and the tables in this section provide examples of criteria for matching text and date values.

Add criteria to a query

1. In the Navigation Pane, right-click the query that you want to change and click Design View on the shortcut menu.
   
2. In the Criteria row of the design grid, click the cell in the column that you want to use, and then type your criteria.
   If you want a larger area in which to type an expression, press SHIFT+F2 to display the Zoom box.
   -or-
   On the Design tab, in the Query Setup group, click Builder to start the Expression Builder and create your expression.
   

Note: When you create expressions that define criteria, do not precede the expressions with the = operator.

For more information about using the Expression Builder, see the article Create an expression.

Expressions that match whole or partial text values

The sample expressions in this table demonstrate criteria that match whole or partial text values.

Field	Expression	Description
ShipCity	"London"	Displays orders shipped to London.
ShipCity	"London" Or "Hedge End"	Uses the Or operator to display orders shipped to London or Hedge End.
ShipCountryRegion	In("Canada", "UK")	Uses the In operator to display orders shipped to Canada or the UK.
ShipCountryRegion	Not "USA"	Uses the Not operator to display orders shipped to countries/regions other than USA.
ProductName	Not Like "C*"	Uses the Not operator and the * wildcard character to display products whose names do not begin with C.
CompanyName	>="N"	Displays orders shipped to companies whose names start with the letters N through Z.
ProductCode	Right([ProductCode], 2)="99"	Uses the Right function to display orders with ProductCode values that end in 99.
ShipName	Like "S*"	Displays orders shipped to customers whose names start with the letter S.

Expressions that use dates in matching criteria

The expressions in the following table demonstrate the use of dates and related functions in criteria expressions.
For more information about entering and using date values, see the article Enter a date or time value. For information about using the functions in these sample expressions, click the links to the various function topics.

Field	Expression	Description
ShippedDate	#2/2/2007#	Displays orders shipped on February 2, 2007.
ShippedDate	Date()	Displays orders shipped today.
RequiredDate	Between Date( ) And DateAdd("m", 3, Date( ))	Uses the Between...And operator and the DateAdd and Date functions to display orders required between today's date and three months from today's date.
OrderDate	< Date( ) - 30	Uses the Date function to display orders more than 30 days old.
OrderDate	Year([OrderDate])=2007	Uses the Year function to display orders with order dates in 2007.
OrderDate	DatePart("q", [OrderDate])=4	Uses the DatePart function to display orders for the fourth calendar quarter.
OrderDate	DateSerial(Year ([OrderDate]), Month([OrderDate])+1, 1)-1	Uses the DateSerial, Year, and Month functions to display orders for the last day of each month.
OrderDate	Year([OrderDate])= Year(Now()) And Month([OrderDate])= Month(Now())	Uses the Year and Month functions and the And operator to display orders for the current year and month.
ShippedDate	Between #1/5/2007# And #1/10/2007#	Uses the Between...And operator to display orders shipped no earlier than 5-Jan-2007 and no later than 10-Jan-2007.
RequiredDate	Between Date( ) And DateAdd("M", 3, Date( ))	Uses the Between...And operator to display orders required between today's date and three months from today's date.
BirthDate	Month([BirthDate])=Month(Date())	Uses the Month and Date functions to display employees who have birthdays this month.

Expressions that match a missing value (null) or a zero-length string

The expressions in the following table work with fields that have potentially missing information — those that might contain a null value or a zero-length string. A null value represents the absence of information; it does not represent a zero or any value at all. Access supports this idea of missing information because the concept is vital to the integrity of a database. In the real world, information is often missing, even if only temporarily (for example, the as-yet undetermined price for a new product). Therefore, a database that models a real world entity, such as a business, must be able to record information as missing. You can use the IsNull function to determine if a field or control contains a null value, and you can use the Nz function to convert a null value to zero.

Field	Expression	Description
ShipRegion	Is Null	Displays orders for customers whose ShipRegion field is null (missing).
ShipRegion	Is Not Null	Displays orders for customers whose ShipRegion field contains a value.
Fax	""	Displays orders for customers who don't have a fax machine, indicated by a zero-length string value in the Fax field instead of a null (missing) value.

Expressions that use patterns to match records

The Like operator provides a great deal of flexibility when you are trying to match rows that follow a pattern, because you can use Like with wildcard characters and define patterns for Access to match. For example, the * (asterisk) wildcard character matches a sequence of characters of any type, and makes it easy to find all names that begin with a letter. For example, you use the expression Like "S*" to find all names that begin with the letter S.
For more information, see the article Like Operator.

Field	Expression	Description
ShipName	Like "S*"	Finds all records in the ShipName field that start with the letter S.
ShipName	Like "*Imports"	Finds all records in the ShipName field that end with the word "Imports".
ShipName	Like "[A-D]*"	Finds all records in the ShipName field that begin with the letters A, B, C, or D.
ShipName	Like "*ar*"	Finds all records in the ShipName field that include the letter sequence "ar".
ShipName	Like "Maison Dewe?"	Finds all records in the ShipName field that include "Maison" in the first part of the value and a five-letter string in which the first four letters are "Dewe" and the last letter is unknown.
ShipName	Not Like "A*"	Finds all records in the ShipName field that do not start with the letter A.

Expressions that match rows based on the result of a domain aggregate function

You use a domain aggregate function when you need to sum, count, or average values selectively. For example, you might want to count only those values that fall within a certain range, or that evaluate to Yes. At other times, you might need to look up a value from another table so that you can display it. The sample expressions in the following table use the domain aggregate functions to perform a calculation on a set of values, and use the result as the query criteria.

Field	Expression	Description
Freight	> (DStDev("[Freight]", "Orders") + DAvg("[Freight]", "Orders"))	Uses the DStDev and DAvg functions to display all orders for which the freight cost rose above the mean plus the standard deviation for freight cost.
Quantity	> DAvg("[Quantity]", "[Order Details]")	Uses the DAvg function to display products ordered in quantities above the average order quantity.

Expressions that match based on the results of subqueries

You use a subquery, also called a nested query, to calculate a value for use as a criterion. The sample expressions in the following table match rows based on the results returned by a subquery.

Field	Expression	Displays
UnitPrice	(SELECT [UnitPrice] FROM [Products] WHERE [ProductName] = "Aniseed Syrup")	Products whose price is the same as the price of Aniseed Syrup.
UnitPrice	>(SELECT AVG([UnitPrice]) FROM [Products])	Products that have a unit price above the average.
Salary	> ALL (SELECT [Salary] FROM [Employees] WHERE ([Title] LIKE "*Manager*") OR ([Title] LIKE "*Vice President*"))	Salary of every sales representative whose salary is higher than that of all employees with "Manager" or "Vice President" in their titles.
OrderTotal: [UnitPrice] * [Quantity]	> (SELECT AVG([UnitPrice] * [Quantity]) FROM [Order Details])	Orders with totals that are higher than the average order value.

Expressions for use in update queries

You use an update query to modify the data in one or more existing fields in a database. For example, you can replace values or delete them entirely. This table demonstrates some ways to use expressions in update queries. You use these expressions in the Update To row in the query design grid for the field that you want to update.
For more information about creating update queries, see the article Create an update query.

Field	Expression	Result
Title	"Salesperson"	Changes a text value to Salesperson.
ProjectStart	#8/10/07#	Changes a date value to 10-Aug-07.
Retired	Yes	Changes a No value in a Yes/No field to Yes.
PartNumber	"PN" & [PartNumber]	Adds PN to the beginning of each specified part number.
LineItemTotal	[UnitPrice] * [Quantity]	Calculates the product of UnitPrice and Quantity.
Freight	[Freight] * 1.5	Increases freight charges by 50 percent.
Sales	DSum("[Quantity] * [UnitPrice]", "Order Details", "[ProductID]=" & [ProductID])	Where the ProductID values in the current table match the ProductID values in the Order Details table, updates sales totals based on the product of Quantity and UnitPrice.
ShipPostalCode	Right([ShipPostalCode], 5)	Truncates the leftmost characters, leaving the five rightmost characters.
UnitPrice	Nz([UnitPrice])	Changes a null (undefined or unknown) value to a zero (0) in the UnitPrice field.

Expressions used in SQL statements

Structured Query Language, or SQL, is the query language that Access uses. Every query that you create in query Design view can also be expressed by using SQL. To see the SQL statement for any query, click SQL View on the View menu. The following table shows sample SQL statements that employ an expression.

SQL statement that uses an expression	Result
SELECT [FirstName],[LastName] FROM [Employees] WHERE [LastName]="Danseglio"	Displays the values in the FirstName and LastName fields for employees whose last name is Danseglio.
SELECT [ProductID],[ProductName] FROM [Products] WHERE [CategoryID]=Forms![New Products]![CategoryID];	Displays the values in the ProductID and ProductName fields in the Products table for records in which the CategoryID value matches the CategoryID value specified in an open New Products form.
SELECT Avg([ExtendedPrice]) AS [Average Extended Price] FROM [Order Details Extended] WHERE [ExtendedPrice]>1000;	Calculates the average extended price for orders for which the value in the ExtendedPrice field is more than 1000, and displays it in a field named Average Extended Price.
SELECT [CategoryID], Count([ProductID]) AS [CountOfProductID] FROM [Products] GROUP BY [CategoryID] HAVING Count([ProductID])>10;	In a field named CountOfProductID, displays the total number of products for categories with more than 10 products.

Top of Page

Examples of default value expressions

When you design a database, you might want to assign a default value to a field or control. Access then supplies the default value when a new record containing the field is created or when an object that contains the control is created. The expressions in the following table represent the sample default values for a field or control.

Add a default value for a field in a table

1. In the Navigation Pane, right-click the table that you want to change and click Design View on the shortcut menu.
   
2. Click the field that you want to change, and on the General tab, click the Default Value property box.
   
3. Type the expression, or click the Build button to the right of the property box to create an expression by using the Expression Builder.
   

If a control is bound to a field in a table, and the field has a default value, the default value of the control takes precedence.

Field	Expression	Default field value
Quantity	1	1
Region	"MT"	MT
Region	"New York, N.Y."	New York, N.Y. (Note that you must enclose the value in quotation marks if it includes punctuation.)
Fax	""	A zero-length string to indicate that, by default, this field should be empty instead of containing a null value
Order Date	Date( )	Today's date
DueDate	Date() + 60	The date 60 days forward from today

Top of Page

Examples of field validation rule expressions

You can create a validation rule for a field or control by using an expression. Access then enforces the rule when data is entered into the field or control. To create a validation rule, you modify the ValidationRule property of the field or control. You should also consider setting the ValidationText property, which holds the text that Access displays when the validation rule is violated. If you don't set the ValidationText property, Access displays a default error message.

Add a validation rule to a field

1. In the Navigation Pane, right-click the table that you want to change and click Design View on the shortcut menu.
   
2. Click the field that you want to change.
   
3. Click the Validation Rule property box, located in the lower section of the table designer.
   
4. Type the expression, or click the Build button to the right of the property box to create an expression by using the Expression Builder.
   
   Note: Do not precede the expression with the = operator when you create a validation rule.

The examples in the following table demonstrate the validation rule expressions for the ValidationRule property and the associated text for the ValidationText property.

ValidationRule property	ValidationText property
<> 0	Please enter a nonzero value.
0 Or > 100	Value must be either 0 or more than 100.
Like "K???"	Value must be four characters, beginning with the letter K.
< #1/1/2007#	Enter a date prior to 1/1/2007.
>= #1/1/2007# And < #1/1/2008#	Date must occur in 2007.

For more information about validating data, see the article Create a validation rule to validate data in a field.
Top of Page

Examples of macro condition expressions

In some cases, you might want to carry out an action or series of actions in a macro only if a particular condition is true. For example, suppose you want an action to run only when the value of the Counter text box is 10. You use an expression to define the condition in the Condition column of the macro: [Counter]=10.

Add a condition for a macro action

1. In the Navigation Pane, right-click the macro that you want to change and click Design View on the shortcut menu.
   
2. If you don't see the Condition column in the macro designer, on the Design tab, in the Show/Hide group, click Conditions.
   
3. Click the Condition cell for the macro action that you want to change, and then type your conditional expression.
   
4. Save your changes and close the macro.
   

As with the ValidationRule property, the Condition column expression is a conditional expression. It must resolve to either a True or False value. The action takes place only when the condition is true.

Use this expression to carry out the action	If
[City]="Paris"	Paris is the City value in the field on the form from which the macro was run.
DCount("[OrderID]", "Orders") > 35	There are more than 35 entries in the OrderID field of the Orders table.
DCount("*", "[Order Details]", "[OrderID]=" & Forms![Orders]![OrderID]) > 3	There are more than three entries in the Order Details table for which the OrderID field of the table matches the OrderID field on the Orders form.
[ShippedDate] Between #2-Feb-2007# And #2-Mar-2007#	The value of the ShippedDate field on the form from which the macro is run is no earlier than 2-Feb-2007 and no later than 2-Mar-2007.
Forms![Products]![UnitsInStock] < 5	The value of the UnitsInStock field on the Products form is less than 5.
IsNull([FirstName])	The FirstName value on the form from which the macro is run is null (has no value). This expression is equivalent to [FirstName] Is Null.
[CountryRegion]="UK" And Forms![SalesTotals]![TotalOrds] > 100	The value in the CountryRegion field on the form from which the macro is run is UK, and the value of the TotalOrds field on the SalesTotals form is greater than 100.
[CountryRegion] In ("France", "Italy", "Spain") And Len([PostalCode])<>5	The value in the CountryRegion field on the form from which the macro is run is either France, Italy, or Spain, and the postal code is not 5 characters long.
MsgBox("Confirm changes?",1)=1	You click OK in a dialog box that the MsgBox function displays. If you click Cancel in the dialog box, Access ignores the action.

Note: To force Access to temporarily ignore an action, type False as a condition. Forcing Access to temporarily ignore an action can be helpful when you are trying to find problems in a macro.

For more information about macros, see the articles Macro basics in Access 2007 and Create a macro.
Top of Page

Read More

INTRODUCTION TO MACROMEDIA AUTHORWARE 7

Video on using Macromedia Authorware 7.0

Read More

Sample of screen Mock up for intractive Multimedia

Read More

Macromedia Authorware Tutorial

Macromedia Authorware Tutorial based on Project

Clik here : https://drive.google.com/file/d/0BwJBf8YcQfEHUU9nNi01QzhabHM/view?usp=sharing

Read More

Data Flow Diagrams

Notes

1. http://www.eis.mdx.ac.uk/staffpages/geetha/bis2030/DFD.html

2. https://www.visual-paradigm.com/tutorials/data-flow-diagram-dfd.jsp

Read More

Tutorial For C Programming

To Download Click HERE

Read More

A Simple C Programming Module

http://downloads.ziddu.com/download/25228121/C_PROGRAMMING_MODULE.pdf.html

Read More

How to Create a Reference for a YouTube Video

How to Create a Reference for a YouTube Video

by Stefanie
Halloween is coming! What better time of year to track down some of your favorite scary YouTube videos to frighten your friends or prove your position on the existence of ghosts? If you spin your YouTube search into research (“The Startle Reflex: Can You Use It to Identify Individuals With Antisocial Personality Disorder?”), here is how to create a reference for your stimulus. (By the way, none of the sample videos given below include something that jumps out at you. Experimentation has proved that my startle reflex is just fine, thanks.)

The general format is as follows:

Author, A. A. [Screen name]. (year, month day). Title of video [Video file]. Retrieved from http://xxxxx

For retrievability, the person who posted the video is put in the author position. You might have noticed that the template shows both a typically formatted author name and a place for a screen name, and here's why: On YouTube and many other video-posting websites, users must post under a screen name. This screen name is integral to finding the video on YouTube, so including it in the reference is important. Sometimes, however, the real name of the individual who posted the video is also known. The individual's real name likely better connects him or her to the real world as well as to any other sources he or she may have provided for your paper (e.g., an author who wrote an article and also produced a YouTube video). Providing the real name, when available, aids the reader by highlighting these interconnections and also makes it possible to alphabetize the reference among any other references by that same author in the reference list. Thus, the reference format for a YouTube video includes both elements when both elements are available.

Example:

Apsolon, M. [markapsolon]. (2011, September 9). Real ghost girl caught on Video Tape 14 [Video file]. Retrieved from http://www.youtube.com/watch?v=6nyGCbxD848

(The capitalization [or lack thereof] in the screen name is in keeping with how it appears online.)

On YouTube, the screen name is most prominent. If the user’s real name is not available, include only the screen name, without brackets:

Screen name. (year, month day). Title of video [Video file]. Retrieved from http://xxxxx

Example:

Bellofolletti. (2009, April 8). Ghost caught on surveillance camera [Video file]. Retrieved from http://www.youtube.com/watch?v =Dq1ms2JhYBI&feature=related

In text, cite by the author name that appears outside of brackets, whichever one that may be. For example, the two example references provided above would be cited as follows: (Apsolon, 2011; Bellofolletti, 2009).

Read More

Writing a Bibliography: APA Format

Below are standard formats and examples for basic bibliographic information recommended by the American Psychological Association (APA). For more information on the APA format, see http://www.apastyle.org.

Basics

Your list of works cited should begin at the end of the paper on a new page with the centered title, References. Alphabetize the entries in your list by the author's last name, using the letter-by-letter system (ignore spaces and other punctuation.) Only the initials of the first and middle names are given. If the author's name is unknown, alphabetize by the title, ignoring any A, An, or The.

For dates, spell out the names of months in the text of your paper, but abbreviate them in the list of works cited, except for May, June, and July. Use either the day-month-year style (22 July 1999) or the month-day-year style (July 22, 1999) and be consistent. With the month-day-year style, be sure to add a comma after the year unless another punctuation mark goes there.

Underlining or Italics?

When reports were written on typewriters, the names of publications were underlined because most typewriters had no way to print italics. If you write a bibliography by hand, you should still underline the names of publications. But, if you use a computer, then publication names should be in italics as they are below. Always check with your instructor regarding their preference of using italics or underlining. Our examples use italics.

Hanging Indentation

All APA citations should use hanging indents, that is, the first line of an entry should be flush left, and the second and subsequent lines should be indented 1/2".

Capitalization, Abbreviation, and Punctuation

The APA guidelines specify using sentence-style capitalization for the titles of books or articles, so you should capitalize only the first word of a title and subtitle. The exceptions to this rule would be periodical titles and proper names in a title which should still be capitalized. The periodical title is run in title case, and is followed by the volume number which, with the title, is also italicized.

If there is more than one author, use an ampersand (&) before the name of the last author. If there are more than six authors, list only the first one and use et al. for the rest.

Place the date of publication in parentheses immediately after the name of the author. Place a period after the closing parenthesis. Do not italicize, underline, or put quotes around the titles of shorter works within longer works.

Format Examples

Books

Format:
Author's last name, first initial. (Publication date). Book title. Additional information. City of publication: Publishing company.

Examples:
Allen, T. (1974). Vanishing wildlife of North America. Washington, D.C.: National Geographic Society.
Boorstin, D. (1992). The creators: A history of the heroes of the imagination. New York: Random House.

Nicol, A. M., & Pexman, P. M. (1999). Presenting your findings: A practical guide for creating tables. Washington, DC: American Psychological Association.

Searles, B., & Last, M. (1979). A reader's guide to science fiction. New York: Facts on File, Inc.
Toomer, J. (1988). Cane. Ed. Darwin T. Turner. New York: Norton.

Encyclopedia & Dictionary

Format:
Author's last name, first initial. (Date). Title of Article. Title of Encyclopedia (Volume, pages). City of publication: Publishing company.

Examples:
Bergmann, P. G. (1993). Relativity. In The new encyclopedia britannica (Vol. 26, pp. 501-508). Chicago: Encyclopedia Britannica.

Merriam-Webster's collegiate dictionary (10th ed.). (1993). Springfield, MA: Merriam-Webster.
Pettingill, O. S., Jr. (1980). Falcon and Falconry. World book encyclopedia. (pp. 150-155). Chicago: World Book.

Tobias, R. (1991). Thurber, James. Encyclopedia americana. (p. 600). New York: Scholastic Library Publishing.

Magazine & Newspaper Articles

Format:
Author's last name, first initial. (Publication date). Article title. Periodical title, volume number(issue number if available), inclusive pages.

Note: Do not enclose the title in quotation marks. Put a period after the title. If a periodical includes a volume number, italicize it and then give the page range (in regular type) without "pp." If the periodical does not use volume numbers, as in newspapers, use p. or pp. for page numbers.
Note: Unlike other periodicals, p. or pp. precedes page numbers for a newspaper reference in APA style.

Examples:
Harlow, H. F. (1983). Fundamentals for preparing psychology journal articles. Journal of Comparative and Physiological Psychology, 55, 893-896.
Henry, W. A., III. (1990, April 9). Making the grade in today's schools. Time, 135, 28-31.
Kalette, D. (1986, July 21). California town counts town to big quake. USA Today, 9, p. A1.
Kanfer, S. (1986, July 21). Heard any good books lately? Time, 113, 71-72.
Trillin, C. (1993, February 15). Culture shopping. New Yorker, pp. 48-51.

Website or Webpage

Format:
Online periodical:
Author's name. (Date of publication). Title of article. Title of Periodical, volume number, Retrieved month day, year, from full URL

Online document:
Author's name. (Date of publication). Title of work. Retrieved month day, year, from full URL

Note: When citing Internet sources, refer to the specific website document. If a document is undated, use "n.d." (for no date) immediately after the document title. Break a lengthy URL that goes to another line after a slash or before a period. Continually check your references to online documents. There is no period following a URL.
Note: If you cannot find some of this information, cite what is available.

Examples:
Devitt, T. (2001, August 2). Lightning injures four at music festival. The Why? Files. Retrieved January 23, 2002, from http://whyfiles.org/137lightning/index.html

Dove, R. (1998). Lady freedom among us. The Electronic Text Center. Retrieved June 19, 1998, from Alderman Library, University of Virginia website: http://etext.lib.virginia.edu/subjects/afam.html

Note: If a document is contained within a large and complex website (such as that for a university or a government agency), identify the host organization and the relevant program or department before giving the URL for the document itself. Precede the URL with a colon.

Fredrickson, B. L. (2000, March 7). Cultivating positive emotions to optimize health and well-being. Prevention & Treatment, 3, Article 0001a. Retrieved November 20, 2000, from http://journals.apa.org/prevention/volume3/pre0030001a.html

GVU's 8th WWW user survey. (n.d.). Retrieved August 8, 2000, from http://www.cc.gatech.edu/gvu/usersurveys/survey1997-10/

Health Canada. (2002, February). The safety of genetically modified food crops. Retrieved March 22, 2005, from http://www.hc-sc.gc.ca/english/protection/biologics_genetics/gen_mod_foods/genmodebk.html

Hilts, P. J. (1999, February 16). In forecasting their emotions, most people flunk out. New York Times. Retrieved November 21, 2000, from http://www.nytimes.com

Source : http://www.sciencebuddies.org/science-fair-projects/project_apa_format_examples.shtml

Read More

The correct practice to joint text and graphic

Read More

screen mock-up, content maps, storyboard for montage/ video / ads

Sample of screen mock-up for video

 

content maps for video

  

Over all story for video

Read More

Keyboard Shortcuts (Microsoft Windows)

Keyboard Shortcuts (Microsoft Windows)
1. CTRL+C (Copy)
2. CTRL+X (Cut)
... 3. CTRL+V (Paste)
4. CTRL+Z (Undo)
5. DELETE (Delete)
6. SHIFT+DELETE (Delete the selected item permanently without placing the item in the Recycle Bin)
7. CTRL while dragging an item (Copy the selected item)
8. CTRL+SHIFT while dragging an item (Create a shortcut to the selected item)
9. F2 key (Rename the selected item)
10. CTRL+RIGHT ARROW (Move the insertion point to the beginning of the next word)
11. CTRL+LEFT ARROW (Move the insertion point to the beginning of the previous word)
12. CTRL+DOWN ARROW (Move the insertion point to the beginning of the next paragraph)
13. CTRL+UP ARROW (Move the insertion point to the beginning of the previous paragraph)
14. CTRL+SHIFT with any of the arrow keys (Highlight a block of text)
SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document)
15. CTRL+A (Select all)
16. F3 key (Search for a file or a folder)
17. ALT+ENTER (View the properties for the selected item)
18. ALT+F4 (Close the active item, or quit the active program)
19. ALT+ENTER (Display the properties of the selected object)
20. ALT+SPACEBAR (Open the shortcut menu for the active window)
21. CTRL+F4 (Close the active document in programs that enable you to have multiple documents opensimultaneously)
22. ALT+TAB (Switch between the open items)
23. ALT+ESC (Cycle through items in the order that they had been opened)
24. F6 key (Cycle through the screen elements in a window or on the desktop)
25. F4 key (Display the Address bar list in My Computer or Windows Explorer)
26. SHIFT+F10 (Display the shortcut menu for the selected item)
27. ALT+SPACEBAR (Display the System menu for the active window)
28. CTRL+ESC (Display the Start menu)
29. ALT+Underlined letter in a menu name (Display the corresponding menu) Underlined letter in a command name on an open menu (Perform the corresponding command)
30. F10 key (Activate the menu bar in the active program)
31. RIGHT ARROW (Open the next menu to the right, or open a submenu)
32. LEFT ARROW (Open the next menu to the left, or close a submenu)
33. F5 key (Update the active window)
34. BACKSPACE (View the folder onelevel up in My Computer or Windows Explorer)
35. ESC (Cancel the current task)
36. SHIFT when you insert a CD-ROMinto the CD-ROM drive (Prevent the CD-ROM from automatically playing)
Dialog Box - Keyboard Shortcuts
1. CTRL+TAB (Move forward through the tabs)
2. CTRL+SHIFT+TAB (Move backward through the tabs)
3. TAB (Move forward through the options)
4. SHIFT+TAB (Move backward through the options)
5. ALT+Underlined letter (Perform the corresponding command or select the corresponding option)
6. ENTER (Perform the command for the active option or button)
7. SPACEBAR (Select or clear the check box if the active option is a check box)
8. Arrow keys (Select a button if the active option is a group of option buttons)
9. F1 key (Display Help)
10. F4 key (Display the items in the active list)
11. BACKSPACE (Open a folder one level up if a folder is selected in the Save As or Open dialog box)

Microsoft Natural Keyboard Shortcuts
1. Windows Logo (Display or hide the Start menu)
2. Windows Logo+BREAK (Display the System Properties dialog box)
3. Windows Logo+D (Display the desktop)
4. Windows Logo+M (Minimize all of the windows)
5. Windows Logo+SHIFT+M (Restorethe minimized windows)
6. Windows Logo+E (Open My Computer)
7. Windows Logo+F (Search for a file or a folder)
8. CTRL+Windows Logo+F (Search for computers)
9. Windows Logo+F1 (Display Windows Help)
10. Windows Logo+ L (Lock the keyboard)
11. Windows Logo+R (Open the Run dialog box)
12. Windows Logo+U (Open Utility Manager)
13. Accessibility Keyboard Shortcuts
14. Right SHIFT for eight seconds (Switch FilterKeys either on or off)
15. Left ALT+left SHIFT+PRINT SCREEN (Switch High Contrast either on or off)
16. Left ALT+left SHIFT+NUM LOCK (Switch the MouseKeys either on or off)
17. SHIFT five times (Switch the StickyKeys either on or off)
18. NUM LOCK for five seconds (Switch the ToggleKeys either on or off)
19. Windows Logo +U (Open Utility Manager)
20. Windows Explorer Keyboard Shortcuts
21. END (Display the bottom of the active window)
22. HOME (Display the top of the active window)
23. NUM LOCK+Asterisk sign (*) (Display all of the subfolders that are under the selected folder)
24. NUM LOCK+Plus sign (+) (Display the contents of the selected folder)

MMC Console keyboard shortcuts

1. SHIFT+F10 (Display the Action shortcut menu for the selected item)
2. F1 key (Open the Help topic, if any, for the selected item)
3. F5 key (Update the content of all console windows)
4. CTRL+F10 (Maximize the active console window)
5. CTRL+F5 (Restore the active console window)
6. ALT+ENTER (Display the Properties dialog box, if any, for theselected item)
7. F2 key (Rename the selected item)
8. CTRL+F4 (Close the active console window. When a console has only one console window, this shortcut closes the console)

Remote Desktop Connection Navigation
1. CTRL+ALT+END (Open the Microsoft Windows NT Security dialog box)
2. ALT+PAGE UP (Switch between programs from left to right)
3. ALT+PAGE DOWN (Switch between programs from right to left)
4. ALT+INSERT (Cycle through the programs in most recently used order)
5. ALT+HOME (Display the Start menu)
6. CTRL+ALT+BREAK (Switch the client computer between a window and a full screen)
7. ALT+DELETE (Display the Windows menu)
8. CTRL+ALT+Minus sign (-) (Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.)
9. CTRL+ALT+Plus sign (+) (Place asnapshot of the entire client window area on the Terminal server clipboardand provide the same functionality aspressing ALT+PRINT SCREEN on a local computer.)

Microsoft Internet Explorer Keyboard Shortcuts
1. CTRL+B (Open the Organize Favorites dialog box)
2. CTRL+E (Open the Search bar)
3. CTRL+F (Start the Find utility)
4. CTRL+H (Open the History bar)
5. CTRL+I (Open the Favorites bar)
6. CTRL+L (Open the Open dialog box)
7. CTRL+N (Start another instance of the browser with the same Web address)
8. CTRL+O (Open the Open dialog box,the same as CTRL+L)
9. CTRL+P (Open the Print dialog box)
10. CTRL+R (Update the current Web page)
11. CTRL+W (Close the current window)

share with others...

Read More

Reference Book For ICT Form 6

There are several reference books :

Read More