Multiple query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a parameter query that allows me to enter in specific data to search
for a record. A report then uses this query to provide me with a report on
the report information. I want to be able to enter in multiple record
searches to pull all info on the records I have asked for.

Say for examply i am looking for record 1164-2005, I enter 1164-2005 into
the parameter pop up window and get a report based on 1164-2005. I want to be
able for example to enter 1164-2005 and 1120-2005 and 0500-2005 and get a
report containing all three records at once and on the same report without
having to do multiple sing serch reports.

As of now I have a parameter query that I enter in 1164-2005 and it pulls up
the record. What I want to happen is to be able to enter 1164-2005,
1123-2005, and 1450-2005 on the same screen and get all three records without
having to doee seperate times.
 
Parameters in a query do not give you that flexibility. Use a form instead.

To use the example below, create a form bound to your table. Add an unbound
text box where you can enter as many values as you wish, separated by
commas. When you press Enter, the form will show only those records that
match one of the ones you chose. You can then create a report with the same
filter if you wish.

Unfortunately, your field contains a dash. JET 4 (all versions from 2000
onwards) have a bug where this is handled inconsistently depending on
whether the field is indexed, so don't use the Like operator:
http://support.microsoft.com/kb/271661/en-us

The example below assumes the field you are wanting to search is a Text type
field named "Notes". Replace with your actual field name.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, ",")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] = """ & _
strWord & """) OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub
 
Back
Top