Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2010-11-15
4
Medium Priority
?
418 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 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