Access 2007 Freezes on Some Form Filters

hk1

Registered User.
Local time
Today, 02:42
Joined
Sep 1, 2009
Messages
121
OK, so I'm editing this post because it was incorrect. I thought ampersands where causing my problem but that wasn't true.

It appears that what is causing Access to freeze is long filters with lots of fields. I'm allowing my users to perform an "any field" search which means I parse out their search and check to see if each character or string of characters exists in any of numerous fields. Here's a typical example:

Code:
([strFileAs] Like '*b*' 
OR  [FirstName] Like '*b*' 
OR  [LastName] Like '*b*' 
OR  [Company] Like '*b*' 
OR  [CustClass] Like '*b*' 
OR  [BusinessPhone] Like '*b*' 
OR  [PersonalPhone] Like '*b*' 
OR  [MobilePhone] Like '*b*' 
OR  [BusinessFax] Like '*b*' 
OR  [Phone2] Like '*b*' 
OR  [Pager] Like '*b*' 
OR  [EmailAddress] Like '*b*' 
OR  [website] Like '*b*' 
OR  [city] Like '*b*' 
OR  [State] Like '*b*' 
OR  [Country] Like '*b*' 
OR  [Zip] Like '*b*')
AND
([strFileAs] Like '*&*' 
OR  [FirstName] Like '*&*' 
OR  [LastName] Like '*&*' 
OR  [Company] Like '*&*' 
OR  [CustClass] Like '*&*' 
OR  [BusinessPhone] Like '*&*' 
OR  [PersonalPhone] Like '*&*' 
OR  [MobilePhone] Like '*&*' 
OR  [BusinessFax] Like '*&*' 
OR  [Phone2] Like '*&*' 
OR  [Pager] Like '*&*' 
OR  [EmailAddress] Like '*&*' 
OR  [website] Like '*&*' 
OR  [city] Like '*&*' 
OR  [State] Like '*&*' 
OR  [Country] Like '*&*' 
OR  [Zip] Like '*&*')
AND
([strFileAs] Like '*m*' 
OR  [FirstName] Like '*m*' 
OR  [LastName] Like '*m*' 
OR  [Company] Like '*m*' 
OR  [CustClass] Like '*m*' 
OR  [BusinessPhone] Like '*m*' 
OR  [PersonalPhone] Like '*m*' 
OR  [MobilePhone] Like '*m*' 
OR  [BusinessFax] Like '*m*' 
OR  [Phone2] Like '*m*' 
OR  [Pager] Like '*m*' 
OR  [EmailAddress] Like '*m*' 
OR  [website] Like '*m*' 
OR  [city] Like '*m*' 
OR  [State] Like '*m*' 
OR  [Country] Like '*m*' 
OR  [Zip] Like '*m*')

Apparently Access isn't too crazy about these kinds of filters so it sometimes freezes. I think I'm just going to have to reduce the size of the filter when the user searches for longer search terms (i.e. long strings with more spaces).
 
Last edited:
Look into using the "Tag" property... You can cut that code WAY down!
 
Could you elaborate a little bit on what you mean? I thought the tag property is something you use on a textbox on form.

I do have filters that are far more efficient but users like to try the Any Field filter first since it works for almost anything and is quite accurate. Of course, the downside is that it's not efficient.
 

Users who are viewing this thread

Back
Top Bottom