Solved

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

Posted on 2010-11-15
4
412 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_
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access report groups with sums 5 29
Access on Mouse move 5 35
Calculation in Access 5 26
Access subform not displaying on tab in naviation form 5 30
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

832 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