You can run a temporary parameter query from a module. A parameter query
allows you to keep changing criteria in a loop.
The following code illustrates how to do this.
For help with creating an SQL string for your database, see the end of this
post.
Regards
Geoff
Public Sub FilterMyTable()
Dim objDB As DAO.Database
Dim objQDF As DAO.QueryDef
Dim objRS As DAO.Recordset
Dim strSQL As String
Dim intDateNo As Integer
Dim intIDNo As Integer
Dim datNextDate As Date
Dim lngNextID As Long
' Point to the current database:
Set objDB = CurrentDb()
' Create a temporary query
' by not giving it a name:
Set objQDF = objDB.CreateQueryDef("")
' Create an SQL statement for a
' parameter query. At this stage the
' SQL does not contain criterion values;
' only parameters, which we give values
' later:
strSQL = "PARAMETERS [Date] DateTime, " _
& "[ID] Long;" & vbNewLine _
& "SELECT Table1.*" & vbNewLine _
& "FROM Table1" & vbNewLine _
& "WHERE (((Table1.Date)=[Date]) " _
& "AND ((Table1.ID)=[ID]));"
' Set the query's SQL property:
objQDF.SQL = strSQL
' Loop through dates:
For intDateNo = 1 To 2
GoSub DoNextDate
Next
Bye:
GoSub CleanUp
Exit Sub
DoNextDate:
' We use the Choose function here simply
' as a device to process a different date
' for each iteration of the Date FOR-NEXT
' loop; we use the loop counter in Choose.
' Get the next date:
datNextDate = Choose(intDateNo, "1/1/06", "2/1/06")
' Put the next date into the Date
' parameter of the query:
objQDF.Parameters("Date").Value = datNextDate
' Loop through ID numbers:
For intIDNo = 1 To 4
GoSub DoNextIDNumber
Next
Return
DoNextIDNumber:
' We use the Choose function here simply
' as a device to process a different ID
' number for each iteration of the ID
' FOR-NEXT loop; we use the loop counter
' in Choose.
' Get the next ID number:
lngNextID = Choose(intIDNo, 100, 200, 300, 400)
' Put the next ID into the ID Parameter
' of the query:
objQDF.Parameters("ID").Value = lngNextID
' We've given the date and ID parameters a
' value, so now let's open a recordset
' using the query:
Set objRS = objQDF.OpenRecordset()
' See if recordset contains records:
If objRS.BOF And objRS.EOF Then
' No records. Simply close recordset
' and return to next iteration of loop.
objRS.Close
Return
End If
' Recordset contains records for the current date
' and ID. Do something with the records:
Do Until objRS.EOF
Debug.Print objRS.Fields![Date], objRS.Fields![ID]
objRS.MoveNext
Loop
' Close the recordset:
objRS.Close
Return
CleanUp:
Set objRS = Nothing
Set objQDF = Nothing
Set objDB = Nothing
Return
End Sub
HOW TO CREATE SQL STRING:
For your application, you'll need to replace the strSQL string variable.
To do this, follow these steps:
1. Create a temporary query in design view.
2. Add your Date and ID fields to the grid.
3. Enter the criterion for the Date field as [Date]
4. Enter the criterion for the ID field as [ID]
5. Open the Query menu and select Parameters.
6. In the first row, in the Parameter column, enter [Date]
7. Click in the Data Type column to the right of [Date] and use the
drop-down arrow to select Date/Time.
8. In the second row, in the Parameter column, enter [ID]
9. Click in the Data Type column to the right of [ID] and use the
drop-down arrow to select Long Integer (assuming that's the right data type
for your ID field).
10. Click OK to close the Parameter Queries dialog.
11. Open the View menu and select SQL view.
12. Highlight and copy all the SQL text.
13. Close the query and return to your code module.
14. Paste the SQL text into the strSQL variable. To do this, you may
find it easier to follow these steps:
(a) Click the mouse just after strSQL =
(b) Open the Add-Ins menu.
(c) Click VBA String Editor.
(d) Paste the SQL text into the Editor.
(e) Click Update.
