Edexcel Unit 7 Using Database Software MS Access Table and Forms Validation Criteria

MS Access database Table and Forms Validation Criteria 

When you want to limit the results of a query based on the values in a field, you use query criteria. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. For example, = "Chicago" is an expression that Access can compare to values in a text field in a query. If the value for that field in a given record is "Chicago", Access includes the record in the query results.

This topic lists several examples of query criteria. It assumes that you are familiar with designing simple select queries.

To learn more about creating select queries, see the article Select data by using a query.

In this topic

• Introduction to query criteria
• Criteria for Text, Memo, and Hyperlink fields
• Criteria for Number, Currency, and AutoNumber fields
• Criteria for Date/Time fields
• Criteria for other fields

Introduction to query criteria

A criterion is similar to a formula — it is a string that may consist of field references, operators, and constants. Query criteria are also referred to as expressions in Microsoft Office Access 2007.

The following tables shows some sample criteria and explains how they work.

CRITERIA	DESCRIPTION
>25 and <50	This criterion applies to a Number field, such as Price or UnitsInStock. It includes only those records where the Price or UnitsInStock field contains a value greater than 25 and less than 50.
DateDiff ("yyyy", [BirthDate], Date()) > 30	This criterion applies to a Date/Time field, such as BirthDate. Only records where the number of years between a person's birthdate and today's date is greater than 30 are included in the query result.
Is Null	This criterion can be applied to any type of field to show records where the field value is null.

As you can see, criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.

This topic lists several commonly used criteria by data type. If the examples given in this topic do not address your specific needs, you might need to write your own criteria. To do that, you must first familiarize yourself with the full list of functions, operators, special characters, and the syntax for expressions referring to fields and literals. For more information, see the articles listed in the See also section.

Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the query in Design view. You then identify the fields for which you want to specify criteria. If the field is not already in the design grid, you add it by either dragging it from the query design window to the field grid, or by double-clicking the field (Double-clicking the field automatically adds it to the next empty column in the field grid.). Finally, you type the criteria in the Criteria row

Criteria that you specify for different fields in the Criteria row are combined by using the AND operator. In other words, the criteria specified in the City and BirthDate fields are interpreted like this:

City = "Chicago" AND BirthDate < DateAdd("yyyy", -40, Date())

---

 The City and BirthDate fields include criteria.

 Only records where the value of the City field is Chicago will satisfy this criterion.

 Only records of those who are at least 40 years old will satisfy this criterion.

 Only records that meet both criteria will be included in the result.

---

What if you want only one of these conditions to be met? In other words, if you have alternate criteria, how do you enter them?

If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid.

---

 The City criterion is specified in the Criteria row.

 The BirthDate criterion is specified in the or row.

---

Criteria specified in the Criteria and or rows are combined using the OR operator, as shown below:

City = "Chicago" OR BirthDate < DateAdd("yyyy", -40, Date())

If you need to specify more alternatives, use the rows below the or row.

Before you continue with the examples, note the following:

• If the criteria is temporary or changes often, you can filter the query result instead of frequently modifying the query criteria. A filter is a temporary criterion that changes the query result without altering the design of the query. For more information about filters, see the article Filter: Limit the number of records in a view.
• If the criteria fields don't change, but the values you are interested in do change frequently, you can create a parameter query. A parameter query prompts the user for field values, and then uses those values to create the query criteria. For more information about parameter queries, see the article Use parameters in queries and reports.

Criteria for Text, Memo, and Hyperlink fields

If you want to see a demo of criteria that you can use with text fields, see the article Watch this: Specify criteria for a text field.

The following examples are for the CountryRegion field in a query that is based on a table that stores contacts information. The criterion is specified in the Criteria row of the field in the design grid.

A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value. To specify criteria for the destination Uniform Resource Locator (URL) portion of the value, use the HyperlinkPartexpression. The syntax for this expression is as follows: HyperlinkPart([Table1].[Field1],1) = "http://www.microsoft.com/", where Table1 is the name of the table containing the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the URL you want to match.

TO INCLUDE RECORDS THAT...	USE THIS CRITERION	QUERY RESULT
Exactly match a value, such as China	"China"	Returns records where the CountryRegion field is set to China.
Do not match a value, such as Mexico	Not "Mexico"	Returns records where the CountryRegion field is set to a country/region other than Mexico.
Begin with the specified string, such as U	Like U*	Returns records for all countries/regions whose names start with "U", such as UK, USA, and so on.  NOTE   When used in an expression, the asterisk (*) represents any string of characters — it is also called a wildcard character. For a list of such characters, see the article Access wildcard character reference.
Do not begin with the specified string, such as U	Not Like U*	Returns records for all countries/regions whose names start with a character other than "U".
Contain the specified string, such as Korea	Like "*Korea*"	Returns records for all countries/regions that contain the string "Korea".
Do not contain the specified string, such as Korea	Not Like "*Korea*"	Returns records for all countries/regions that do not contain the string "Korea".
End with the specified string, such as "ina"	Like "*ina"	Returns records for all countries/regions whose names end in "ina", such as China and Argentina.
Do not end with the specified string, such as "ina"	Not Like "*ina"	Returns records for all countries/regions that do not end in "ina", such as China and Argentina.
Contain null (or missing) values	Is Null	Returns records where there is no value in the field.
Do not contain null values	Is Not Null	Returns records where the value is not missing in the field.
Contain zero-length strings	"" (a pair of quotes)	Returns records where the field is set to a blank (but not null) value. For example, records of sales made to another department might contain a blank value in the CountryRegion field.
Do not contain zero-length strings	Not ""	Returns records where the CountryRegion field has a nonblank value.
Contains null values or zero-length strings	"" Or Is Null	Returns records where there is either no value in the field, or the field is set to a blank value.
Is not empty or blank	Is Not Null And Not ""	Returns records where the CountryRegion field has a nonblank, non-null value.
Follow a value, such as Mexico, when sorted in alphabetical order	>= "Mexico"	Returns records of all countries/regions, beginning with Mexico and continuing through the end of the alphabet.
Fall within a specific range, such as A through D	Like "[A-D]*"	Returns records for countries/regions whose names start with the letters "A" through "D".
Match one of two values, such as USA or UK	"USA" Or "UK"	Returns records for USA and UK.
Contain one of the values in a list of values	In("France", "China", "Germany", "Japan")	Returns records for all countries/regions specified in the list.
Contain certain characters at a specific position in the field value	Right([CountryRegion], 1) = "y"	Returns records for all countries/regions where the last letter is "y".
Satisfy length requirements	Len([CountryRegion]) > 10	Returns records for countries/regions whose name is more than 10 characters long.
Match a specific pattern	Like "Chi??"	Returns records for countries/regions, such as China and Chile, whose names are five characters long and the first three characters are "Chi".  NOTE   The characters ? and _, when used in an expression, represent a single character — these are also called wildcard characters. The character _ cannot be used in the same expression with the ? character, nor can it be used in an expression with the * wildcard character. You may use the wildcard character _ in an expression that also contains the % wildcard character.

Criteria for Number, Currency, and AutoNumber fields

The following examples are for the UnitPrice field in a query that is based on a table that stores products information. The criterion is specified in the Criteria row of the field in the query design grid.

TO INCLUDE RECORDS THAT...	USE THIS CRITERION	QUERY RESULT
Exactly match a value, such as 100	100	Returns records where the unit price of the product is $100.
Do not match a value, such as 1000	Not 1000	Returns records where the unit price of the product is not $1000.
Contain a value smaller than a value, such as 100	< 100 <= 100	Returns records where the unit price is less than $100 (<100). The second expression (<=100) displays records where the unit price is less than or equal to $100.
Contain a value larger than a value, such as 99.99	>99.99 >=99.99	Returns records where the unit price is greater than $99.99 (>99.99). The second expression displays records where the unit price is greater than or equal to $99.99.
Contain one of the two values, such as 20 or 25	20 or 25	Returns records where the unit price is either $20 or $25.
Contain a value that falls with a range of values	>49.99 and <99.99 -or- Between 50 and 100	Returns records where the unit price is between (but not including) $49.99 and $99.99.
Contain a value that falls outside a range	<50 or >100	Returns records where the unit price is not between $50 and $100.
Contain one of many specific values	In(20, 25, 30)	Returns records where the unit price is either $20, $25, or $30.
Contain a value that ends with the specified digits	Like "*4.99"	Returns records where the unit price ends with "4.99", such as $4.99, $14.99, $24.99, and so on.
Contain null null (or missing) values	Is Null	Returns records where no value is entered in the UnitPrice field.
Contain non-null values	Is Not Null	Returns records where the value is not missing in the UnitPrice field.

Criteria for Date/Time fields

The following examples are for the OrderDate field in a query based on a table that stores Orders information. The criterion is specified in the Criteria row of the field in the query design grid.

TO INCLUDE RECORDS THAT ...	USE THIS CRITERION	QUERY RESULT
Exactly match a value, such as 2/2/2006	#2/2/2006#	Returns records of transactions that took place on Feb 2, 2006. Remember to surround date values with the # character so that Access can distinguish between date values and text strings.
Do not match a value, such as 2/2/2006	Not #2/2/2006#	Returns records of transactions that took place on a day other than Feb 2, 2006.
Contain values that fall before a certain date, such as 2/2/2006	< #2/2/2006#	Returns records of transactions that took place before Feb 2, 2006. To view transactions that took place on or before this date, use the <= operator instead of the < operator.
Contain values that fall after a certain date, such as 2/2/2006	> #2/2/2006#	Returns records of transactions that took place after Feb 2, 2006. To view transactions that took place on or after this date, use the >= operator instead of the > operator.
Contain values that fall within a date range	>#2/2/2006# and <#2/4/2006#	Returns records where the transactions took place between Feb 2, 2006 and Feb 4, 2006. You can also use the Between operator to filter for a range of values, including the end points. For example, Between #2/2/2006# and #2/4/2006# is the same as >=#2/2/2006# and <=#2/4/2006# .
Contain values that fall outside a range	<#2/2/2006# or >#2/4/2006#	Returns records where the transactions took place before Feb 2, 2006 or after Feb 4, 2006.
Contain one of two values, such as 2/2/2006 or 2/3/2006	#2/2/2006# or #2/3/2006#	Returns records of transactions that took place on either Feb 2, 2006 or Feb 3, 2006.
Contain one of many values	In (#2/1/2006#, #3/1/2006#, #4/1/2006#)	Returns records where the transactions took place on Feb 1, 2006, March 1, 2006, or April 1, 2006.
Contain a date that falls in a specific month (irrespective of year), such as December	DatePart("m", [SalesDate]) = 12	Returns records where the transactions took place in December of any year.
Contain a date that falls in a specific quarter (irrespective of year), such as the first quarter	DatePart("q", [SalesDate]) = 1	Returns records where the transactions took place in the first quarter of any year.
Contain today's date	Date()	Returns records of transactions that took place on the current day. If today's date is 2/2/2006, you see records where the OrderDate field is set to Feb 2, 2006.
Contain yesterday's date	Date()-1	Returns records of transactions that took place the day before the current day. If today's date is 2/2/2006, you see records for Feb 1, 2006.
Contain tomorrow's date	Date() + 1	Returns records of transactions that took place the day after the current day. If today's date is 2/2/2006, you see records for Feb 3, 2006.
Contain dates that fall during the current week	DatePart("ww", [SalesDate]) = DatePart("ww", Date()) and Year( [SalesDate]) = Year(Date())	Returns records of transactions that took place during the current week. A week starts on Sunday and ends on Saturday.
Contain dates that fell during the previous week	Year([SalesDate])* 53 + DatePart("ww", [SalesDate]) = Year(Date())* 53 + DatePart("ww", Date()) - 1	Returns records of transactions that took place during the last week. A week starts on Sunday and ends on Saturday.
Contain dates that fall during the following week	Year([SalesDate])* 53+DatePart("ww", [SalesDate]) = Year(Date())* 53+DatePart("ww", Date()) + 1	Returns records of transactions that will take place next week. A week starts on Sunday and ends on Saturday.
Contain a date that fell during the last 7 days	Between Date() and Date()-6	Returns records of transactions that took place during the last 7 days. If today's date is 2/2/2006, you see records for the period Jan 24, 2006 through Feb 2, 2006.
Contain a date that belongs to the current month	Year([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now())	Returns records for the current month. If today's date is 2/2/2006, you see records for Feb 2006.
Contain a date that belongs to the previous month	Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) - 1	Returns records for the previous month. If today's date is 2/2/2006, you see records for Jan 2006.
Contain a date that belongs to the next month	Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) + 1	Returns records for the next month. If today's date is 2/2/2006, you see records for Mar 2006.
Contain a date that fell during the last 30 or 31 days	Between Date( ) And DateAdd("M", -1, Date( ))	A month's worth of sales records. If today's date is 2/2/2006, you see records for the period Jan 2, 2006. to Feb 2, 2006
Contain a date that belongs to the current quarter	Year([SalesDate]) = Year(Now()) And DatePart("q", Date()) = DatePart("q", Now())	Returns records for the current quarter. If today's date is 2/2/2006, you see records for the first quarter of 2006.
Contain a date that belongs to the previous quarter	Year([SalesDate])*4+DatePart("q",[SalesDate]) = Year(Date())*4+DatePart("q",Date())- 1	Returns records for the previous quarter. If today's date is 2/2/2006, you see records for the last quarter of 2005.
Contain a date that belongs to the next quarter	Year([SalesDate])*4+DatePart("q",[SalesDate]) = Year(Date())*4+DatePart("q",Date())+1	Returns records for the next quarter. If today's date is 2/2/2006, you see records for the second quarter of 2006.
Contain a date that falls during the current year	Year([SalesDate]) = Year(Date())	Returns records for the current year. If today's date is 2/2/2006, you see records for the year 2006.
Contain a date that belongs to the previous year	Year([SalesDate]) = Year(Date()) - 1	Returns records of transactions that took place during the previous year. If today's date is 2/2/2006, you see records for the year 2005.
Contain a date that belongs to next year	Year([SalesDate]) = Year(Date()) + 1	Returns records of transactions with next year's date. If today's date is 2/2/2006, you see records for the year 2007.
Contain a date that falls between Jan 1 and today (year to date records)	Year([SalesDate]) = Year(Date()) and Month([SalesDate]) <= Month(Date()) and Day([SalesDate]) <= Day (Date())	Returns records of transactions with dates that fall between Jan 1 of the current year and today. If today's date is 2/2/2006, you see records for the period Jan 1, 2006 to to 2/2/2006.
Contain a date that occurred in the past	< Date()	Returns records of transactions that took place before today.
Contain a date that occurrs in the future	> Date()	Returns records of transactions that will take place after today.
Filter for null (or missing) values	Is Null	Returns records where the date of transaction is missing.
Filter for non-null values	Is Not Null	Returns records where the date of transaction is known.

Criteria for other fields

Yes/No fields   In the Criteria row, type Yes to include records where the check box is selected. Type No to include records where the check box is not selected.

Attachments   In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments.

Lookup fields   There are two types of Lookup fields: those that look up values in an existing data source (by using a foreign key), and those that are based on a list of values specified when the Lookup field is created.

Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are the same as for other text fields.

The criteria you can use in a Lookup field based on values from an existing datasource depend on the data type of the foreign key, rather than the data type of the data being looked up. For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type. Because the field stores a number instead of text, you use criteria that work for numbers; that is, >2.

If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:

1. Locate the source table in the Navigation Pane.
2. Open the table in Design view by either:
• Clicking the table, and then pressing CTRL+ENTER
• Right-clicking the table, and then clicking Design View.
3. The data type for each field is listed in the Data Type column of the table design grid.

Multivalued fields   Data in a multivalued field are stored as rows in a hidden table that Office Access 2007 creates and populates to represent the field. In query Design view, this is represented in the Field List by using an expandable field. To use criteria for a multivalued field, you supply criteria for a single row of the hidden table. To do this:

1. Create a query containing the multivalued field, and open it in Design view.
2. Expand the multivalued field by clicking the plus symbol (+) next to it — if the field is already expanded, this is a minus symbol (-). Just below the name of the field, you will see a field representing a single value of the multivalued field. This field will have the same name as the multivalued field, with the string .Valueappended.
3. Drag the multivalued field and its single value field to separate columns in the design grid. If you want to see only the complete multivalue field in your results, clear the Show check box for the single value field.
4. Type your criteria in the Criteria row for the single value field, using criteria that is appropriate for whatever type of data the values represent.
5. Each value in the multivalued field will be individually evaluated using the criteria you supply. For example, you may have a multivalued field that stores a list of numbers. If you supply the criteria >5 AND <3, any record where there is at least one value greater than 5 and one value less than 3 will match.

Reference http://office.microsoft.com/en-001/access-help/examples-of-query-criteria-HA010066611.aspx

For Edexcel Unit 7-Using Database Software MS Access classes

CALL        +94 777 33 7279

Email       ITClassSL@gmail.com

How to Automate Reports in Excel using VBA Macros

How to Automate Reports in Excel

One of the many features of Microsoft Excel is its ability to automate reports. You can create interactive spreadsheets to simplify others' ability to enter data into its workbooks, and you can also automate the generation of its reports. Both of these features require some knowledge of Visual Basic. The steps to perform both tasks are described below.

Method 1 of 2: Creating an Interactive Spreadsheet

1. 
   1
   Decide on the layout of your spreadsheet. Your spreadsheet should be laid out so that others using it can find the fields they need quickly to enter data.
   • You can lay out your spreadsheet horizontally or vertically. Most users will find a vertical layout easier to work with, particularly if you plan to have them print out the spreadsheet.
   Ad
2. 
   2
   Create text labels for the spreadsheet. You'll want a label at the top of each column, as well as a label in the cell to the left of each cell in the column where you plan to place the entry fields.
3. 
   3
   Press the Alt and F11 keys simultaneously. This opens the Microsoft Visual Basic editor.
4. 
   4
   Double-click "ThisWorkbook" in the "Project-VBA Project" pane in the upper left. This opens a coding window in the main section of the editor.
5. 
   5
   Select "Procedure" from the Insert menu. This displays the Add Procedure dialog box.
6. 
   6
   Enter a name for the procedure in the Name field. Give the procedure a meaningful name, such as "SumExpenses" if your interactive spreadsheet will be used for reporting travel expenses. Click OK to close the dialog.
   • Your procedure name cannot include spaces, but you can use an underscore ( _ ) in place of a space.
   • Once the Add Procedure dialog box closes, you will see a line labeled "Public Sub" followed by the name of your procedure. Beneath that line will be a space and the words "End Sub."
7. 7
   Enter code for each input field in the spreadsheet. You'll write two lines of code for each input.
   • The first line of code takes the form "Range("cellname").Select", where "cellname" represents the cell in which the input field will go. This should be the cell immediately to the right of a text label; if you have a text label in cell A2, you'd put an input field in cell B2 (Range("B2").Select). Include the quotes around the cell name, but not those around the complete code statement.
     
   • The second line of code takes the form "ActiveCell.Value = InputBox("InputPrompt")", where "InputPrompt" represents text that will appear to tell the user what kind of data to enter in the input cell. For example, if the input cell is to enter meal expenses, you'd replace "InputPrompt" with "Enter total of all meals, including tips." (Include the quotes around the input prompt text, but not those enclosing the entire command.)
     
8. 
   8
   Enter code for each calculation field. You again use the same two lines as described above, but this time your ActiveCell.Value is a calculation or numeric function, such as SUM, in place of the InputBox function used to display an input prompt.
9. 
   9
   Add a line of code to save your interactive spreadsheet. The format is "ActiveWorkbook.SaveAs Filename:="Filename.xls"," where "Filename" represents the name of your interactive spreadsheet. (Include the quotes around "Filename.xls," but not those around the complete example.)
   • If you have Excel 2007 or later, you can substitute the suffix ".xlsx" for ".xls," but if some of the people who will use your interactive spreadsheet have Excel 2003 or earlier, they won't be able to use the spreadsheet without a plug-in reader program.
10. 10
    Press the Alt and Q keys simultaneously. This closes the Visual Basic editor.
11. 
    11
    Press the Alt and F8 keys simultaneously. This opens the Macro dialog box.
12. 
    12
    Click the name of your procedure in the Macro list. If yours is the only procedure in the list, it will be selected automatically.
13. 
    13
    Click the Options button. You will be prompted to enter a keyboard character to use as a keyboard shortcut with the Ctrl key. Choose a meaningful letter not already used as a shortcut character, such as "e" for "entry."
14. 
    14
    Click "OK" to close the Macro Options dialog. You can now distribute your interactive spreadsheet to those who will use it. After opening it, they can use the shortcut key to enable entry and follow the prompts you created to fill in their data.

Method 2 of 2: Automating Report Generation

1. 1
   Make your report into a PivotTable. PivotTables are designed to summarize data to let you compare numbers and identify trends. Your PivotTable should be connected to data elsewhere in your spreadsheet or imported from a database.
2. 2
   Write a Visual Basic script to open and close the report. Your script must perform the functions listed below. Each function will be described followed by the code given in brackets to implement it. When you write the actual code, write it in a single block, substitute your own names for the example names, and do not include the brackets that enclose the entire example.
   • Open the spreadsheet in read-only mode. [DIM XLAppSet XLApp = CreateObject("Excel.App")xlapp.visible=falsexlapp.workbooks.open \\excelloc\filename.xls,3,]
   • Refresh the data and save the report, in this example as a PDF with a date stamp. [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, \\pdfloc\reportname_ & DatePart("yyyy,Now()) & "-" & Right("0" & DatePart("m",Now()),2) & "-" Right("0" & DatePart("d",Now()),2) & ".pdf"] If your output document is to be in a different format, substitute the correct extension for that format for ".pdf".
   • Close the spreadsheet without saving it, then close Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
   • Use ".xlsx" in place of ".xls" in the spreadsheet suffix if your spreadsheet was saved in Excel 2007 and later's XML-based format.
3. 3
   Write a batch script to start the Visual Basic script. This is necessary for the Visual Basic script to be run automatically. Without the batch script, the VB script must be run manually.
   • Your script would be in this format, substitution your own folder and filename for those given here, and omitting the brackets: [cscript /nologo \\fileloc\script.vbs]
4. 4
   Write a batch script to verify the output file exists as created. Your script must perform the functions described below. Each function will be followed by code given in brackets to implement it. When you write the actual code, write it in a single block, substitute your own names for the example names, and do not include the enclosing brackets.
   • Check to see that the output file exists. [For /f "tokens=2-4 delims=/ " %%a in ('date /t') do set rreport=reportname_%%c-%%a-%%b.pdf)] If the output file's format is not a PDF, substitute the correct file format for ".pdf" where given.
   • If the output file/report exists, email it to the people who need it. [If exist \\pdfloc\%rreport% ( sendemail -f sender@senderdomain.com -t recipient@recipientdomain.com -u Scheduled Report -m Report %%report% is attached. -a \pdfloc\%rreport% -s yourserver:port -xu username -xp password)]
   • If the output file/report doesn't exist at the specified location, have the procedure send you a message that delivery failed. [ Else ( sendemail -f sender@senderdomain.com -t sender@senderdomain.com -u Report did not run -m file %rreport% does not exist in \\pdfloc\ -s yourserver:port -xu username -xp password)]
5. 5
   Verify the "Desktop" folder exists on the computer. You have to verify the Desktop folder's existence for both a 32-bit and a 64-bit system. If you don't do this, Excel and your spreadsheet have to be opened manually.
   • 32-bit system location: c:\windows\system32\config\systemprofile
   • 64-bit system location: c:\windows\syswow64\config\systemprofile

Source http://www.wikihow.com/Automate-Reports-in-Excel

=====================================================================

The Project Window in the VBE of Excel

Note: Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in lesson 1.

As you can see, the Project window shows you all the workbooks that are open ("Book1") in the example below) and their components. You can use the + and - signs to show the details.

A new Excel workbook includes three sheets and another component named "ThisWorkbook". As we will see later  in lesson 9 on events "ThisWorkbook" is a component in which you will store the macros ( also called VBA procedures) that should start automatically when the workbook is opened.

Working within the Project Window

We will now complete a brief exercise to learn how easy it is to work within the Project Window.

Exercise 2 (Create your first macro and use it)

Step 1: Using the ALT/F11 key go back to Excel.

Step 2: Add a sheet. Right-click on the tab of Sheet2 and select "Insert". 

Step 3: In the dialog window that appears, click on "OK".

Step 4: Using the "ALT/F11" key, go back to the Visual Basic Editor and see that a sheet has been added to the workbook. Notice that the worksheets are sorted alphabetically in the Project window even if they are not in the workbook.

If you have purchased and downloaded the course on Excel Macros and opened the Excel file "vba-tutorial-editor.xls" plus a new workbook you will see this:

In the picture above you can see that the VBAProject named "Book1.xls" has 3 sheets and ThisWorkbook.The workbook "vba-tutorial-editor.xls" has 7 sheets, two userforms, two modules plus the "ThisWorkbook" object.

- Userforms are dialog windows (see example image below) that you develop to communicate with the users of your Excel programs and ask them to supply information or make choices.

- Modules are folders in which you save one or many of your macros. You can export and save these modules to be used later in other workbook.

In the complete lesson 2 you will learn how to add any type of components and how to remove, import, export and manage them from the Project window.

Close the VBE and close Excel without saving anything.

---

Here is a sample of what you will find in chapter  3
of the downloadable Tutorial on Excel macros

The Properties Window in the VBE of Excel

Note: Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in lesson 1.

The Properties window shows you the properties of the component that is selected in the Project Window (single click).  For example in the new workbook if you single click on "Sheet1" in the Project Window you see the properties of sheet1 in the Properties Window like in the image below.

As you can see, a worksheet has 12 properties that you can change in this Properties window. Notice that there are 2 "Name" properties. On the first line there is the programmatical name of the sheet (Sheet1). You will discover later the advantages and disadvantages of changing this property. The second "Name" property (9th line) is the name (or caption) that appears on the tab of the sheet in Excel.

Changing the "Name" Property

Ref http://www.excel-vba.com/excel-vba-solutions-beginners.htm

=======================================================================
Excel VBA (Visual Basic for Applications) is the name of the programming language of Excel.

1 Create a Macro: With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro.

2 MsgBox: The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program.

3 Workbook and Worksheet Object: Learn more about the Workbook and Worksheet object in Excel VBA.

4 Range Object: The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA.

5 Variables: This chapter teaches you how to declare, initialize and display a variable in Excel VBA.

6 If Then Statement: Use the If Then statement in Excel VBA to execute code lines if a specific condition is met.

7 Loop: Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.

8 Macro Errors: This chapter teaches you how to deal with macro errors in Excel.

9 String Manipulation: In this chapter, you'll find the most important functions to manipulate strings in Excel VBA.

10 Date and Time: Learn how to work with dates and times in Excel VBA.

11 Events: Events are actions performed by users which trigger Excel VBA to execute code.

12 Array: An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.

13 Function and Sub: In Excel VBA, a function can return a value while a sub cannot.

14 Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

15 ActiveX Controls: Learn how to create ActiveX controls such as command buttons, text boxes, list boxes etc.

16 Userform: This chapter teaches you how to create an Excel VBA Userform.

Ref http://www.excel-easy.com/vba.html

Call         +94 777 33 7279
Email      ITClassSL@gmail.com

Will provide MS Excel report automation creating charts reports, MS Outlook automation and classes

Creating OOP open source PHP mySQL Projects using eclips

Creating PHP Projects

PHP projects are the containers within which all PHP and other application files should be created.

	To create a new PHP project: From the menu bar, go to File | New | PHP Project -Or- In PHP Explorer view, right-click and select New | PHP Project. The New PHP Project wizard is displayed. Enter the following information: Project name - The required project name Contents - Select whether to: Create a new project in the workspace - Creates a new PHP project in the workspace directory. Create a project from existing source - Creates a PHP project pointing to files situated outside of the workspace. Click Browse to select the required source content. PHP Version - Select whether to: Use default PHP settings - Uses the default PHP Interpreter settings. Use project specific settings - Select the PHP version to be used for the project and whether ASP tags are used as PHP tags. See PHP Version Support for more information Project Layout - Select whether to: Use project as source folder - All resources within the project will be added to the Build Path by default. Create separate folders for source files and public resources - Separate folders will be created in which you can place resources which should be included or excluded from the Build Path. See Configuring a Project's PHP Build Path for more information. The default setting for this option can be configured from the New Project Layout Preferences page. JavaScript Support - Mark the 'Enable JavaScript support for this project' checkbox for JavaScript functionality (e.g. JavaScript Content Assist options) to be available to the project. See Enabling JavaScript Support in PHP Projects for more information. Click Next to configure the project's Include Path (this can also be done following the project creation). Click Next to configure the project's PHP Build Path (this can also be done following project creation). Click Finish.
The new PHP project will be created in your workspace and displayed in PHP Explorer View. You can now start to develop your application by creating PHP Files or adding other resources to your project. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Configuring a Project's PHP Build Path By configuring your project's PHP Build Path, you can select resources which will be included or excluded from the Build process. Note: If during project creation you selected the 'use project as source folder' under the Project Layout category, all resources within the folder will be added to the Build Path by default. If you selected to 'create separate folders for source files and public resources' during the project's creation, resources in the 'public' folder will be excluded from the Build Path by default. The project's PHP Build Path can be configured through the project's Build Path properties page, accessed by right-clicking the required project and selecting Build Path | Configure Build Path. This page allows you to add folders and resources to your Build Path and configure rules for including / excluding certain resources in the project. You can configure your Build Path according to the options listed below. Once you have made changes, click OK to save. Note: By default, all folders which are on your Build Path will be included in your Include Path. Configuring Inclusion/Exclusion Patterns for the Project By configuring inclusion/exclusion patterns for the Build Path, you can select to include or exclude all resources in your project which match a defined pattern. To include/exclude resources from the Build Path: In the Build Path Properties page, expand the node next to the folder whose Inclusion/Exclusion pattern you want to configure and click Edit. -Or- In PHP Explorer view, right-click the folder whose Inclusion/Exclusion pattern you want to configure and select Build Path | Configure Inclusion / Exclusion Filters. The Inclusion and Exclusion patterns dialog is displayed. Click Add next to the Inclusion or Exclusion patterns panes. The Add Inclusion/Exclusion Pattern dialog is displayed. Enter or select the required resource(s) or pattern to include / exclude and click OK. Click Finish. All resources in the project which match an inclusion pattern but do not match an exclusion pattern will be added to the Build Path. Configuring Different Inclusion/Exclusion Patterns for Folders Within your Project You can configure different inclusion/exclusion rules for child folders within a selected directory. However, you will need to exclude the selected child folder from the parent directory's Build Path and add it as a separate Build Path source folder. This is done to resolve any conflicts which may arise from configuring one pattern for the parent folder and another for the child. Source folders can be created and/or added from the PHP Build Path properties page or from PHP Explorer view. To add a directory as a separate source folder: From the PHP Build Path Properties page: Click Add Folder... The Source Folder Selection dialog will display. If necessary, click Create New Folder to create a new folder. Select the required folder and click OK. The folder will be added as a separate source folder in the Build Path list. Note: In order to overcome the nesting conflict, you should exclude the folder you have just added from the parent folder's Build Path. See 'Configuring Inclusion/Exclusion Patterns', above, for information on how to exclude the folder and all resources contained within it. From PHP Explorer view: Right-click the project and select Build Path | New Source Folder. The New Source Folder dialog will be displayed. Enter the name for the new folder in the Folder name field. Adding a folder to a project's Build Path could cause a conflict between the inclusion/exclusion patterns configured for the project root and those configured for the selected folder. In order to avoid these nesting conflicts, select one of the following options: Replace existing project source folder entry to solve nesting - This will remove your parent directory as a source folder from the Build Path configuration and replace it with the currently selected folder. Update exclusion filters in other source folders to solve nesting - This will exclude the currently selected folder from the parent directory's Build Path but will add it as a separate entity so that different inclusion/exclusion patters can be configured for it. Click Finish. You can now configure a pattern for including/excluding resources for the source folder by following the instructions under 'Configuring Inclusion/Exclusion Patterns', above. Adding External Source Folders to the Build Path You can add an external source folder as a link to your project which will be scanned during the Build process. To add an external source folder to the project's Build Path: In the Build Path Properties page, click the 'Link Source...' button. -Or- In PHP Explorer view, right-click the project and select Build Path | Link Source. The Link Source dialog is displayed. Click the 'Browse..' button and browse to the location of the folder you want to add to the Build Path. Alternately, click Variables and select the variable which points to the required resource. The Folder name field will have been automatically populated with the name of the original folder. Edit this entry if required. Adding a folder to a project's Build Path could cause a conflict between the inclusion/exclusion patterns configured for the project root and those configured for the selected folder. In order to avoid these nesting conflicts, select one of the following options: Replace existing project source folder entry to solve nesting - This will remove your parent directory as a source folder from the Build Path configuration and replace it with the currently selected folder. Update exclusion filters in other source folders to solve nesting - This will exclude the currently selected folder from the parent directory's Build Path but will add it as a separate entity so that different inclusion/exclusion patters can be configured for it. Ignore nesting conflicts - The selected folder will be added as a separate entity to the Build Path list but will need to be manually excluded from the project root's Build Path to avoid nesting conflicts. Click Finish. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> New Project Layout Preferences The New Project Layout Preferences page allows you to configure the default layout for new PHP projects. The default layout will configure whether all folders under the project root will be considered as 'source' folders for the Build process, or whether separate folders will be created for resources which are included or excluded from the Build process. The preferences configured in the New Project Layout Preferences page will affect the default option selected in the Project Layout category of the New PHP Project wizard. This setting can be modified per project during creation. The New Project Layout Preferences page is accessed from Window | Preferences | PHP | New Project Layout Preferences . To configure the default New PHP Project Layout: Select the required option: Use project as source folder - By default, all folders created under the project root will be considered 'source folders' and will be scanned during the Build process. See Configuring a Project's PHP Build Path to configure the project's PHP Build Path. Create separate folders for source files and public resources - Two folders will be created under your project root - one 'source' folder for resources which will be scanned during the Build process and one 'public' folder which will be skipped during the Build process. You can change the default names for these folders by entering the required name in the 'Default source/public folder name' fields. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Enabling JavaScript Support in PHP Projects Enabling JavaScript support in PHP projects allows JavaScript libraries and external files to be referenced by the project . Note: Once JavaScript support has been enabled for a project, you should set the project's JavaScript Build Path in order for the required resources to be made available to the project. These procedures describe how to enable JavaScript support for new PHP projects, add support to existing projects, or disable JavaScript support. Enabling JavaScript Support for New PHP Projects To enable JavaScript support in new PHP Projects: Go to File Menu and select New | PHP Project. -Or- In PHP Explorer view, right-click and select New | PHP Project. The new PHP Project wizard will launch. Enter the required information in the various fields. To enable JavaScript support, mark the 'Enable JavaScript support for this project' checkbox. Click Finish. A new PHP Project will be created with full JavaScript support. Enabling JavaScript Support for Existing PHP Projects JavaScript libraries and features can be added to existing PHP projects in your workspace. To enable JavaScript Support for existing PHP Projects: In PHP Explorer view, right-click the project for which you want to enable JavaScript support and select Configure | Add Java Script Support. JavaScript support will be enabled for the project. Removing JavaScript Support If you are not using JavaScript libraries or files in your project, you can remove JavaScript support for that project. To remove JavaScript Support for existing PHP Projects: In PHP Explorer view, right-click the project for which you want to enable JavaScript support and select Configure | Remove JavaScript Support. JavaScript support will be removed from the project and no JavaScript libraries or external files will be available to the project. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Configuring a Project's PHP Include Path Adding external resources to a project's include path makes resources outside the project available for operations such as debugging and content assist. See PHP Include Paths for more information. This procedure describes how to configure your project's Include Path. To configure your project's Include Path: In PHP Explorer view, right-click the required project and select Include Path | Configure Include Path... -Or- right-click the project and select Properties | Include Path. The project's PHP Include Path properties page will appear. From this page you can configure the following: To configure source folders on your Include Path: Select the Source tab. By default, all folders which are on your Include Path will be added to your Build Path. Note: If your project root is on your Include Path, all folders and resources contained within your project will also be on the Include Path. To add only certain folders, remove the project root from the Include Path and add only the required folders. To remove a folder, select it and click Remove. To add a folder from your project, click the Add Folder button and select the required folder. A prompt will display, asking whether you would also like to add the selected folder(s) to your Build Path. It is recommended that the resources on your Build Path match the resources on your Include Path. Click Yes to add the folder(s) to your Build Path or No for your Build Path to not be affected. To add another project from your workspace to your Include Path: Select the "Projects tab". Click Add. The Required Project Selection dialog appears. Select the projects you would like to add and click OK. The selected project(s) will be added to your project's Include Path. To add a library to your Include Path: Select the "Libraries tab". Click Add Library. The Add Library dialog appears. Select the required Library (if available) and click OK. To add external folders to your Include Path: Select the "Libraries tab". Click Add External Folder. The Add Include Path dialog appears. Browse to and select the required folder. Click OK. The folder will be added to your project's Include Path. Note: The library is a read-only file and will not be available for editing. To configure the order of elements on your Include Path: Select the "Order tab". This determines the order in which resources will be searched for in require/include calls. See Include Paths for more information on the order for which files are searched for. If applicable, it is recommended that elements appear in the same order as they do in your php.ini. If necessary, rearrange the order of the entries. To do so, select an element and click Up or Down to move it in the list. Once all the elements are added and are in the right order, click OK. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Configuring a Project's PHP Build Path By configuring your project's PHP Build Path, you can select resources which will be included or excluded from the Build process. Note: If during project creation you selected the 'use project as source folder' under the Project Layout category, all resources within the folder will be added to the Build Path by default. If you selected to 'create separate folders for source files and public resources' during the project's creation, resources in the 'public' folder will be excluded from the Build Path by default. The project's PHP Build Path can be configured through the project's Build Path properties page, accessed by right-clicking the required project and selecting Build Path | Configure Build Path. This page allows you to add folders and resources to your Build Path and configure rules for including / excluding certain resources in the project. You can configure your Build Path according to the options listed below. Once you have made changes, click OK to save. Note: By default, all folders which are on your Build Path will be included in your Include Path. Configuring Inclusion/Exclusion Patterns for the Project By configuring inclusion/exclusion patterns for the Build Path, you can select to include or exclude all resources in your project which match a defined pattern. To include/exclude resources from the Build Path: In the Build Path Properties page, expand the node next to the folder whose Inclusion/Exclusion pattern you want to configure and click Edit. -Or- In PHP Explorer view, right-click the folder whose Inclusion/Exclusion pattern you want to configure and select Build Path | Configure Inclusion / Exclusion Filters. The Inclusion and Exclusion patterns dialog is displayed. Click Add next to the Inclusion or Exclusion patterns panes. The Add Inclusion/Exclusion Pattern dialog is displayed. Enter or select the required resource(s) or pattern to include / exclude and click OK. Click Finish. All resources in the project which match an inclusion pattern but do not match an exclusion pattern will be added to the Build Path. Configuring Different Inclusion/Exclusion Patterns for Folders Within your Project You can configure different inclusion/exclusion rules for child folders within a selected directory. However, you will need to exclude the selected child folder from the parent directory's Build Path and add it as a separate Build Path source folder. This is done to resolve any conflicts which may arise from configuring one pattern for the parent folder and another for the child. Source folders can be created and/or added from the PHP Build Path properties page or from PHP Explorer view. To add a directory as a separate source folder: From the PHP Build Path Properties page: Click Add Folder... The Source Folder Selection dialog will display. If necessary, click Create New Folder to create a new folder. Select the required folder and click OK. The folder will be added as a separate source folder in the Build Path list. Note: In order to overcome the nesting conflict, you should exclude the folder you have just added from the parent folder's Build Path. See 'Configuring Inclusion/Exclusion Patterns', above, for information on how to exclude the folder and all resources contained within it. From PHP Explorer view: Right-click the project and select Build Path | New Source Folder. The New Source Folder dialog will be displayed. Enter the name for the new folder in the Folder name field. Adding a folder to a project's Build Path could cause a conflict between the inclusion/exclusion patterns configured for the project root and those configured for the selected folder. In order to avoid these nesting conflicts, select one of the following options: Replace existing project source folder entry to solve nesting - This will remove your parent directory as a source folder from the Build Path configuration and replace it with the currently selected folder. Update exclusion filters in other source folders to solve nesting - This will exclude the currently selected folder from the parent directory's Build Path but will add it as a separate entity so that different inclusion/exclusion patters can be configured for it. Click Finish. You can now configure a pattern for including/excluding resources for the source folder by following the instructions under 'Configuring Inclusion/Exclusion Patterns', above. Adding External Source Folders to the Build Path You can add an external source folder as a link to your project which will be scanned during the Build process. To add an external source folder to the project's Build Path: In the Build Path Properties page, click the 'Link Source...' button. -Or- In PHP Explorer view, right-click the project and select Build Path | Link Source. The Link Source dialog is displayed. Click the 'Browse..' button and browse to the location of the folder you want to add to the Build Path. Alternately, click Variables and select the variable which points to the required resource. The Folder name field will have been automatically populated with the name of the original folder. Edit this entry if required. Adding a folder to a project's Build Path could cause a conflict between the inclusion/exclusion patterns configured for the project root and those configured for the selected folder. In order to avoid these nesting conflicts, select one of the following options: Replace existing project source folder entry to solve nesting - This will remove your parent directory as a source folder from the Build Path configuration and replace it with the currently selected folder. Update exclusion filters in other source folders to solve nesting - This will exclude the currently selected folder from the parent directory's Build Path but will add it as a separate entity so that different inclusion/exclusion patters can be configured for it. Ignore nesting conflicts - The selected folder will be added as a separate entity to the Build Path list but will need to be manually excluded from the project root's Build Path to avoid nesting conflicts. Click Finish. Why you Should be using PHP's PDO for Database Access ?? PDO Introduction "PDO - PHP Data Objects - is a database access layer providing a uniform method of access to multiple databases." It doesn't account for database-specific syntax, but can allow for the process of switching databases and platforms to be fairly painless, simply by switching the connection string in many instances. This tutorial isn't meant to be a complete how-to on SQL. It's written primarily for people currently using the mysql or mysqli extension to help them make the jump to the more portable and powerful PDO. Read More On http://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059 Refrences  https://www.eclipse.org/resources/resource.php?id=543 For PHP/mySQL Classes Projects Assignments CALL +94 777 33 7279 Email   ITClassSL@gmail.com