Use Logical Operators in TextBox Criteria to Feed Query

[mrmmickle1]

I've found it hard to find information on this topic online so far. I did find one forum string that said this was not possible.... but I was hoping for a solution.



Right now I have a rules table in a database with many hundreds of queries and reports. The rules table contains fields with parameters that are scattered throughout reports and queries in the database. There are many fields in the rules table and I will try to give a simple example of what I am trying to accomplish. I would like to create a Criteria Table, so that I can do a Dlookup for certain fields that are contained in many queries. So for example. If I had 100 queries with hard coded values I would need to go update 100 queries manually. However if I have a criteria table I can simple update the criteria in the table and it will filter through the 100 queries. So rather than making 100 changes. I only have to make 1.

My problem I can use these fields for individual criteria but I am unable to use them for multiple criteria.

So if i want a criteria to be "United States" this filters the query as expected.... but if I want to do something more complex like IN ("United States", "Germany", "Italy") the query is unable to distinguish the IN as an operator. I imagine that the query reads this all as one big text string.

So my question is How can I use multiple criteria. IN or NOT IN or thinks of this nature? Is there a custom function that will allow me to use this type of syntax in a "criteria table"? AM I missing some special character needed to interpret the operator? Or is this just not possible?

I am trying to rebuild a very large database and some things are scattered everywhere.....so in the rebuild I would like to simply update in one place.... then it filters through the whole database.

Thank you in advance for any help!!


EDIT: I FOUND THIS UDF THAT WORKS LIKE THE IN OPERATOR ..... IS THERE A WAY TO ADAPT IT FOR NOT IN (The Opposite)....

Public Function FindWord(varFindIn As Variant, varWord As Variant) As Boolean


   Const PUNCLIST = """' .,?!:;(){}[]/"
   Dim intPos As Integer
   
   FindWord = False
   
   If Not IsNull(varFindIn) And Not IsNull(varWord) Then
       intPos = InStr(varFindIn, varWord)
       
       ' loop until no instances of sought substring found
       Do While intPos > 0
           ' is it at start of string
           If intPos = 1 Then
               ' is it whole string?
               If Len(varFindIn) = Len(varWord) Then
                   FindWord = True
                   Exit Function
               ' is it followed by a space or punctuation mark?
               ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord), 1)) > 0 Then
                   FindWord = True
                   Exit Function
               End If
           Else
               ' is it precedeed by a space or punctuation mark?
               If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0 Then
                   ' is it at end of string or followed by a space or punctuation mark?
                   If InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord), 1)) > 0 Then
                       FindWord = True
                       Exit Function
                   End If
               End If
           End If
           
           ' remove characters up to end of first instance
           ' of sought substring before looping
           varFindIn = Mid(varFindIn, intPos + 1)
           intPos = InStr(varFindIn, varWord)
       Loop
   End If


End Function

[June7]

Must use VBA to build dynamic IN parameter array.

The quote marks would have to be replaced with apostrophe delimiter so VBA can build by concatenating text with field values and the resulting construct would be like:

SELECT * FROM tablename WHERE Country IN ('United States', 'Germany', 'Italy');

Then what do you want to do with that SELECT statement - open a Recordset, set the RecordSource property of form, modify query using QueryDefs?

Or just build the WHERE clause to set form Filter property?

Now I see your EDIT. Where is that function called from? It only returns a True or False, not a parameter array.

[mrmmickle1]

June7,

Thanks so much for the quick response. The function is called from the query itself like this:

Field = IIf(IsNull([forms]![frmTest]![txtName]),0,FindWord([Forms]![frmTest]![txtName],[EE_Data]![Name]))


Criteria = TRUE

It appears to only work on direct tables though.... I can't get it to work on another query. Is it possible to use this function against a query?

[June7]

The function call is passing values by referencing form controls. Shouldn't matter if the form is bound to table or query.

If you want to reference fields of the query function is called from, that should also work.

[mrmmickle1]

June7,

You're right in my sample I forgot to swap out the Table for the Query of the shorter data set of the table. Thanks for making me see my error!! WooHoo. Now I have something to work with. I really appreciate you taking the time to respond to this post and several other posts I have had over the past few years. It really means a lot! Just curious if you have any opinion or additional suggestions on the scenario I described in my original post?

ALSO AS A NOTE TO OTHERS WHO MAY READ THIS THREAD:

In regards to the use of the function:

Criteria = TRUE is equivalent to IN
Criteria = FALSE is equivalent to NOT IN

[June7]

Nothing to add because i don't fully understand what you are trying to accomplish. I don't get the 'big picture' - how this feature relates to the business model this db supports.