Letter of Authorization

Letter of Authorization

To,

<who>

<address>

I hereby authorize ________________I/C NO:________________, my representative to collect <Item Description> on my behalf.

Thanks and Regards,

_________________

(Name)

I/C No:

H/P No:

Programming Language

C
C++
Java
Ruby
PHP
ASP.NET
SQL
Perl 
Javascript
CSS
FORTRAN
Modula2
COBOL
LISP
BASIC
FORTH
APL
Pascal
SNOBOL
Concurrent Euclid
HyperTalk
Motif
Unix
 Paradox
Revelation
Visual Basic
Prolog
Ada
Assembly
370 JCL
Python



 

What is DAO?

来源：

为了建立一个健壮的J2EE应用，将所有对数据源的访问操作抽象封装在一个公共API中（建立一个接口）。
用户需要通过这接口，来进行和数据源的所有事务、交互。

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

• DAO (Data Access Objects）（数据访问对象）是一种应用程序编程接口（API）。
• DAO是程序员访问数据库【Access数据库、其他的结构化查询语言（SQL）数据库】的 第一个面向对象的数据库接口。
• 它夹在业务逻辑与数据库资源中间。 
• 它显露 Microsoft Jet数据库“引擎”（由 Microsoft Access 所使用），并允许开发者通过 ODBC 象直接连接到其他数据库一样，直接连接到 数据库表。
• DAO 最适用于单系统应用程序或小范围本地分布使用。
• DAO是Data Access Object数据访问接口，数据访问：顾名思义就是与数据库打交道。夹在业务逻辑与数据库资源中间。 

开发人员使用数据访问对象（DAO）设计模式把底层的数据访问逻辑和高层的商务逻辑分开。

如何设计和实现数据访问对象？

• 哪些是事务性对象？
• 事务划分（transaction demarcation)包括：-
• 编程性事务（programmatic）:  程序员担负编写事务逻辑代码的责任。 
• 声明性事务（declarative） : 程序员使用EJB的部署描述符声明事务属性。

DAO模式是标准的J2EE设计模式之一.开发人员使用这个模式把底层的数据访问操作和上层的商务逻辑分开。

一个典型的DAO实现有下列几个组件：

1. 一个DAO工厂类  (DaoFactory @ ConnectionFactory Class)
2. 一个DAO接口      (Dao Interface)
3. 一个实现DAO接口的具体类 (Implementation Class)
4. 数据传递对象（值对象）

需知相关信息：

1. 数据访问对象DAO的结构 
2. 数据访问对象DAO的功能
3. 实战应用——使用数据访问对象DAO 

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。 Example  (Visual Basic): How to use the DAO library to create, delete, modify and list the objects in Access.

[from: http://allenbrowne.com/func-dao.html]

Option Compare Database
Option Explicit

'Constants for examining how a field is indexed.
Private Const intcIndexNone As Integer = 0
Private Const intcIndexGeneral As Integer = 1
Private Const intcIndexUnique As Integer = 3
Private Const intcIndexPrimary As Integer = 7

Function CreateTableDAO()
    'Purpose:   Create two tables using DAO.
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    'Initialize the Contractor table.
    Set db = CurrentDb()
    Set tdf = db.CreateTableDef("tblDaoContractor")
    
    'Specify the fields.
    With tdf
        'AutoNumber: Long with the attribute set.
        Set fld = .CreateField("ContractorID", dbLong)
        fld.Attributes = dbAutoIncrField + dbFixedField
        .Fields.Append fld
        
        'Text field: maximum 30 characters, and required.
        Set fld = .CreateField("Surname", dbText, 30)
        fld.Required = True
        .Fields.Append fld
        
        'Text field: maximum 20 characters.
        .Fields.Append .CreateField("FirstName", dbText, 20)
        
        'Yes/No field.
        .Fields.Append .CreateField("Inactive", dbBoolean)
        
        'Currency field.
        .Fields.Append .CreateField("HourlyFee", dbCurrency)
        
        'Number field.
        .Fields.Append .CreateField("PenaltyRate", dbDouble)
        
        'Date/Time field with validation rule.
        Set fld = .CreateField("BirthDate", dbDate)
        fld.ValidationRule = "Is Null Or <=Date()"
        fld.ValidationText = "Birth date cannot be future."
        .Fields.Append fld
        
        'Memo field.
        .Fields.Append .CreateField("Notes", dbMemo)
        
        'Hyperlink field: memo with the attribute set.
        Set fld = .CreateField("Web", dbMemo)
        fld.Attributes = dbHyperlinkField + dbVariableField
        .Fields.Append fld
    End With
    
    'Save the Contractor table.
    db.TableDefs.Append tdf
    Set fld = Nothing
    Set tdf = Nothing
    Debug.Print "tblDaoContractor created."
    
    'Initialize the Booking table
    Set tdf = db.CreateTableDef("tblDaoBooking")
    With tdf
        'Autonumber
        Set fld = .CreateField("BookingID", dbLong)
        fld.Attributes = dbAutoIncrField + dbFixedField
        .Fields.Append fld
        
        'BookingDate
        .Fields.Append .CreateField("BookingDate", dbDate)
        
        'ContractorID
        .Fields.Append .CreateField("ContractorID", dbLong)
        
        'BookingFee
        .Fields.Append .CreateField("BookingFee", dbCurrency)
        
        'BookingNote: Required.
        Set fld = .CreateField("BookingNote", dbText, 255)
        fld.Required = True
        .Fields.Append fld
    End With
    
    'Save the Booking table.
    db.TableDefs.Append tdf
    Set fld = Nothing
    Set tdf = Nothing
    Debug.Print "tblDaoBooking created."
    
    'Clean up
    Application.RefreshDatabaseWindow   'Show the changes
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Function ModifyTableDAO()
    'Purpose:   How to add and delete fields to existing tables.
    'Note:      Requires the table created by CreateTableDAO() above.
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    'Initialize
    Set db = CurrentDb()

    Set tdf = db.TableDefs("tblDaoContractor")
    
    'Add a field to the table.
    tdf.Fields.Append tdf.CreateField("TestField", dbText, 80)
    Debug.Print "Field added."
    
    'Delete a field from the table.
    tdf.Fields.Delete "TestField"
    Debug.Print "Field deleted."
    
    'Clean up
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Function DeleteTableDAO()
    DBEngine(0)(0).TableDefs.Delete "DaoTest"
End Function

Function MakeGuidTable()
    'Purpose:   How to create a table with a GUID field.
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property

    Set db = CurrentDb()
    Set tdf = db.CreateTableDef("Table8")
    With tdf
        Set fld = .CreateField("ID", dbGUID)
        fld.Attributes = dbFixedField
        fld.DefaultValue = "GenGUID()"
        .Fields.Append fld
    End With
    db.TableDefs.Append tdf
End Function

Function CreateIndexesDAO()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim ind As DAO.Index
    
    'Initialize
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblDaoContractor")
    
    '1. Primary key index.
    Set ind = tdf.CreateIndex("PrimaryKey")
    With ind
        .Fields.Append .CreateField("ContractorID")
        .Unique = False
        .Primary = True
    End With
    tdf.Indexes.Append ind
    
    '2. Single-field index.
    Set ind = tdf.CreateIndex("Inactive")
    ind.Fields.Append ind.CreateField("Inactive")
    tdf.Indexes.Append ind
    
    '3. Multi-field index.
    Set ind = tdf.CreateIndex("FullName")
    With ind
        .Fields.Append .CreateField("Surname")
        .Fields.Append .CreateField("FirstName")
    End With
    tdf.Indexes.Append ind
    
    'Refresh the display of this collection.
    tdf.Indexes.Refresh
    
    'Clean up
    Set ind = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Debug.Print "tblDaoContractor indexes created."
End Function

Function DeleteIndexDAO()
    DBEngine(0)(0).TableDefs("tblDaoContractor").Indexes.Delete "Inactive"
End Function

Function CreateRelationDAO()
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    
    'Initialize
    Set db = CurrentDb()
    
    'Create a new relation.
    Set rel = db.CreateRelation("tblDaoContractortblDaoBooking")
    
    'Define its properties.
    With rel
        'Specify the primary table.
        .Table = "tblDaoContractor"
        'Specify the related table.
        .ForeignTable = "tblDaoBooking"
        'Specify attributes for cascading updates and deletes.
        .Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
        
        'Add the fields to the relation.
        'Field name in primary table.
        Set fld = .CreateField("ContractorID")
        'Field name in related table.
        fld.ForeignName = "ContractorID"
        'Append the field.
        .Fields.Append fld
        
        'Repeat for other fields if a multi-field relation.
    End With
    
    'Save the newly defined relation to the Relations collection.
    db.Relations.Append rel
    
    'Clean up
    Set fld = Nothing
    Set rel = Nothing
    Set db = Nothing
    Debug.Print "Relation created."
End Function

Function DeleteRelationDAO()
    DBEngine(0)(0).Relations.Delete "tblDaoContractortblDaoBooking"
End Function

Function DeleteQueryDAO()
    DBEngine(0)(0).QueryDefs.Delete "qryDaoBooking"
End Function

Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As Integer, _
    varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
    'Purpose:   Set a property for an object, creating if necessary.
    'Arguments: obj = the object whose property should be set.
    '           strPropertyName = the name of the property to set.
    '           intType = the type of property (needed for creating)
    '           varValue = the value to set this property to.
    '           strErrMsg = string to append any error message to.
    
    If HasProperty(obj, strPropertyName) Then
        obj.Properties(strPropertyName) = varValue
    Else
        obj.Properties.Append obj.CreateProperty(strPropertyName, intType, varValue)
    End If
    SetPropertyDAO = True

ExitHandler:
    Exit Function

ErrHandler:
    strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to " & varValue & _
        ". Error " & Err.Number & " - " & Err.Description & vbCrLf
    Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
    'Purpose:   Return true if the object has the property.
    Dim varDummy As Variant
    
    On Error Resume Next
    varDummy = obj.Properties(strPropName)
    HasProperty = (Err.Number = 0)
End Function

Function StandardProperties(strTableName As String)
    'Purpose:   Properties you always want set by default:
    '           TableDef:        Subdatasheets off.
    '           Numeric fields:  Remove Default Value.
    '           Currency fields: Format as currency.
    '           Yes/No fields:   Display as check box. Default to No.
    '           Text/memo/hyperlink: AllowZeroLength off,
    '                                UnicodeCompression on.
    '           All fields:      Add a caption if mixed case.
    'Argument:  Name of the table.
    'Note:      Requires: SetPropertyDAO()
    Dim db As DAO.Database      'Current database.
    Dim tdf As DAO.TableDef     'Table nominated in argument.
    Dim fld As DAO.Field        'Each field.
    Dim strCaption As String    'Field caption.
    Dim strErrMsg As String     'Responses and error messages.
    
    'Initalize.
    Set db = CurrentDb()
    Set tdf = db.TableDefs(strTableName)
    
    'Set the table's SubdatasheetName.
    Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
        strErrMsg)
    
    For Each fld In tdf.Fields
        'Handle the defaults for the different field types.
        Select Case fld.Type
        Case dbText, dbMemo 'Includes hyperlinks.
            fld.AllowZeroLength = False
            Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean, _
                True, strErrMsg)
        Case dbCurrency
            fld.DefaultValue = 0
            Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
                strErrMsg)
        Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
            fld.DefaultValue = vbNullString
        Case dbBoolean
            Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
                CInt(acCheckBox))
        End Select
        
        'Set a caption if needed.
        strCaption = ConvertMixedCase(fld.Name)
        If strCaption <> fld.Name Then
            Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
        End If
        
        'Set the field's Description.
        Call SetFieldDescription(tdf, fld, , strErrMsg)
    Next
    
    'Clean up.
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
    If Len(strErrMsg) > 0 Then
        Debug.Print strErrMsg
    Else
        Debug.Print "Properties set for table " & strTableName
    End If
End Function

Function ConvertMixedCase(ByVal strIn As String) As String
    'Purpose:   Convert mixed case name into a name with spaces.
    'Argument:  String to convert.
    'Return:    String converted by these rules:
    '           1. One space before an upper case letter.
    '           2. Replace underscores with spaces.
    '           3. No spaces between continuing upper case.
    'Example:   "FirstName" or "First_Name" => "First Name".
    Dim lngStart As Long        'Loop through string.
    Dim strOut As String        'Output string.
    Dim boolWasSpace As Boolean 'Last char. was a space.
    Dim boolWasUpper As Boolean 'Last char. was upper case.
    
    strIn = Trim$(strIn)        'Remove leading/trailing spaces.
    boolWasUpper = True         'Initialize for no first space.
    
    For lngStart = 1& To Len(strIn)
        Select Case Asc(Mid(strIn, lngStart, 1&))
        Case vbKeyA To vbKeyZ   'Upper case: insert a space.
            If boolWasSpace Or boolWasUpper Then
                strOut = strOut & Mid(strIn, lngStart, 1&)
            Else
                strOut = strOut & " " & Mid(strIn, lngStart, 1&)
            End If
            boolWasSpace = False
            boolWasUpper = True
            
        Case 95                 'Underscore: replace with space.
            If Not boolWasSpace Then
                strOut = strOut & " "
            End If
            boolWasSpace = True
            boolWasUpper = False
            
        Case vbKeySpace         'Space: output and set flag.
            If Not boolWasSpace Then
                strOut = strOut & " "
            End If
            boolWasSpace = True
            boolWasUpper = False
            
        Case Else               'Any other char: output.
            strOut = strOut & Mid(strIn, lngStart, 1&)
            boolWasSpace = False
            boolWasUpper = False
        End Select
    Next
    
    ConvertMixedCase = strOut
End Function

Function SetFieldDescription(tdf As DAO.TableDef, fld As DAO.Field, _
Optional ByVal strDescrip As String, Optional strErrMsg As String) _
As Boolean
    'Purpose:   Assign a Description to a field.
    'Arguments: tdf = the TableDef the field belongs to.
    '           fld = the field to document.
    '           strDescrip = The description text you want.
    '                        If blank, uses Caption or Name of field.
    '           strErrMsg  = string to append any error messages to.
    'Notes:     Description includes field size, validation,
    '               whether required or unique.
    
    If (fld.Attributes And dbAutoIncrField) > 0& Then
        strDescrip = strDescrip & " Automatically generated " & _
            "unique identifier for this record."
    Else
        'If no description supplied, use the field's Caption or Name.
        If Len(strDescrip) = 0& Then
            If HasProperty(fld, "Caption") Then
                If Len(fld.Properties("Caption")) > 0& Then
                    strDescrip = fld.Properties("Caption") & "."
                End If
            End If
            If Len(strDescrip) = 0& Then
                strDescrip = fld.Name & "."
            End If
        End If
        
        'Size of the field.
        'Ignore Date, Memo, Yes/No, Currency, Decimal, GUID,
        '   Hyperlink, OLE Object.
        Select Case fld.Type
        Case dbByte, dbInteger, dbLong
            strDescrip = strDescrip & " Whole number."
        Case dbSingle, dbDouble
            strDescrip = strDescrip & " Fractional number."
        Case dbText
            strDescrip = strDescrip & " " & fld.Size & "-char max."
        End Select
        
        'Required and/or Unique?
        'Check for single-field index, and Required property.
        Select Case IndexOnField(tdf, fld)
        Case intcIndexPrimary
            strDescrip = strDescrip & " Required. Unique."
        Case intcIndexUnique
            If fld.Required Then
                strDescrip = strDescrip & " Required. Unique."
            Else
                strDescrip = strDescrip & " Unique."
            End If
        Case Else
            If fld.Required Then
                strDescrip = strDescrip & " Required."
            End If
        End Select
        
        'Validation?
        If Len(fld.ValidationRule) > 0& Then
            If Len(fld.ValidationText) > 0& Then
                strDescrip = strDescrip & " " & fld.ValidationText
            Else
                strDescrip = strDescrip & " " & fld.ValidationRule
            End If
        End If
    End If
    
    If Len(strDescrip) > 0& Then
        strDescrip = Trim$(Left$(strDescrip, 255&))
        SetFieldDescription = SetPropertyDAO(fld, "Description", _
            dbText, strDescrip, strErrMsg)
    End If
End Function

Private Function IndexOnField(tdf As DAO.TableDef, fld As DAO.Field) _
As Integer
    'Purpose:   Indicate if there is a single-field index _
    '               on this field in this table.
    'Return:    The constant indicating the strongest type.
    Dim ind As DAO.Index
    Dim intReturn As Integer
    
    intReturn = intcIndexNone
    
    For Each ind In tdf.Indexes
        If ind.Fields.Count = 1 Then
            If ind.Fields(0).Name = fld.Name Then
                If ind.Primary Then
                    intReturn = (intReturn Or intcIndexPrimary)
                ElseIf ind.Unique Then
                    intReturn = (intReturn Or intcIndexUnique)
                Else
                    intReturn = (intReturn Or intcIndexGeneral)
                End If
            End If
        End If
    Next
    
    'Clean up
    Set ind = Nothing
    IndexOnField = intReturn
End Function

Function CreateQueryDAO()
    'Purpose:   How to create a query
    'Note:      Requires a table named MyTable.
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb()
    
    'The next line creates and automatically appends the QueryDef.
    Set qdf = db.CreateQueryDef("qryMyTable")
    
    'Set the SQL property to a string representing a SQL statement.
    qdf.SQL = "SELECT MyTable.* FROM MyTable;"
    
    'Do not append: QueryDef is automatically appended!

    Set qdf = Nothing
    Set db = Nothing
    Debug.Print "qryMyTable created."
End Function

Function CreateDatabaseDAO()
    'Purpose:   How to create a new database and set key properties.
    Dim dbNew As DAO.Database
    Dim prp As DAO.Property
    Dim strFile As String
    
    'Create the new database.
    strFile = "C:\SampleDAO.mdb"
    Set dbNew = DBEngine(0).CreateDatabase(strFile, dbLangGeneral)
    
    'Create example properties in new database.
    With dbNew
        Set prp = .CreateProperty("Perform Name AutoCorrect", dbLong, 0)
        .Properties.Append prp
        Set prp = .CreateProperty("Track Name AutoCorrect Info", _
            dbLong, 0)
        .Properties.Append prp
    End With
    
    'Clean up.
    dbNew.Close
    Set prp = Nothing
    Set dbNew = Nothing
    Debug.Print "Created " & strFile
End Function

Function ShowDatabaseProps()
    'Purpose:   List the properies of the current database.
    Dim db As DAO.Database
    Dim prp As DAO.Property
    
    Set db = CurrentDb()
    For Each prp In db.Properties
        Debug.Print prp.Name
    Next
    
    Set db = Nothing
End Function

Function ShowFields(strTable As String)
    'Purpose:   How to read the fields of a table.
    'Usage:     Call ShowFields("Table1")
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set db = CurrentDb()
    Set tdf = db.TableDefs(strTable)
    For Each fld In tdf.Fields
        Debug.Print fld.Name, FieldTypeName(fld)
    Next
    
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Function ShowFieldsRS(strTable)
    'Purpose:   How to read the field names and types from a table or query.
    'Usage:     Call ShowFieldsRS("Table1")
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim strSql As String
    
    strSql = "SELECT " & strTable & ".* FROM " & strTable & " WHERE (False);"
    Set rs = DBEngine(0)(0).OpenRecordset(strSql)
    For Each fld In rs.Fields
        Debug.Print fld.Name, FieldTypeName(fld), "from " & fld.SourceTable & "." & fld.SourceField
    Next
    rs.Close
    Set rs = Nothing
End Function

Public Function FieldTypeName(fld As DAO.Field)
    'Purpose: Converts the numeric results of DAO fieldtype to text.
    'Note:    fld.Type is Integer, but the constants are Long.
    Dim strReturn As String         'Name to return
    
    Select Case CLng(fld.Type)
        Case dbBoolean: strReturn = "Yes/No"            ' 1
        Case dbByte: strReturn = "Byte"                 ' 2
        Case dbInteger: strReturn = "Integer"           ' 3
        Case dbLong                                     ' 4
            If (fld.Attributes And dbAutoIncrField) = 0& Then
                strReturn = "Long Integer"
            Else
                strReturn = "AutoNumber"
            End If
        Case dbCurrency: strReturn = "Currency"         ' 5
        Case dbSingle: strReturn = "Single"             ' 6
        Case dbDouble: strReturn = "Double"             ' 7
        Case dbDate: strReturn = "Date/Time"            ' 8
        Case dbBinary: strReturn = "Binary"             ' 9 (no interface)
        Case dbText                                     '10
            If (fld.Attributes And dbFixedField) = 0& Then
                strReturn = "Text"
            Else
                strReturn = "Text (fixed width)"
            End If
        Case dbLongBinary: strReturn = "OLE Object"     '11
        Case dbMemo                                     '12
            If (fld.Attributes And dbHyperlinkField) = 0& Then
                strReturn = "Memo"
            Else
                strReturn = "Hyperlink"
            End If
        Case dbGUID: strReturn = "GUID"                 '15
        
        'Attached tables only: cannot create these in JET.
        Case dbBigInt: strReturn = "Big Integer"        '16
        Case dbVarBinary: strReturn = "VarBinary"       '17
        Case dbChar: strReturn = "Char"                 '18
        Case dbNumeric: strReturn = "Numeric"           '19
        Case dbDecimal: strReturn = "Decimal"           '20
        Case dbFloat: strReturn = "Float"               '21
        Case dbTime: strReturn = "Time"                 '22
        Case dbTimeStamp: strReturn = "Time Stamp"      '23
        
        'Constants for complex types don't work prior to Access 2007.
        Case 101&: strReturn = "Attachment"         'dbAttachment
        Case 102&: strReturn = "Complex Byte"       'dbComplexByte
        Case 103&: strReturn = "Complex Integer"    'dbComplexInteger
        Case 104&: strReturn = "Complex Long"       'dbComplexLong
        Case 105&: strReturn = "Complex Single"     'dbComplexSingle
        Case 106&: strReturn = "Complex Double"     'dbComplexDouble
        Case 107&: strReturn = "Complex GUID"       'dbComplexGUID
        Case 108&: strReturn = "Complex Decimal"    'dbComplexDecimal
        Case 109&: strReturn = "Complex Text"       'dbComplexText
        Case Else: strReturn = "Field type " & fld.Type & " unknown"
    End Select
    
    FieldTypeName = strReturn
End Function

Function DAORecordsetExample()
    'Purpose:   How to open a recordset and loop through the records.
    'Note:      Requires a table named MyTable, with a field named MyField.
    Dim rs As DAO.Recordset
    Dim strSql As String
    
    strSql = "SELECT MyField FROM MyTable;"
    Set rs = DBEngine(0)(0).OpenRecordset(strSql)
    
    Do While Not rs.EOF
        Debug.Print rs!MyField
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
End Function

Function ShowFormProperties(strFormName As String)
On Error GoTo Err_Handler
    'Purpose:   Loop through the controls on a form, showing names and properties.
    'Usage:     Call ShowFormProperties("Form1")
    Dim frm As Form
    Dim ctl As Control
    Dim prp As Property
    Dim strOut As String
    
    DoCmd.OpenForm strFormName, acDesign, WindowMode:=acHidden
    Set frm = Forms(strFormName)
    
    For Each ctl In frm
        For Each prp In ctl.Properties
            strOut = strFormName & "." & ctl.Name & "." & prp.Name & ": "
            strOut = strOut & prp.Type & vbTab
            strOut = strOut & prp.Value
            Debug.Print strOut
        Next
        If ctl.ControlType = acTextBox Then Stop
    Next
    
    Set frm = Nothing
    DoCmd.Close acForm, strFormName, acSaveNo

Exit_Handler:
    Exit Function

Err_Handler:
    Select Case Err.Number
    Case 2186:
        strOut = strOut & Err.Description
        Resume Next
    Case Else
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ShowFormProperties()"
        Resume Exit_Handler
    End Select
End Function

Public Function ExecuteInTransaction(strSql As String, Optional strConfirmMessage As String) As Long
On Error GoTo Err_Handler
    'Purpose:   Execute the SQL statement on the current database in a transaction.
    'Return:    RecordsAffected if zero or above.
    'Arguments: strSql = the SQL statement to be executed.
    '           strConfirmMessage = the message to show the user for confirmation. Number will be added to front.
    '           No confirmation if ZLS.
    '           -1 on error.
    '           -2 on user-cancel.
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim bInTrans As Boolean
    Dim bCancel As Boolean
    Dim strMsg As String
    Dim lngReturn As Long
    Const lngcUserCancel = -2&
    
    Set ws = DBEngine(0)
    ws.BeginTrans
    bInTrans = True
    Set db = ws(0)
    db.Execute strSql, dbFailOnError
    lngReturn = db.RecordsAffected
    If strConfirmMessage <> vbNullString Then
        If MsgBox(lngReturn & " " & Trim$(strConfirmMessage), vbOKCancel + vbQuestion, "Confirm") <> vbOK Then
            bCancel = True
            lngReturn = lngcUserCancel
        End If
    End If
    
    'Commmit or rollback.
    If bCancel Then
        ws.Rollback
    Else
        ws.CommitTrans
    End If
    bInTrans = False

Exit_Handler:
    ExecuteInTransaction = lngReturn
    On Error Resume Next
    Set db = Nothing
    If bInTrans Then
        ws.Rollback
    End If
    Set ws = Nothing
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ExecuteInTransaction()"
    lngReturn = -1
    Resume Exit_Handler
End Function

Function GetAutoNumDAO(strTable) As String
    'Purpose:   Get the name of the AutoNumber field, using DAO.
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    Set db = CurrentDb()
    Set tdf = db.TableDefs(strTable)
    
    For Each fld In tdf.Fields
        If (fld.Attributes And dbAutoIncrField) <> 0 Then
            GetAutoNumDAO = fld.Name
            Exit For
        End If
    Next
    
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

 

Maven 构建JAVA编写系统的自动化工具

介绍：
Maven is an attempt to apply patterns to a project's build infrastructure.Maven is essentially a project management and comprehension tool and as such provides a way to help with managing:Builds ： generate class, jar, war etc file
Documentation: generate javadoc, site documentations etc
Reporting: generate junit text reports.
Dependencies: generate document that describe the package dependencies of the software.
SCM (Software Configuration Management): software setup, version control, log/bug managements etc
Releases: generate releases distribution package
Distribution: web application based distribution settings
安装Maven：


POM文件的设置参考：
POM essentials:

• project This is the top-level element in all Maven pom.xml files.
• modelVersion This element indicates what version of the object model this POM is using. The version of the model itself changes very infrequently but it is mandatory in order to ensure stability of use if and when the Maven developers deem it necessary to change the model.
• groupId This element indicates the unique identifier of the organization or group that created the project. The groupId is one of the key identifiers of a project and is typically based on the fully qualified domain name of your organization. For example org.apache.maven.plugins is the designated groupId for all Maven plug-ins.
• artifactId This element indicates the unique base name of the primary artifact being generated by this project. The primary artifact for a project is typically a JAR file. Secondary artifacts like source bundles also use the artifactId as part of their final name. A typical artifact produced by Maven would have the form <artifactId>-<version>.<extension> (for example, myapp-1.0.jar).
• packaging This element indicates the package type to be used by this artifact (e.g. JAR, WAR, EAR, etc.). This not only means if the artifact produced is JAR, WAR, or EAR but can also indicate a specific lifecycle to use as part of the build process. (The lifecycle is a topic we will deal with further on in the guide. For now, just keep in mind that the indicated packaging of a project can play a part in customizing the build lifecycle.) The default value for the packaging element is JAR so you do not have to specify this for most projects.
• version This element indicates the version of the artifact generated by the project. Maven goes a long way to help you with version management and you will often see the SNAPSHOT designator in a version, which indicates that a project is in a state of development. We will discuss the use of snapshots and how they work further on in this guide.
• name This element indicates the display name used for the project. This is often used in Maven's generated documentation.
• url This element indicates where the project's site can be found. This is often used in Maven's generated documentation.
• description This element provides a basic description of your project. This is often used in Maven's generated documentation.

settings文件的设置参考：


Maven常用命令：
Check for maven version：
     mvn --version

Build the Project： 
     mvn package

Cleans up artifacts created by prior builds
      mvn clean

Generates site documentation for this project
     mvn site

Compile your application sources:
     mvn compile

Compile your test  sources and run your unit tests
     mvn test

Compile your test sources (but not execute the tests)
     mvn test-compile

Create a JAR file
     mvn package

Install the artifact you have generated (the jar file)
     mvn install

Generate an IntelliJ IDEA descriptor for the project
     mvn idea:idea

Using Eclipse IDE
     mvn eclipse:eclipse

How to Install SAP Netweaver Application Server?

• Go to web browser & enter http://www.sdn.sap.com/irj/sdn
• If not a member? Click on "not a member" to register. 
• Select to register as "Public User". Then, continue by follow the easy prompt.
• After submitted your registration, you have to validate your account though the link in the mail from SAP to complete the registration.
• After the validation, you might be asked for few questions to completed your profile.
• Once you logged into the community network, click on the "Downloads" from the quick link.
• Next, click on "Software Downloads". Select "SAP NetWeaver Main Releases".
• On the "SAP NetWeaver Main Releases" page, scroll all down to find the SAP NetWeaver Application Server which corresponding  to your operating system.
• Before you proceed to download, please ensure that your computer have enough resources to run the SAP NetWeaver Application Server.
• Requisition before the installation:
• The hostname must not exceed 13 characters. No special character is allowed.
• Check the File System of the computer that you are intend to install the SAP NetWeaver Application Server. Select Computer > Select C: Drive > Right click the mouse & select Properties. You can see the File System under the General tab.
• The web-browser must be IE 6.0 or higher or Firefox 1.0 or higher
•  RAM > 2GB
• CPU > Pentium III (1.1GHz or higher)
• minimum 50 GB hard disk space (36 GB permanent)
• High resolution monitor (1024x768 or higner, 256 colors)
• Note: Only 1 SAP Server per computer allowed.
• Make sure the file %WINDIR%\system32\drivers\etc\services must no include an entry for the port 3200, 3600 and 8000
•  Then, go to C:\Windows\System32\drivers\etc folder, right click on the services file. Click on Open with Notepad.Scroll up & down & make sure that port 3200, 3600 and 8000 are not in the services file.
• Then you can download to your local pc.
• Extract both zip files using winrar.
• Run the cmd application as administrator.
• On the command prompt, type hdwwiz.exe & click on Enter to start the Add Hardware wizard.
• Choose to install the hardware that I manually select from a list (Advanced). Click on Next.
• Select Network Adapter.
• Under Manufacturer, select Microsoft. & under Network Adapter, select Microsoft Loopback Adapter. Click on Next & then Finish.
• Assign a static IP address to the lookback adapter.
• Right click on the Notepad & run as administrator.
• Click on open & select C:\Windows\System32\drivers\etc\hosts file.
• In the hosts file, make the required entry.

          [STATIC_IP_ADDRESS]              [hostname]

•  Open the SAP_NetWeaver_XXX_Installation_Master\IM_WINDOWS_X86_64 folder, click on sapinst.exe to start the installation of SAP NetWeaver Application Server.
• Select SAP Installation Master > SAP NetWeaver 7.0 including Enhancement Package 2 > SAP Application Server ABAP > MaxDB > Central System > Central System. Click on Next
• Scroll all the page down, & click on I accept. Then, click on Next.
• On the Java Runtime Environment page, set the JRE Directory to the JRE path on the PC.

          Example: C:\Program Files (x86)\Java\jre6

•  Set the Master Password (which contents upper & lower case character & numeric number, maximum 18 characters long)
• On the Paramter Summary, click on Next if you accept the default settings.You can modify them before you click on Next.
• A successful installation will end with a finished successfully note.
• There will have a icon for SAP Management Console on the desktop.
• Open the sapmmc, expand the tree structure, navigate to the SAP System node that you want to explore.
• Click on Start icon.
• Choose the required option(s). Click on OK.
• You can check the SAP system by login using the SAP Logon 720 application.

ITSMobile?

ITSmobile简介

•  ITSmobile (Internet Transaction Server for Mobile) 是新一代实现手持、移动设备（如RF）应用的SAP技术，用于连接手持、移动设备与SAP系统。
• ITSmobile应用Dynpro编程模式。
• ITSmobile代替了WEB SAPConsole，SAPConsole NW7.1 之后将被逐渐淘汰。
• ITSmobile适用于SAP Netweaver 7.10,7.0和SAP Netweaver 2004。
• ITSmobile是Netweaver和ITS集成的一部分。它可以用来创建一个可以通过手持、移动设备的游览器登入的SAP应用程序（有网络功能的SAP应用程序）。

ITSMobile features HTML generation of SAP Screens through its Template Generator
Generated HTML acts as 'Templates' for further customization.ITSmobile模板技术使用了HTML模板概念，创建可视化在浏览器中为SAP屏幕的应用。
动态内容/数据可以包含在模板运行时使用HTMLBusiness(HTMLB)。
（备注： ITSmobile模板生成器允许你生成一个模板或直接创建屏幕。）


ITSmobile在开发上有以下几个优点：

• 可以使用ABAP语言进行开发和调试
• 可以使用SAPGUI基于windows的调试
• 快速初始化模板和可视化的HTML
• 可以编辑模板生成的HTML，达到需求
• HTML 或者 JavaScript可以简单的集成

有两种服务,itsmobile00和itsmobile01,在你的系统,你可以用来测试ITSmobile功能。
ITSmobile支持1维和2维的Barcode。
支持语音拣选（pick by voice），XHTML+Voice ，ITS XV Template generator
支持RFID (Radio-frequency identification)
 

How to Develop a ITSmobile Application

工具要求：

• SAP Web AS ABAP development workbench
• Web application Builder which has 2 components :

•   Internet Service creator.

                      Parts of Internet Service    :
                         Service Parameters
                         Theme / Topic
                         HTML Templates

• HTML Template Generator

运行组件要求 :

• ICM Status -shoul be in  Running status(Needs to be checked in Transaction SMICM).
• HTTP Service - should be Active(In transaction SMICM,GOTO-->Services)
• Published Internet Service(In transactions SE80 and SICF)
• ITS own Services

ITSmobile体系结构 (Architecture of ITSmobile)

Steps to create Mobile ITS application:
1. 生成Internet  Service和模板

• Create the Application(that needs to dispalyed in handheld device) in SAP Web AS ABAP development workbench in SE80.
• In transaction"SE80", select the "Package" in which you want to create the Internet Service (ITS).
• In the tree structure of the package, select "ITS Services".
• Right click the mouse and select "Create".
• If you have created "Package", right click "Create" > "More" > "Internet Service".
• Enter an "Internet Service" name and a "Transaction code" that is to be linked to the service. Leave the ITS Mixed Mode option unselected and save your entries.
• A Theme 99 folder is created for the service at the same time as the service.
• In transaction SE80, switch to your ABAP application and select all of the screens for which you want to create a template, and choose Create Template from the context menu (if necessary, through More Functions).
• Enter the name of the previously created service and a theme (such as 99) and selection Mobile Devices as the generation style.
• On the Parameters tab page, enter all of the required parameters. If you have created the service as screen-based and have already specified the associated transaction during the creation process, the parameter ~TRANSACTION is predefined.

• Parameter	Value	Detail
  ~ITSMOBILE	1	Defines the service as a mobile service and sets the corresponding ITS standards.
  ~TRANSACTION	<your transaction>	Specifies the transaction to be executed.
  ~THEME	<your theme>(for example, 99)	Specifies the desired theme for your service.

   
• Confirm by choosing Save.
• Generate HTML Mobile template for all the screens of the ABAP application from "SE80"
• Publish the Internet Service from "SE80"
• Select the service and choose Publish > Entire Service from the context menu.
• Note at this point, that to publish the entire service, you need to select at least one ITS instance (ITS site). To do this, in the ABAP Workbench, choose Utilities ® Settings. Choose the Internet Transaction Server tab page, and then choose Publish. At this point, you can decide the instances (sites) on which your service is to be published.
• A message in the status line shows whether the publishing was successful.
• Creating an ICF Service with the same name (as in Step-2) from Transaction "SICF" under the folder: "default_host/sap/bc/gui/sap/its"
• Activate the service created from "SICF". The service can be tested from the context menu of the Service.

 Development Guidelines for Mobile Applications:

1. Keep the UI simple by design
2. Keep in mind the limited screen sizes of handheld devices
3. It is recommended to limit the UI elements to: "Text Box", "I/O Fields", "Checkboxes", "Radio Buttons" and "Pushbuttons" Refer Supported Screen Elements for more details
   Frames, Tab strips, Table Controls are not supported
4. GUI Status not to include any Buttons. Instead include it on the Screens
   Error/Warning/Status/Information messages should be handled on an output field. That means, "Message" statement or calling a Function Module to show a popup message / confirmation prompt, should not be used

Steps to create Internet service in SE80.

1.  In SE80 choose Internet service from the dropdown and give an internet sevice name.
2. Give the transaction name of the program.
3. Create template by right clicking on the internet service CREATE->TEMPLATE.Give the theme as 99,enter the screen no for which template needs to created and the generating style should be Mobile Gerate.
4. Double click on the Internet service name.In the parameters section give ~transaction = transaction name of the program and ~generateddynpro = 1.
5. Save the service.Right click on the Internet service select PULISH->COMPLETE SERVICE.You should get a success message like this.

Steps to create Service in SICF transaction.

1. In SICF transaction,execute the report to get into the second screen.Navigate to the path default_host/sap/bc/gui/sap/its and right click and select New sub element.
2. Give the same Internet service name as given in SE80.In the sevice tab for GUI LINK drop down select YES.In the GUI configuration tab give the following parameters.
3. In the Logon Data tab choose Procedure as Alternative logon procedure.
4. Delete other types of logon procedures retaining only Fields Authentication.
5. In the Handler list tab give the class name as CL_HTTP_EXT_ITS.
6. In the error pages tab choose the last radio button system login.
7. Click the configuration button.
8. Click the check boxes : System ID,client,Language,system messages.
9. Give the user specific class as 'CL_MOBILE_SYSTEM_LOGIN'.
10. save the internet service,right click and activate it.Then right click and Test Service.
11. It will open a webpage and will display the application as seen in handheld device.

Useful link:-
http://help.sap.com/saphelp_nw70/helpdata/en/46/668d4b72255de4e10000000a1553f6/frameset.htm

What is POJO?

POJO (Plain Old Java Object), is a normal Java object class (that is, not aJavaBean, EntityBean, SessionBean etc.) and does not serve any other special role nor does it implement any special interfaces of any of the Java frameworks. This term was coined by Martin Fowler, Rebbecca Parsons and Josh MacKenzie who believed that by creating the acronym POJO, such objects would have a "fancy name", thereby convincing people that they were worthy of use.

POJO是一个简单的、正规Java对象，它包含业务逻辑处理或持久化逻辑等，但不是JavaBean、EntityBean、 SessionBean等，不具有任何特殊角色和不继承或不实现任何其它Java框架的类或接口。POJO有一些private的参数作为对象的属性。然后针对每个参数定义了get和set方法作为访问的接口。

Example: 

public class customer {
   private long id;
   private String firstName;
   private String lastName;
   private String address;

   public void setId(long id) {
      this.id=id;
   }

   public void setFirstName(String firstName) {
      this.firstName=firstName;
   } 

   public void setLastＮame(String lastName) {
      this.lastName=lastName;
   } 

   public void setAddress(String address) {
      this.address=address;
   } 

   public void getId() {
      return id;
   }

   public void getFirstName() {
      return firstName;
   } 

   public void getLastＮame() {
      return lastName;
   } 

   public void getAddress() {
      return address;
   }

} 

Archive & Unarchive linux commands

TAR Command

Creating a tar file from directory or file
   # tar -cvwf [tar_file] [directory | file]

Extracting/Unarchive/Untar the *.tar files from a tar file
   # tar -xwf [tar_file] -C [to_specific_folder]

Extracting/Unarchive/Untar the *.tar files from a tar file
   # tar -xvwf [tar_file] -C [to_specific_folder]

Extracting/Unarchive/Untar the *.gz files from a tar file
   # tar -xvwzf [gz_file] -C [to_specific_folder]

Creating a tarred file that is compressed
   # tar -cvjf [tbz_file] [directory | file]

Extracting the files from a tbz file
   # tar -xjf [tbz_file] -C [to_specific_folder]

Listing the tar contents without extracting any of the files
   # tar -tvf [tar_file]

GZI, GUNZIP Command

Compress the file
   # gzip [file]

Uncompress the compressed file
   # gunzip -c [compressed_file]                             // the compressed file will no longer on the drive
   # gunzip -c [compressed_file] > [file]                 // the compressed file won't be deleted

ZCAT Command

Uncompress a file
   # zcat [file]

ZIP, UNZIP Command

Archive file
   # zip [archive_file] [file_to_be_archived...]

Archive a directory
   # zip -r [archive_file] [directory_to_be_archived]
   # zip -rm [archive_file] [directory_to_be_archived]    //the to-be-archived directory will be
                                                                                             //removed
Unarchived an ZIP archive
   # unzip [archive_file]

BZIP2, BUNZIP2 Command

Compress a file
   # bzip2 [file_to_be_compressed]                            // the uncompressed version will be deleted
   # bzip2 -c [file_to_be_compressed] > [compressed_file]
  
Compress a directory
   # bzip2 -r [directory]

Uncompress compressed file
   # bunzip2 [compressed_file]
   # bzip2 -d [compressed_file]

/etc/sysconfig/iptables settings

To check if iptables is installed.
   # rpm -q iptables

To see if iptables is actually running
   # lsmod | grep ip_tables

To inspect currently loaded rules for iptables.
   # iptables -L

To save the new added rules
   # /sbin/service iptables save


To enable iptables by running:
   # system-config-securitylevel

To edit the rules for iptables.
   # vi /etc/sysconfig/iptables

Example of the content of / etc/sysconfig/iptables file.

*nat
:PREROUTING ACCEPT [190:33819]
:POSTROUTING ACCEPT [1:60]
:OUTPUT ACCEPT [4:240]
-A POSTROUTING -o eth1 -j MASQUERADE
COMMIT
# Completed on Wed Nov 23 12:04:17 2011
# Generated by iptables-save v1.4.7 on Wed Nov 23 12:04:17 2011
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [1413536:615884533]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -i eth+ -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
-A FORWARD -p icmp -j ACCEPT
-A FORWARD -i lo -j ACCEPT
-A FORWARD -i eth+ -j ACCEPT
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

Rule Set for iptables

1. Set of rules that all all outgoing connections but block all unwanted incoming connections:

# iptables -P INPUT ACCEPT
# iptables -F
# iptables -A INPUT -i lo -j ACCEPT
# iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
# iptables -A INPUT -p tcp --dport 22 -j ACCEPT
# iptables -P INPUT DROP
# iptables -P FORWARD DROP
# iptables -P OUTPUT ACCEPT
# iptables -L -v

Switch Definition
-P  : default policy
-F  : flush all existing rules
-A : append a rule to a specific chain
-i   : to specify packets matching or destinated for the localhost interface
-j   : to jump to the target action for packets matching the rule.
-m : to load a module (state)
-p  : connection types
-dport : connection port
-L -v: to list the rules
-s  : source IP address

2. If allowed external internet interface (ppp0 dialup modem), it will have effectively like disabled our firewall.

# iptables -A INPUT -i ppp0 -j ACCEPT

3. To allow all incoming packets within internal LAN but still filter incoming packets on our external internet connection.

# iptables -A INPUT -i lo -j ACCEPT

# iptables -A INPUT -i eth0 -j ACCEPT 

4. Add packets from trusted IP address

# iptables -A INPUT -s [ip_address] -j ACCEPT        

# iptables -A INPUT -s [ip_address]/[port] -j ACCEPT      

# iptables -A INPUT -s [ip_address]/[subnet_mask] -j ACCEPT 

# iptables -A INPUT -s [ip_address] -m mac --mac-source [mac_address_of_source_ip_address] -j ACCEPT 

5. Accept tcp packets on destination port 6881 (bittorent)

# iptables -A INPUT -p tcp --dport 6881 -j ACCEPT

6. Accept tcp packets on destination port 6881-6890

# iptables -A INPUT -p tcp --dport 6881:6890 -j ACCEPT

7. Accept tcp packets on destination port 22 (SSH)

# iptables -A INPUT -p tcp --dport 22 -j ACCEPT

8. Accept tcp packets on destination port 22 (SSH) from private LAN

# iptables -A INPUT -p tcp -s 192.168.0.0/24 --dport 22 -j ACCEPT

Excel Formula

The INDEX function

The basic INDEX function returns a VALUE based on a defined array / column and a row number.  The syntax from Excel is as follows:

=INDEX ( array , row number )

Below is an example of using INDEX to return the value “Wallet,” assuming that you already know that the value is three cells down on your defined array.

Formula: =INDEX(B2:B6,2)

 

	A	B	C
1	ID	Product	Quantity
2	101	Watch	5
3	128	Wallet	12
4	206	Bag	200
5	212	Shoes	8
6	237	Belt	2
7	Lookup	Result
8	128	Wallet

 

The MATCH function

The basic MATCH function returns a NUMBER based on the relative position of a lookup value within a defined array / column.  The syntax from Excel is as follows:

=MATCH (  lookup value , lookup array , match type )

Below is an example of using the MATCH formula to return the position of “206″ within our column reference.

Formula: =MATCH(A8, A2:A6, 0)

 

	A	B	C
1	ID	Product	Quantity
2	101	Watch	5
3	128	Wallet	12
4	206	Bag	200
5	212	Shoes	8
6	237	Belt	2
7	Lookup	Result
8	206	3

Since “206″ is 3th cells down in the array, the value “3″ is returned.

INDEX MATCH

When we combine both the INDEX formula and the MATCH formula, the number that the MATCH formula returns becomes the row number for your INDEX formula.

=INDEX ( array , MATCH formula )

=INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0″ ))

 

Below is an example of using the INDEX MATCH to return "Quantity” for our lookup value.

As you can see, it returns the same value we got from VLOOKUP.

Formula: =INDEX(C2:C6, MATCH(A8, A2:A6, 0))

	A	B	C
1	ID	Product	Quantity
2	101	Watch	5
3	128	Wallet	12
4	206	Bag	200
5	212	Shoes	8
6	237	Belt	2
7	Lookup	Result
8	206	200

The VLOOKUP function

First our point of reference: the trusty VLOOKUP formula.  VLOOKUP returns a VALUE based on a defined array and column reference.  The syntax from Excel is as follows:

=VLOOKUP ( lookup value , lookup array , column , range lookup )

Below is an example of using VLOOKUP to return the value “200” based on the lookup value “206”

Formula: =VLOOKUP(A8, A2:C6, 3, FALSE)

 

	A	B	C
1	ID	Product	Quantity
2	101	Watch	5
3	128	Wallet	12
4	206	Bag	200
5	212	Shoes	8
6	237	Belt	2
7	Lookup	Result
8	206	200

----------------------------------------------------------------------------------------------------------------------- 

A	B	C	D
Ali		Susan	Yes
Susan		Meng	No
May		Yee	No
Mutu		Ahmad	No
Joe		Ali	Yes

 To check is the variable in column C existed in the column A (row 1-5) list.
 [column D]
    =IF(ISERROR(MATCH(C1,$A$1:$A$5,0)),"No","Yes")

A	B	C	D
Ali	13	Susan	20
Susan	20	Meng
May	7	Yee
Mutu	34	Ahmad
Joe	56	Ali	13

To get the pairing variable for variable in column C from table A1:B5
[Column D]
 =IF(ISERROR(VLOOKUP(C1,$A$1:$B$5,2,FALSE)),"",VLOOKUP(C1,$A$1:$B$5,2,FALSE))