search criteria

[Paul Hearfield]

I have many records in a table and I need to find a set
of records from RUN NUMBER X to RUN NUMBER X and then
show them in a table view, these records can then be
exported to an excel file. How do I input the run numbers
to get the desired records.
RUN NUMBER is the actual field name.
If this is done with a filter can you explain how I would
do it.
Regards.

 

[Allen Browne]

1. Open the form that is bound to this table in design view.

2. Add two text boxes and a command button, preferably to the Form Header
section (View menu). Name them:
txtFrom
txtTo
cmdApplyFilter.

3. Right-click cmdApplyFilter, and choose Properties.
Set the On Click property to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.

4. Paste the following code between the "Private Sub..." and "End Sub"
lines:
'-------------code begins-----------------
Private Sub cmdApplyFilter_Click()
Dim strWhere As String
If IsNull(Me.txtFrom) Then
If Not IsNull(Me.txtTo) Then 'To, but no From.
strWhere = strWhere & "[RUN NUMBER] <= " & Me.txtTo
End If
Else
If IsNull(Me.txtTo) Then 'From, but no To.
strWhere = strWhere & "[RUN NUMBER] >= " & Me.txtFrom
Else 'Both From and To.
strWhere = strWhere & "[RUN NUMBER] Between " & Me.txtFrom & "
And " & Me.txtTo
End If
End If

If len(strWhere) = 0 Then
MsgBox "No criteria"
Else
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
'-------------code ends-----------------

Note: If RUN NUMBER is a field of type Text, you need additional quotes,
e.g:
strWhere = strWhere & "[RUN NUMBER] <= " & Me.txtTo & """"