Query Exclusion List

[Zippersabrat]

Hi all! I have an Access Database that I am using to track the difference in product codes between two different external databases. I export the codes from both and import to their own table. I then run two queries, one to show what is missing from one and another to show what is missing from the other. I have a ton of exceptions that I have built out in the Expression Builder as seen below. I am wondering if I can create a table to track these so I can make it more user-friendly to allow others on my team to edit. Ideally, I could have a table and the a form for them to add to the list. Not sure if this is even possible so thought I would reach out to the experts before I even try to come up with something. Thanks in advance!

 

[theDBguy]

Is that the expression in your query criteria?

I think you can store them in a table and join that table in your query to automatically filter the data.

 

[plog]

Yes, you can put them in a table (CodeTable) and then LEFT JOIN from that table to the one you have (MainTable) to achieve the results you want.

Preferably you would breakout all the codes in MainTable to their own field. Perhaps all those prefixes are followed by a dash and then other data (e.g. VX-12378, VX-9877, etc). That way you can put the VX in one field and the 12378 and 9877 in another. Then its a striaght LEFT JOIN from CodeTable to identify matches.


...LEFT JOIN CodeTable ON CodeTable.Code = MainTable.Code...

If not, you can use a non-equi join. 90% of the time a LEFT JOIN looks like the above, but it doesn't have to be an equals sign. If you can't break the code out into its own field you can do this:

...LEFT JOIN CodeTable ON MainTable.WholeCode LIKE CodeTable.Code & "*"...

I am sure that syntax is incorrect with respect to the wildcard character. But it demonstrates that you don't have to use an equals sign in your LEFT JOIN.

 

[MajP]

Maybe a table like this.
tblExclusions tblExclusions

ExclusionID	ExclusionText	ExclusionType
1​	IDR	Beginning
2​	PKG	Anywhere
3​	MRK	Anywhere
4​	TR_0017	Match
5​	TST	Ending

Public Function GetExclusions(YourFieldName As String) As String
  Dim strEx As String
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblExclusions")
  Do While Not rs.EOF
    Select Case rs!exclusionType
      Case "Anywhere"
        strEx = "'*" & rs!ExclusionText & "*'"
      Case "Beginning"
        strEx = "'" & rs!ExclusionText & "*'"
      Case "Ending"
        strEx = "'*" & rs!ExclusionText & "'"

      Case "Match"
        strEx = "'" & rs!ExclusionText & "'"
     End Select
     If GetExclusions = "" Then
       GetExclusions = YourFieldName & " NOT LIKE " & strEx
     Else
       GetExclusions = GetExclusions & " AND " & YourFieldName & " NOT LIKE " & strEx
     End If
    rs.MoveNext
  Loop

End Function

Results passing in Fld1 as the field name

fldone NOT LIKE 'IDR*' AND fldone NOT LIKE '*PKG*' AND fldone NOT LIKE '*MRK*' AND fldone NOT LIKE 'TR_0017' AND fldone NOT LIKE '*TST'

Then have a form with a button that gets the string above and updates and opens the query.

Probably should have use the terms "Starts with", "Ends With" instead of beginning and ending.

 

[arnelgp]

you can create a table with those exclusion list (first letters )

then create Function that will check this Table:

Public Function IsExcluded(ByVal pFieldValue As Variant) As Boolean
    Dim iX As Integer
    Dim rs As DAO.Recordset
    pFieldValue = Trim$(pFieldValue & "")
    iX = Len(pFieldValue)
    If iX < 1 Then
        Exit Function
    End If
    With CurrentDb.OpenRecordset("tblExlusion", dbOpenSnapshot, dbReadOnly)
        .MoveFirst
        Do Until .EOF
            IsExcluded = (pFieldValue Like ![Excluded] & "*")
            If IsExcluded Then Exit Do
            .MoveNext
        Loop
    End With
End Function

your Criteria in Query:

Where IsExcluded([theFieldToCheck]) = False

 

[The_Doc_Man]

It may be easier from a logic standpoint to reverse your logic. Add flag fields to show EXCLUDED (perhaps as a Boolean or Yes/No field).
Then put those things in a table, call it EXCL.

EXCLUDED: KeyValue
where the KeyValue is things like "IDR*" or "VX*" or "PKG*"

Then do an update:

UPDATE MainTable SET EXCLUDED = 0'
UPDATE MainTable INNER JOIN EXCL ON MainTable.MyField LIKE EXCL.KeyValue SET MainTable.EXCLUDED = 1;

Now use the EXCLUDED flag in your other query's WHERE clause to exclude the records that were marked as such. This approach might actually be faster than that ugly exclusion list, and has the further advantage that you can add other stuff to the exclusions list because now it is just a table.