Filter a query with DoCmd?

[MFrost]

Hi folks,

I have a little challenge on my hands at the moment and cant seem to google/search/code up the answer myself (if there is one).
I currently have a loop in VBA that does (in it's simplified form)

DoCmd.OpenQuery
DoCmd.PrintOut
DoCmd.Close

For a few hundred queries. What i now have the challenge of doing is filtering the query using VBA in the same way as you would manually (open the query, right click column, filter for, "text here", then print.)
The reason I dont want to edit the queries is firstly because there are so many and secondly, a bit of vba is far more efficient.

Does anyone know if this is possible? Please note that all the queries are oracle pass through queries, not access queries, which may/may not make things a little harder?

Thank you so much for any help. I look forward to any response.

 

[DCrake]

Is there any logic to the filter string for the queries or will it be random based on individual queries?

Don't forget that you can create an Access query from a Oracle query in much the same way you would with an Access table.

Why do you have so many queries? is there a common flavour to them?

David

 

[MFrost]

Hi DCrake

There is very much a logic to filtering for specific strings. There will be about 8 strings i will want to filter for on the same column name of every query.

We have so many queries because we are a 'data control' department as such and all of these queries are checking for 'abnormalities'. According to what area the abnormalities are in they are sent to the correct person to deal with them. There is already a column on all the queries that i can split these up by, it's just a case of working out how without too much changed to the existing queries because i still need the queries in the state they are for other uses.

I understand that an access query can be created, i did have a read over that but the work load involved in creating all the new queries is fairly high.

Thanks for the quick reply!

 

[DCrake]

Ok lets say we are searching 1 field for the occurance of 8 different phrases or words we can create a function to handle this

Public Function FindPhrase(Anystring As String) As Boolean

Dim bFlag as Boolean

'Test for first phrase or word

If InStr(Anystring,"Hello") > 0 Then
   bFlag = True
End If

'Second test
If InStr(Anystring,"Monday") > 0 Then
   bFlag = True
End If

Repeat for each know string to search for

FindPhrase = bFlag

End Function

Then in a single query enter the column

Wanted: FindPhrase([YourTextFieldHere])

This will then run the function for each block of text in each record. If any of the conditions are met then True will be returned.

Finally in your condition row enter True

So that the resulting query will only show records that contain the know phrase or phrases in the record.

Also can more than one phrase appear in the same block of text?

David

 

[MFrost]

DCrake I owe you a pint!

This is very good, small, easy to modify, and almost exactly what I was after (i wont need to change any of my queries ), but i never would have thought of that myself!

Extremely helpful, and also absurdly fast! Well done.

With regards to there being multiple phrases in the same block, that's not going to happen thankfully.

Thanks a million, i really appreciate your help.

Take care.

(a very happy) MFrost

p.s. I may be back in this thread if i run into trouble, but i think i've got an idea of how to get it doing exactly what i'm after.

 

[DCrake]

Glad you got things solved