Work around apparent bug: Filter contains query name??!

I'm trying to do something that shouldn't be this hard.
* Start with Query A that a user has filtered (in Datasheet view) in some unknown way
* Save the Query as Query B
* Open Query B and apply the filter

The problem (you can check this):
* if I open ANY query in datasheet view
* Add one or more filters (using the dropdown filters -- I'm doing this by choosing a few text values from the list for a text field)
* Switch to SQL view and open the Properties sheet to see the "Filter" value for the query
* PROBLEM: you will see the query name prepended inside the filter

At this point, that query name (Query A) will stay in the filter... even if you save the query as a new query, copy to a new query, etc...

And because that name is in there, if you attempt to activate the filter in the newly named and/or copy of the query, it will blow up, asking you for values of the filter variables.

Seems a workaround would be to either eliminate this problem, or in VBA modify the text value of the Filter property.

I am (unfortunately) on a tight deadline to find a workaround for this. (less than 12 hours from now... and I need some sleep in there :) )

In my case, the process of copying/replicating the query is under VB control... so one great solution would be information on how to "fix" the filter value when saving or copying the query. If I do that by hand, the new query does work fine...
Who is Participating?

Improve company productivity with a Business Account.Sign Up

MrPete_Connect With a Mentor Author Commented:
I'm getting close. Have learned a few things through trial and error...

[BEFORE obtaining the filter value, the query must be saved and/or copied elsewhere. An unsaved query's properties do not match the current reality...]

See attached code for most of what I need.

Now to mess with the filter string...
<save the query>
Set DB = CurrentDb()
Set Q = DB.QueryDefs(strQry)
  On Error Resume Next
  strFilter = Q.Properties("Filter")
  strFiltFix = "[" & strQry & "]."
  isFiltOnLoad = Q.Properties("FilterOnLoad")
  On Error GoTo 0

Open in new window

MrPete_Author Commented:
I think I can simplify (?) the problem to the following goal for a VBA function/subroutine/code:

Input: sQryName is the text name of a query in the current DB

  - obtain sFilterText, the (text) value of the Filter property
  - change sFilterText as follows:
        construct the following string: "[" & sQryName & "]."
        remove all copies of that string from sFilterText
  - set the Filter property to the new sFilterText value
  - set the Filter On Load property of sQryName to TRUE

From there, I have code that will save and/or copy the query as needed.

AND, once I have good code to accomplish the above steps, if there's a problem I can use the same techniques to fix it (eg if the copied query does not Filter On Load, I can turn it on for the new query as well)

THANK YOU for any help you can provide!
MrPete_Author Commented:
As a bonus, to help any others who come along: I have discovered another valuable purpose for editing the Filter property:

One of my scripts takes an existing query and removes duplicates (for mailing list purposes) by grouping the entries on name, address, etc.

However, this is invalidated by a filter operation because for grouping one needs the filtration *as part of* the query rather than *after* the query.

So, if I can pull the Filter expression from query properties, and insert it as a "WHERE" clause, I'll be all set!
MrPete_Author Commented:
Looks like I was able to solve this myself. Here's the last tidbit:
 strFilter = Q.Properties("Filter")
 strFiltFix = "[" & strQry & "]."
 strFiltNew = Trim(Replace(strFilter, strFiltFix, ""))
I don't know why MS "installed" this bug, but the above combination of VB effects is a usable workaround.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.