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)....
Code:
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