Filter code not working

[DZ]

Hi

I am using this code to filter my form:
Dim strr As String
strr = Me.txtSearch

Me.FilterOn = True
Me.Filter = _
"[Library] like '*" & strr & "*'" _
& " or [Instrument] like '*" & strr & "*'" _
& " or [Location] like '*" & strr & "*'" _
& " or [Description] like '*" & strr & "*'" _
& " or [Comment] like '*" & strr & "*'"

The code works for all fields except the location field. All the fields are
text fields.
I tested the filtering of the Location field by entering various simple
words into the Location field for various records and when I type one of the
those words into Me.txtSearch and execute the code, those records are not
returned. All the other fields are working properly with this code.

Can any tell me why the Location field is not included in the filtered
results.


Thanks

 

[Tom van Stiphout]

On Wed, 22 Jul 2009 01:26:01 -0700, DZ <[email protected]>
wrote:

Btw, I typically first set the Filter property, then set FilterOn.

Perhaps because there is no Location field? Note that a filter
expression must use Field names, not Control names.

-Tom.
Microsoft Access MVP

 

[Dale_Fye via AccessMonster.com]

My first guess is that the [Location] field in the underlying table is setup
as a Lookup field, where the actual value stored in the column is a numeric
ID field, but what is displayed is the text value associated with that field
in another table.

To check this, open the table in design view. Look for the [Location] field
and click on it. Then go to the "Lookup" tab in the field properties section.
If the "Display Control" property is set to ListBox or Combo box, that is
what has happened. If that is the case, then instead of using a table for
the RecordSource for your form, use a query, and include the table that is
shown in the RowSource for the Locations field in the query. Finally,
instead of using the [Location] field from your table, use the field from the
lookup table which contains the Location Name.

BTW, I never setup fields with the Lookup Property set to ComboBox or
ListBox. In my mind, all this functionality does is confuse people as to
what information is actually stored in the table.


HTH
Dale

Hi

I am using this code to filter my form:
Dim strr As String
strr = Me.txtSearch

Me.FilterOn = True
Me.Filter = _
"[Library] like '*" & strr & "*'" _
& " or [Instrument] like '*" & strr & "*'" _
& " or [Location] like '*" & strr & "*'" _
& " or [Description] like '*" & strr & "*'" _
& " or [Comment] like '*" & strr & "*'"

The code works for all fields except the location field. All the fields are
text fields.
I tested the filtering of the Location field by entering various simple
words into the Location field for various records and when I type one of the
those words into Me.txtSearch and execute the code, those records are not
returned. All the other fields are working properly with this code.

Can any tell me why the Location field is not included in the filtered
results.

Thanks