Ignore Specific String of Text

[Gicu]

In the latest version in post #29 I used this:

Not Like "ABC[*]Open Item Inc" & "*"

And the total amount in Query1 was consistent with eliminating the three records starting with ABC (@ $5.00 each) but including the ones that have Open and Item. So it looks like you might have something else going on.



An alternative way to deal with it is to create a new query using like that returns the unique ID of the records containing the string you want to eliminate. Then add that to your original query and create a left outer join from the unique IDs of the table to the unique IDs of the new query, add the unique ID from the query to the list of fields and set IS Null in the Criteria row (basically an unmatched query as suggested by Ajax in post #20).

Cheers,
Vlad

[petes1505]

Okay. Let me try that and I'll come back here and post the results. Thanks again for the help. Much appreciated.

[orange]

It may be a syntax issue

Do you have criteria similar to:

WHERE Description NOT Like "ABC[*]Open Item Inc" & "*"

I realize you're getting different data, but perhaps you could show us your query SQL.
We're over 30 posts and still guessing.

Here is another version

' ----------------------------------------------------------------
' Procedure Name: Nov1021
' Purpose: To test NOT Like condition with * within the data and
'   the string to be checked/ignored starts  "ABC*Open Item Inc~"
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 10-Nov-21
'  https://www.accessforums.net/showthread.php?t=84768
' ----------------------------------------------------------------
Sub Nov1021()
          Dim i As Integer
          Dim x(3) As String
10        x(1) = "ABC*Open Item Inc~ and bla bla bla"
20        x(0) = "ABC OPENItem  Inc. etc, etc etc..."
30        x(2) = " ABC*Closed Item Inc~ and related articles to expand the test string"
40          x(3) = "Preamble ABC*Open Item Inc~ and blaa blah blaugh"
50        For i = LBound(x) To UBound(x)
60            If Not x(i) Like "ABC[*]Open Item Inc~*" Then
70                Debug.Print "x(" & i & ")  " & x(i) & " Is NOT like " & "ABC*Open Item Inc~* --- so process this record"
80            Else
90                Debug.Print "x(" & i & ")  " & x(i) & " Is  Like " & "ABC*Open Item Inc~  ----so IGNORE THIS RECORD"
100           End If
110       Next i
End Sub

RESULT:

x(0) ABC OPENItem Inc. etc, etc etc... Is NOT like ABC*Open Item Inc~* --- so process this record
x(1) ABC*Open Item Inc~ and bla bla bla Is Like ABC*Open Item Inc~ ----so IGNORE THIS RECORD
x(2) ABC*Closed Item Inc~ and related articles to expand the test string Is NOT like ABC*Open Item Inc~* --- so process this record
x(3) Preamble ABC*Open Item Inc~ and blaa blah blaugh Is NOT like ABC*Open Item Inc~* --- so process this record

[Micron]

If others are not having the same issue, that indicates there is a difference in the data set being queried and it seems that's going to remain an issue. However, can you try

(NOT LIKE "ABC[*]OPEN ITEM Inc~")

[CJ_London]

to list a few

Post #1 you said Not Like “ABC*Open Item Inc~“
Post #2 suggested NOT LIKE "ABC[*]OPEN ITEM Inc~"
post #4 your saidif I reverse things and use Like "ABC*Open Item Inc~"
post #13 question Shouldn't that be Not Like "ABC*Open Item Inc~" & "*"
post #14 you said Here is an example of what I want it to ignore: ABC*Open Item Inc~
post #21 exampleIf Not x(i) Like "ABC[*]Open Item Inc~" Then

then it changes
post #30 you said when I use the term Like "ABC*Open Item Inc"

so what is with the tilde character ~?

Is it actually part of the criteria string or something else?

You have still not provided an example (made up or otherwise) of a record which is being returned which shouldn't be, or one that should be but isn't
You have still not provided the sql to your query which, for all we know includes other criteria which is messing things up

quite frankly

the query is ignoring any line that includes any of the words listed in the criteria. For example, if a line includes the word Open, the query ignores that line. That is the best example I can provide.

is not good enough.

[petes1505]

An alternative way to deal with it is to create a new query using like that returns the unique ID of the records containing the string you want to eliminate. Then add that to your original query and create a left outer join from the unique IDs of the table to the unique IDs of the new query, add the unique ID from the query to the list of fields and set IS Null in the Criteria row (basically an unmatched query as suggested by Ajax in post #20).

I tried doing this but received an error as it creates a circular reference.

[petes1505]

Hello Orange. If it helps, here is the SQL. The field that contains the text I was referring to is tbl_Invoice2_L.Description.

SELECT tbl_Invoice2_L.SupplierId, tbl_Supplier_L.SupplierName, tbl_Invoice2_L.AccountingDate, tbl_Invoice2_L.Amount, tbl_CompanySite_L.SiteId, tbl_Invoice2_L.CompanySiteId, tbl_CompanySite_L.CompanyLevel2, tbl_CompanySite_L.CompanyLevel3, tbl_Invoice2_L.AccountId, tbl_GLList_L.[GL Account Name], tbl_Invoice2_L.POId, tbl_GLList_L.[Spend Type], tbl_GLList_L.Addressable, tbl_Supplier_L.SupplierType, tbl_Invoice2_L.Description
FROM ((tbl_Invoice2_L LEFT JOIN tbl_Supplier_L ON tbl_Invoice2_L.SupplierId = tbl_Supplier_L.SupplierId) LEFT JOIN tbl_CompanySite_L ON tbl_Invoice2_L.CompanySiteId = tbl_CompanySite_L.SiteId) LEFT JOIN tbl_GLList_L ON tbl_Invoice2_L.AccountId = tbl_GLList_L.AccountID;

[CJ_London]

no idea how you can get a circular reference from a single join. Think you need to pay someone, get them to sign a NDA and pass the problem over to them

[petes1505]

Hello. The tilde character is included in the string. It was a typo on my part to not include it in my later examples.

I just provided the SQL. As for an example, the query is ignoring more than it should, so there is no example to provide.

[CJ_London]

I'm going to drop out, tired of trying to get a straight answer

For the benefit of those remaining this is the sql laid out

SELECT 
tbl_Invoice2_L.SupplierId, 
tbl_Supplier_L.SupplierName, 
tbl_Invoice2_L.AccountingDate,
tbl_Invoice2_L.Amount, 
tbl_CompanySite_L.SiteId, 
tbl_Invoice2_L.CompanySiteId, 
tbl_CompanySite_L.CompanyLevel2, 
tbl_CompanySite_L.CompanyLevel3, 
tbl_Invoice2_L.AccountId, 
tbl_GLList_L.[GL Account Name], 
tbl_Invoice2_L.POId, 
tbl_GLList_L.[Spend Type], 
tbl_GLList_L.Addressable, 
tbl_Supplier_L.SupplierType, 
tbl_Invoice2_L.Description
FROM ((tbl_Invoice2_L LEFT JOIN tbl_Supplier_L ON tbl_Invoice2_L.SupplierId = tbl_Supplier_L.SupplierId)
			LEFT JOIN tbl_CompanySite_L ON tbl_Invoice2_L.CompanySiteId = tbl_CompanySite_L.SiteId) 
				LEFT JOIN tbl_GLList_L ON tbl_Invoice2_L.AccountId = tbl_GLList_L.AccountID;

Unfortunately, the bit that matters (the criteria) is not included

[petes1505]

Ajax,

There is no criteria. All this query does is pull data from the ten tables and fields listed in the SQL. What I am try to do is add criteria. In tbl_Invoice_L.Description i want it to ignore any line that begins with OTI*Open Item Inc~. That is all I am trying to do.

[petes1505]

no idea how you can get a circular reference from a single join. Think you need to pay someone, get them to sign a NDA and pass the problem over to them

An alternative way to deal with it is to create a new query using like that returns the unique ID of the records containing the string you want to eliminate. Then add that to your original query and create a left outer join from the unique IDs of the table to the unique IDs of the new query, add the unique ID from the query to the list of fields and set IS Null in the Criteria row (basically an unmatched query as suggested by Ajax in post #20).

Ajax,

You were correct and your alternative method solved the issue. When I created the initial query I did so incorrectly. I was staring at the screen for hours and guess I needed to walk away for a while. Once I recognized the error and updated the query it worked perfectly.

Thank you and everyone on this thread who tried to help, and sorry for any confusion I may have caused. I've only been working with Access for a short while but I'm pretty determined. Again, the help is very much appreciated.

Pete

[CJ_London]

You are also new to this forum. For the future, if you are having a problem like this, provide the full sql - it was requested back in post #9. It took 26 posts before you provided it and even then not the whole sql so you wasted a lot of peoples time.