Solved

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

Posted on 2010-11-15
4
411 Views
Last Modified: 2012-05-10
I'm trying to do something that shouldn't be this hard.
Goal:
* 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...
0
Comment
Question by:MrPete_
  • 4
4 Comments
 
LVL 2

Author Comment

by:MrPete_
Comment Utility
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

Action:
  - 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!
Pete
0
 
LVL 2

Author Comment

by:MrPete_
Comment Utility
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!
0
 
LVL 2

Accepted Solution

by:
MrPete_ earned 0 total points
Comment Utility
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")
Q.Properties("FilterOnLoad")=True
  On Error GoTo 0

Open in new window

0
 
LVL 2

Author Comment

by:MrPete_
Comment Utility
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now