Filtering with a Calculated Result (1 Viewer)

[MatthewB]

I have a calculated field called SearchItem :
[ownerID] & [BuildingNr] & [ BuildingName] & [OwnerName]

The Code on the Search btn:

Me.FINDER.SetFocus
Me.FilterOn = False
Me.Filter = "[SearchItem] Like '*" & FINDER & "*' "
ME.FilterOn = True
Me.Requery

When I populate the FINDER field with one of the field experessions, as in [BuilidingNr] HT132 I get an expected filter on the Calculated field, but not if I use two expressions as in HT132 Jackson - Jackson being an owners name.

The calculated field reads by example: 123HT132DevonHeightsJackson

Any thoughts as to why?

The Clear btn code is:

DoCmd.ShowAllRecords
Me.FINDER.SetFocus
Me.FINDER = ""

 

[theDBguy]

[ownerID] & [BuildingNr] etc. doesn't have a space in between them. If you want spaces, you have to add them. For example:

[ownerID] & " " & [BuildingNr] etc.

 

[plog]

SearchItem ='123HT132DevonHeightsJackson'

You submitted a search for this substring: 'HT132 Jackson'.

1. The spacing issue as pointed out by DBguy.

2. You are submitting one search string but expect the computer to know to do it in 2 parts. Even if you got the spacing right 'HT132Jackson' isn't going to make a match because it simply doesn't exist in your SearchItem.

Either make another search input, or write a complex VBA that parses out the input string into each of its 'words' and then repeatedly matches each 'word' to see if its in SearchItem, returnign any results where every 'word' is matched.

 

[MatthewB]

Thanks
So a solution I think it to separate any expression with *
Provided the expression is in the same order as the calculated field it returns the anticipated result

Regards

 

[arnelgp]

you need to Split your Finder textbox:

Dim var As Variant
Dim i As Integer
Dim sFilter As String
var = Split([Finder])
For i = 0 To Ubound(var)
sFilter = sFilter & "[SearchItem] Like '*" & var(i) & "*' And "
next
If len(sFilter) <> 0 Then
sFilter = Left$(sFilter, Len(sFilter) - 5)
End If
Me.Filter = sFilter
ME.FilterOn = True
Me.Requery

 

[MatthewB]

you need to Split your Finder textbox:

Dim var As Variant
Dim i As Integer
Dim sFilter As String
var = Split([Finder])
For i = 0 To Ubound(var)
sFilter = sFilter & "[SearchItem] Like '*" & var(i) & "*' And "
next
If len(sFilter) <> 0 Then
sFilter = Left$(sFilter, Len(sFilter) - 5)
End If
Me.Filter = sFilter
ME.FilterOn = True
Me.Requery

Arnelgp
Thanks for the reply
I have to admit that being new to access your code is completely above me. If i were to go and try parse your code to understand what you are doing where would you tell me to look? The Microsoft Access site was not that helpful.