Solved

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

Posted on 2010-11-15
4
414 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
4 Comments
 
LVL 2

Author Comment

by:MrPete_
ID: 34142416
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_
ID: 34142555
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
ID: 34142655
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_
ID: 34142719
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

734 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