garystil
asked on
Identifying queries with same criteria
My database has over a hundred different queries and all queries contains a field name called F1.
The criteria I have set for F1 varies. It could be >8 , >9 or <12
I have 2 questions:
1. If I want to identify (and perhaps display) all queries which have F1 set at >9 , what would I do?
2. If I want to change the criteria in F1 from >9 to >8 in all those queries, what would I do?
I use Access 2000 and Windows XP.
Thanks,
Gary
The criteria I have set for F1 varies. It could be >8 , >9 or <12
I have 2 questions:
1. If I want to identify (and perhaps display) all queries which have F1 set at >9 , what would I do?
2. If I want to change the criteria in F1 from >9 to >8 in all those queries, what would I do?
I use Access 2000 and Windows XP.
Thanks,
Gary
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Peter. I'm just about to leave, so will look at this later in the day and get back to you.
Gary
Gary
ASKER
I'm back. I barely have any experience at this Peter, so would appreciate it if you could insert the actual F1, >9 etc criteria in the code and tell me precisely where I paste this.
Thanks, Gary
Thanks, Gary
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've tried to follow.
I've pasted the code in the module. Debug/compile seems fine. I then run the first sub, but I don't see a list of relevant queries (nothing happens). I pasted FindQrys "F1) >9" in immediate window, but again nothing happens.
Gary
I've pasted the code in the module. Debug/compile seems fine. I then run the first sub, but I don't see a list of relevant queries (nothing happens). I pasted FindQrys "F1) >9" in immediate window, but again nothing happens.
Gary
I suggest you find one query that you know has this condition, and look at the sql view of that query to see how the condition is expressed.
Post the sql view here if you want.
Post the sql view here if you want.
ASKER
SELECT OILS.Date1, OILS.Name, OILS.R1, OILS.F1, OILS.[No], OILS.Date2, OILS.R2, OILS.Fld, OILS.No2, OILS.nPP, OILS.[1Fno]
FROM OILS
WHERE (((OILS.F1)>9) AND ((OILS.No2)<10) AND ((OILS.nPP)>1) AND ((OILS.[1Fno])>1 And (OILS.[1Fno])<10));
See above for sample qry.
When I ran the module earlier, was I meant to somehow create a table/report into which the module could export its results?
FROM OILS
WHERE (((OILS.F1)>9) AND ((OILS.No2)<10) AND ((OILS.nPP)>1) AND ((OILS.[1Fno])>1 And (OILS.[1Fno])<10));
See above for sample qry.
When I ran the module earlier, was I meant to somehow create a table/report into which the module could export its results?
ASKER
Peter, I forgot to say that I'm prompted for a Macro Name when I try to run this with the immediate window.
That suggests you've put the code in a form module rather than a standard module.
You need to create a new module from the database window and paste it into that. (Make sure the code is gone from the form module)
Looking ar your sql then you would need:
FindQrys "F1)>9)"
You need to create a new module from the database window and paste it into that. (Make sure the code is gone from the form module)
Looking ar your sql then you would need:
FindQrys "F1)>9)"
ASKER
As far as I can tell it is a standard module.
I go to modules and click new. I paste in your code, click debug/compile, then run the code but with no output.
I then paste FindQrys "F1)>9)" in immediate window and I'm prompted for a Macro Name. (I should point out that I do have a Macro and another module in the database.
I go to modules and click new. I paste in your code, click debug/compile, then run the code but with no output.
I then paste FindQrys "F1)>9)" in immediate window and I'm prompted for a Macro Name. (I should point out that I do have a Macro and another module in the database.
You don't have to run the code. That is what is producing the Macros dialog box.
You just use the immediate window.
Paste FindQrys "F1)>9)" and press Enter
This should produce at least the name of the query you posted above.
You just use the immediate window.
Paste FindQrys "F1)>9)" and press Enter
This should produce at least the name of the query you posted above.
ASKER
It did the job. That's fantastic!
And it was just a matter of pressing ENTER.... Thanks Peter. Sorry for stuffing you around. It would have been quickly resolved if I'd known my way around.
With the 2nd stage of my question, if I want to change F1 from >9 to >8, what exactly do I paste in the immediate window? (assuming thats where it goes).
And it was just a matter of pressing ENTER.... Thanks Peter. Sorry for stuffing you around. It would have been quickly resolved if I'd known my way around.
With the 2nd stage of my question, if I want to change F1 from >9 to >8, what exactly do I paste in the immediate window? (assuming thats where it goes).
Make sure you have a backup.
You now have a list of queries that have been found, so you will be able to check some of them afterwards.
Use:
FindReplaceQrys "F1)>9)" "F1)>8)"
You now have a list of queries that have been found, so you will be able to check some of them afterwards.
Use:
FindReplaceQrys "F1)>9)" "F1)>8)"
ASKER
I pasted your code, pressed enter and......
Compile Error:
Expected: end of statement.
Compile Error:
Expected: end of statement.
Sorry missed a comma out
FindReplaceQrys "F1)>9)" ,"F1)>8)"
FindReplaceQrys "F1)>9)" ,"F1)>8)"
ASKER
Brilliant. Thanks Peter. 200 points heading in your direction.
Feeling sick wishing I had done this ages ago.
Cheers,
Gary
Feeling sick wishing I had done this ages ago.
Cheers,
Gary
ASKER
II' the first question can't be resolved easily, I'd be happy if I could have an answer to the 2nd question.
Thanks,
Gary