VBA/Excel/Access/Word/Access/SQL Select
Содержание
- 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
<source lang="vb">
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
</source>
Creating a Select Query with ActiveX Data Objects
<source lang="vb">
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
</source>
Executing a Select Query
<source lang="vb">
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
</source>
Get DISTINCT records
<source lang="vb">
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
</source>
Get DISTINCTROW
<source lang="vb">
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
</source>
Get more than one column
<source lang="vb">
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
</source>
Get only one column
<source lang="vb">
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
</source>
Get the top 10 percent
<source lang="vb">
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
</source>
Get the top 5 percent
<source lang="vb">
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
</source>
Loop through the ResultSet after executing select statement
<source lang="vb">
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
</source>
Modifying a Select Query
<source lang="vb">
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
</source>
Order by two fields
<source lang="vb">
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
</source>
Order record in a decscending order
<source lang="vb">
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
</source>
Order the resultset with Order by clause
<source lang="vb">
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
</source>
Select all columns
<source lang="vb">
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
</source>
Use and to combine conditions
<source lang="vb">
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
</source>
Use Between And
<source lang="vb">
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
</source>
Use between and with number type column
<source lang="vb">
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
</source>
Use Date function in where clause
<source lang="vb">
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
</source>
Use IN and like in where clause
<source lang="vb">
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
</source>
Use IN in select statement
<source lang="vb">
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
</source>
Use "Is not null"
<source lang="vb">
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
</source>
Use Is NULL to check if a column is null
<source lang="vb">
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
</source>
Use Not In
<source lang="vb">
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
</source>
Use "Select all"
<source lang="vb">
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
</source>
use SUM in sql statement
<source lang="vb">
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
</source>
Use where clause and order by clause together
<source lang="vb">
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
</source>
Use wild card character in link
<source lang="vb">
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
</source>
Using Date field type in select statement
<source lang="vb">
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
</source>
You must use a pound symbol (#) when delimiting dates for Microsoft Access, like this:
<source lang="vb">
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
</source>
