Hopefully you are not saying that the data is *stored* like that in the
table. That would be a disasmare (a combination of a disaster and a
nightmare), i.e. you would need to change the database design so it is
normalized.
But if you just want the user to be able to key various values into the
*search* box, separated by spaces, and then return the results in the
continuous form, you could use the AfterUpdate event of this text box to
parse the long string into an array, and turn it into a filter string.
This example shows how to do that. The search box is named txtKeywords, and
the field to search in is named "Notes" in this example:
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] Like ""*" & 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
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Cainman said:
I should have provided a better description of my request. I want to allow
users of my database to enter multiple entries at one time in a form and
then
run a query and return the appropriate results for each entry. The entries
will be different and will look like this:
52.HCFS.789085..CV
48.HCFS.792390..CV
36.HCFS.988784..CD
166 T1ZF BOTHWAAKW02 EVRTWAXA03T
136 T1ZF BOTHWAAKW02 EVRTWAXA03T
Allen Browne said:
You could set the Filter property of your form, using the Like operator
and
wildcard, like this:
Me.Filter = "[MyField] Like ""ABC*"""
Me.FilterOn = True
A better solution might be to use 2 fields:
- a Text field for the ABC part (whatever that means), and
- a Number field for the 1 - 99 part.
It is always better to make your fields atomic (i.e. split them up, so
separate pieces of information go into separate fields.)
Cainman said:
I need to build a form in Access 2003 that searches for multiple
entries
at
once. For example how would I build a form that could search for: ABC1,
ABC2,
ABC3 in a table that has ABC1 - ABC99 ? I understand how to use a
prompt
in a
query for a single record but am not sure how to set up a prompt for
multiple
entries or have a form lookup multiple entries at once.
