VBA/Excel/Access/Word/Access/SQL Select — различия между версиями

Текущая версия на 12:46, 26 мая 2010

Содержание

1 Add where clause to the select statement
2 Creating a Select Query with ActiveX Data Objects
3 Executing a Select Query
4 Get DISTINCT records
5 Get DISTINCTROW
6 Get more than one column
7 Get only one column
8 Get the top 10 percent
9 Get the top 5 percent
10 Loop through the ResultSet after executing select statement
11 Modifying a Select Query
12 Order by two fields
13 Order record in a decscending order
14 Order the resultset with Order by clause
15 Select all columns
16 Use and to combine conditions
17 Use Between And
18 Use between and with number type column
19 Use Date function in where clause
20 Use IN and like in where clause
21 Use IN in select statement
22 Use "Is not null"
23 Use Is NULL to check if a column is null
24 Use Not In
25 Use "Select all"
26 use SUM in sql statement
27 Use where clause and order by clause together
28 Use wild card character in link
29 Using Date field type in select statement
30 You must use a pound symbol (#) when delimiting dates for Microsoft Access, like this:

Add where clause to the select statement

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT txtCustFimyRecordsetName, txtCustLastName FROM tblCustomer WHERE txtState = "NJ"")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Creating a Select Query with ActiveX Data Objects

 
Sub Create_SelectQuery()
   Dim cat As ADOX.Catalog
   Dim cmd As ADODB.rumand
   Dim strPath As String
   Dim strSQL As String
   Dim strQryName As String
   On Error GoTo ErrorHandler
   strPath = CurrentProject.Path & "\mydb.mdb"
   strSQL = "SELECT Employees.* FROM Employees WHERE Employees.City="London";"
   strQryName = "London Employees"
   Set cat = New ADOX.Catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
   Set cmd = New ADODB.rumand
   cmd.rumandText = strSQL
   cat.Views.Append strQryName, cmd
ExitHere:
   Set cmd = Nothing
   Set cat = Nothing
   MsgBox "The procedure completed successfully.", _
       vbInformation, "Create Select Query"
   Exit Sub
ErrorHandler:
   If InStr(Err.Description, "already exists") Then
      cat.Views.Delete strQryName
      Resume
   Else
      MsgBox Err.Number & ": " & Err.Description
      Resume ExitHere
   End If
End Sub

Executing a Select Query

 
Sub Execute_SelectQuery()
   Dim cmd As ADODB.rumand
   Dim myRecordset As ADODB.Recordset
   Dim strPath As String
   strPath = CurrentProject.Path & "\mydb.mdb"
   Set cmd = New ADODB.rumand
   With cmd
      .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
      .rumandText = "[Products by Category]"
      .rumandType = adCmdTable
   End With
   Set myRecordset = New ADODB.Recordset
   Set myRecordset = cmd.Execute
   Debug.Print myRecordset.GetString
   myRecordset.Close
   Set myRecordset = Nothing
   Set cmd = Nothing
End Sub

Get DISTINCT records

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT DISTINCT City FROM Employees ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Get DISTINCTROW

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT DISTINCTROW CompanyName FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID ORDER BY CompanyName; 
")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Get more than one column

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT FirstName, LastName, PhoneNo FROM Employees")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Get only one column

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT LastName FROM Employees ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Get the top 10 percent

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT TOP 10 PERCENT * FROM Products ORDER BY UnitPrice ASC; ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Get the top 5 percent

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT TOP 5 * FROM Products ORDER BY UnitPrice DESC ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Loop through the ResultSet after executing select statement

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("select * from employees")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Modifying a Select Query

 
Sub Modify_Query()
   Dim cat As ADOX.Catalog
   Dim cmd As ADODB.rumand
   Dim strPath As String
   Dim newStrSQL As String
   Dim oldStrSQL As String
   Dim strQryName As String
   strPath = CurrentProject.Path & "\mydb.mdb"
   newStrSQL = "SELECT Employees.* FROM Employees" & _
      " WHERE Employees.City="London"" & _
      " ORDER BY BirthDate;"
   strQryName = "London Employees"
   Set cat = New ADOX.Catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
   Set cmd = New ADODB.rumand
   Set cmd = cat.Views(strQryName).rumand
   oldStrSQL = cmd.rumandText
   Debug.Print oldStrSQL
   cmd.rumandText = newStrSQL
   Debug.Print newStrSQL
   Set cat.Views(strQryName).rumand = cmd
   Set cmd = Nothing
   Set cat = Nothing
End Sub

Order by two fields

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtState = "NJ" ORDER BY txtCustLastName DESC, txtCustFirstName")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Order record in a decscending order

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT * FROM Employees ORDER BY Country DESC")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Order the resultset with Order by clause

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT * FROM Employees ORDER BY EmployeeID ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Select all columns

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT * FROM Employees")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use and to combine conditions

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT txtCustFimyRecordsetName, txtCustLastName FROM tblCustomer WHERE txtState = "NJ" AND txtCustLastName = "Miller"")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use Between And

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("Select * FROM Products WHERE UnitPrice NOT BETWEEN 10 and 25 ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use between and with number type column

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("Select * FROM Products WHERE UnitPrice Between 10 and 25")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use Date function in where clause

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT * FROM Employees WHERE ((Year([HireDate])<1993) OR (City="Redmond"))      ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use IN and like in where clause

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT * FROM Employees WHERE City IN ("Redmond", "London") AND ReportsTo LIKE "Buchanan, Steven" ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use IN in select statement

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT * FROM Employees WHERE City IN ("Redmond", "London")")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use "Is not null"

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("Select * from Employees WHERE ReportsTo IS NOT NULL ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use Is NULL to check if a column is null

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("Select * from Employees WHERE ReportsTo IS NULL")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use Not In

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT * FROM Employees WHERE City NOT IN ("Redmond", "London") ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use "Select all"

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT ALL * FROM Employees ORDER BY EmployeeID; ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

use SUM in sql statement

 
Private Sub RunningSumSQL()
    Dim db As Database
    Set db = CurrentDb
    Dim qry As QueryDef
    Dim sSQL As String
    
    On Error Resume Next
    db.QueryDefs.Delete "temp"
    On Error GoTo 0
    
    sSQL = "SELECT R1.Event,(SELECT SUM(R2.Duration) FROM Running As R2 WHERE R2.Event < R1.Event) AS StartTime FROM Running As R1"
    Set qry = db.CreateQueryDef("temp", sSQL)
    DoCmd.OpenQuery qry.Name
End Sub

Use where clause and order by clause together

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtState = "NJ" ORDER BY txtCustLastName, txtCustFirstName
")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Use wild card character in link

 
Sub CreateRst_WithSQL()
   Dim conn As ADODB.Connection
   Dim myRecordset As ADODB.Recordset
   Dim strConn As String
   strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & CurrentProject.Path & _
      "\mydb.mdb"
   Set conn = New ADODB.Connection
   conn.Open strConn
   Set myRecordset = conn.Execute("SELECT txtCustFimyRecordsetName, txtCustLastName FROM tblCustomer WHERE txtCustLastName Like "M*" ")
   Do Until myRecordset.EOF
       For Each fld In myRecordset.Fields
          Debug.Print fld.Name & "=" & fld.Value
       Next fld
       myRecordset.MoveNext
   Loop
   
   myRecordset.Close
   Set myRecordset = Nothing
   conn.Close
   Set conn = Nothing
End Sub

Using Date field type in select statement

 
Sub FindProject()
    Dim strSQL As String
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * FROM Employees WHERE BirthDate = #12/31/2007#"
    "Attempt to find a specific project
    strSQL = "[EmployeeID] = " & 1
    rst.Find strSQL
    "Determine if the specified project was found
    If rst.EOF Then
        msgBox lngValue & " Not Found"
    Else
        msgBox lngValue & " Found"
    End If
    rst.Close
    Set rst = Nothing
End Sub

You must use a pound symbol (#) when delimiting dates for Microsoft Access, like this:

 
Sub FindProject()
    Dim strSQL As String
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * FROM Employees WHERE BirthDate = #12/31/2007#"
    "Attempt to find a specific project
    strSQL = "[EmployeeID] = " & 1
    rst.Find strSQL
    "Determine if the specified project was found
    If rst.EOF Then
        msgBox lngValue & " Not Found"
    Else
        msgBox lngValue & " Found"
    End If
    rst.Close
    Set rst = Nothing
End Sub

Текущая версия на 12:46, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Текущая версия на 12:46, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)

VBA/Excel/Access/Word/Access/SQL Select — различия между версиями

Текущая версия на 12:46, 26 мая 2010

Содержание

Add where clause to the select statement

Creating a Select Query with ActiveX Data Objects

Executing a Select Query

Get DISTINCT records

Get DISTINCTROW

Get more than one column

Get only one column

Get the top 10 percent

Get the top 5 percent

Loop through the ResultSet after executing select statement

Modifying a Select Query

Order by two fields

Order record in a decscending order

Order the resultset with Order by clause

Select all columns

Use and to combine conditions

Use Between And

Use between and with number type column

Use Date function in where clause

Use IN and like in where clause

Use IN in select statement

Use "Is not null"

Use Is NULL to check if a column is null

Use Not In

Use "Select all"

use SUM in sql statement

Use where clause and order by clause together

Use wild card character in link

Using Date field type in select statement

You must use a pound symbol (#) when delimiting dates for Microsoft Access, like this:

Навигация

Поиск