[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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
0
garystil
Asked:
garystil
  • 10
  • 7
2 Solutions
 
garystilAuthor Commented:
Experts,
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
0
 
peter57rCommented:
Here are two subs which should enable you to do what you want.
You must bear in mind that you have to match the sql that Access is generatiing - you can't use your own logic for this.

The subs are essentially the same , but the first just finds queries that contain a string and the second replaces one string with another.

Run the first one until you are sure you are finding the right queries.

Sub FindQrys(oldstr As String)
Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
'Debug.Print qdf.SQL
If InStr(qdf.SQL, oldstr) > 0 Then
Debug.Print qdf.Name
Else
' do nothing
End If
Next qdf
End Sub

Sub FindReplaceQrys(oldstr As String, newstr As String)
Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
'Debug.Print qdf.SQL
If InStr(qdf.SQL, oldstr) > 0 Then
Debug.Print qdf.Name
qdf.SQL = Replace(qdf.SQL, oldstr, newstr)
Else
' do nothing
End If
Next qdf
End Sub


In the immedate window do this to get a list of the queries that contain the string..
FindQrys "fieldnamehere) >9"

or you might need to do
FindQrys "fieldnamehere]) >9"
0
 
garystilAuthor Commented:
Thanks Peter. I'm just about to leave, so will look at this later in the day and get back to you.

Gary
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
garystilAuthor Commented:
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
0
 
peter57rCommented:
Please re-read the second line of my response.

Everything takes place in the VBA code window, once you have selected New module from the database window.

Then, you paste the code as I posyed it, into a new standard module.
Choose Debug>Compile from the menu to check all is OK.

***Now take a backup of your application.***

You can run the first sub, which simply lists the queries where your text is found, by clicking into the Immediate window which is at the bottom of the VBA screen.
You put your version of this..

FindQrys "fieldnamehere) >9"
or
FindQrys "fieldnamehere]) >9"
depending on whether you have used [ ] in your queries, or not.
It is probably safer to do both ,one at a time and see whether one or both produce results.

If you are happy that you are getting the correct queries listed you can then run the second procedure :
This time it will be...
FindReplaceQrys "fieldnamehere) >9", "fieldnamehere) >8"
and/or the equivalent using the ] as before.



0
 
garystilAuthor Commented:
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
0
 
peter57rCommented:
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.
0
 
garystilAuthor Commented:
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?
0
 
garystilAuthor Commented:
Peter, I forgot to say that I'm prompted for a Macro Name when I try to run this with the immediate window.
0
 
peter57rCommented:
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)"
0
 
garystilAuthor Commented:
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.

0
 
peter57rCommented:
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.
0
 
garystilAuthor Commented:
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).



0
 
peter57rCommented:
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)"
0
 
garystilAuthor Commented:
I pasted your code, pressed enter and......

Compile Error:

Expected: end of statement.
0
 
peter57rCommented:
Sorry missed a comma out
FindReplaceQrys "F1)>9)" ,"F1)>8)"
0
 
garystilAuthor Commented:
Brilliant. Thanks Peter.  200 points heading in your direction.

Feeling sick wishing I had done this ages ago.

Cheers,
Gary
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now