Changing Query Filter for multiple Queries

[SDamico]

I am working with several databases that each have 20+ different queries.
Most of the queries have the same filter (like 200*)to restrict only the
dates after 12/31/1999. In 2010, however, the queries do not pick up any of
the data for 2010. Therefore, all of the queries that use that filter need to
be changed. Is there any way to handle this change systematically? Thanks so
much

 

[John W. Vinson]

I am working with several databases that each have 20+ different queries.
Most of the queries have the same filter (like 200*)to restrict only the
dates after 12/31/1999. In 2010, however, the queries do not pick up any of
the data for 2010. Therefore, all of the queries that use that filter need to
be changed. Is there any way to handle this change systematically? Thanks so
much

Well, that's a lousy filter, I'm sorry to say! Not only do you have a Y2.1K
problem, but you are treating date fields as if they were text strings...
which they aren't. A critierion of

= #1/1/2000#

would be much faster, simpler, and would avoid this problem.

That said, there's no *reliable* way to do this, since the criterion could be
expressed differently in different queries. You could loop through the
Querydefs collection of the current database object, and use Replace() to
replace all instances of "LIKE 200*" with ">= #1/1/2000#", but I would not
wager a thin dime on all of the resulting queries working. It would be well
worth a few minutes of your time to open and manually fix the queries!

 

[SDamico]

Thanks for the reply.

Well, that's a lousy filter, I'm sorry to say! Not only do you have a Y2.1K
problem, but you are treating date fields as if they were text strings...
which they aren't. A critierion of



would be much faster, simpler, and would avoid this problem.

That said, there's no *reliable* way to do this, since the criterion could be
expressed differently in different queries. You could loop through the
Querydefs collection of the current database object, and use Replace() to
replace all instances of "LIKE 200*" with ">= #1/1/2000#", but I would not
wager a thin dime on all of the resulting queries working. It would be well
worth a few minutes of your time to open and manually fix the queries!