Has anyone tried this?

  • Thread starter Thread starter nmark
  • Start date Start date
N

nmark

I'm trying to make a query using the 'In' operator in criteria, but I want to
use the values taken from a textbox in a form.

The syntax looks like :

(criteria)
In ([Forms]![FormName]![Textbox1])

The value in the textbox looks quite alright : "Value a"; Value B" etc

However this doesn't work...

Any suggestions?
 
I doubt that the query will treat the value of the text box as a delimited
list for the query like that.

If you add an unbound text box named "txtKeywords" to your bound form, you
could put code like this into its AfterUpdate event. The user can type in up
to 99 words, separated by spaces, and the the form will be filtered so it
returns only records that have at least one of those words in the Notes
field:

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
 
Hi,


indeed, that will "search" for the string "Value a"; "Value b".


Instead, you can try:

... WHERE ( ";" & Forms!FormName!Textbox1 & ";" ) LIKE ( "*[; ]" &
fieldName & "[; ]*" )



but that could be quite slow, since indexes cannot be used. Use % instead of
* if, in your settings, the wildcard is % rather than *.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top