Ignore Specific String of Text

[petes1505]

Must it remain or can you run an update query to replace it with a space? What happens if you remove it from a couple of records and re-test?

The data is refreshed weekly, so I am trying to make it as automated as possible. If an update query had to be run for every refresh it would introduce a manual step into the process.

[CJ_London]

The results are totals, not line-by-line detail. When adding the criteria I'm expecting the query to return $800. It's not....it is returning something less than $800.

so take off the grouping so you can see the line by line detail, find what records are missing, and work out why

[petes1505]

so take off the grouping so you can see the line by line detail, find what records are missing, and work out why

There are a bit more than 15,000 lines in the table.

[Micron]

If the import is automatic, why can't the update be part of that?

[CJ_London]

well you won't provide us with anything meaningful so can't suggest anything else.

You have heard of left joins? so left join your transaction table to the ungrouped query on the PK and find those transaction records that don't have a matching PK. And $1000 seems a very small amount for 15000 records which implies there is something else you are not telling us

Based on what you have told us, Minty's suggestion way back at post#2 should solve the problem. The fact we are now at post 19 and no further forward means you have not revealed anything else to help us help you.

[orange]

Here's a mock up. Hope it's helpful.

 ----------------------------------------------------------------
' Procedure Name: Nov1021
' Purpose: To test Like condition with * within the data
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 10-Nov-21
' ----------------------------------------------------------------
Sub Nov1021()
          Dim i As Integer
          Dim x(2) As String
10        x(1) = "ABC*Open Item Inc~"
20        x(0) = "ABC OPENItem  Inc."
30        x(2) = " ABC*Closed Item Inc~"
40        For i = LBound(x) To UBound(x)
50            If Not x(i) Like "ABC[*]Open Item Inc~" Then
60                Debug.Print "x(" & i & ")  Is NOT Like " & "ABC Open*Item Inc~"
70            Else
80                Debug.Print "x(" & i & ")  Is  Like " & "ABC Open*Item Inc~"
90            End If
100       Next i
End Sub

With this output:

x(0) Is NOT Like ABC Open*Item Inc~
x(1) Is Like ABC Open*Item Inc~
x(2) Is NOT Like ABC Open*Item Inc~

[petes1505]

Hello Orange,

Thank you for the mock up, but I'm confused. What is the purpose and where is this used?

[orange]

Sorry had * in wrong position in print out

edited sub

' ----------------------------------------------------------------
' Procedure Name: Nov1021
' Purpose: To test Like condition with * within the data
' 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(2) As String
10        x(1) = "ABC*Open Item Inc~"
20        x(0) = "ABC OPENItem  Inc."
30        x(2) = " ABC*Closed Item Inc~"
40        For i = LBound(x) To UBound(x)
50            If Not x(i) Like "ABC[*]Open Item Inc~" Then
60                Debug.Print "x(" & i & ")  Is NOT Like " & "ABC*Open Item Inc~"
70            Else
80                Debug.Print "x(" & i & ")  Is  Like " & "ABC*Open Item Inc~"
90            End If
100       Next i
End Sub

Result:

x(0) Is NOT Like ABC*Open Item Inc~
x(1) Is Like ABC*Open Item Inc~
x(2) Is NOT Like ABC*Open Item Inc~

Purpose to show Not Like with an "*" embedded in the data/target string

Putting [ ] around the *.

[Gicu]

Sorry to ask but isn't Not Like "ABC[*]Open Item Inc~" similar to <> "ABC*Open Item Inc~"

With like I always use a wildcard character, otherwise it acts like an equal sign, does it not?

So shouldn't the criteria be Not Like "ABC[*]Open Item Inc~" & "*" to eliminate those records that start with that specific string?

Cheers,

[orange]

@vlad
I agree.
I can substitute this for the not like and get same results
50 If x(i) <> "ABC*Open Item Inc~" Then
or
50 If Not x(i) = "ABC*Open Item Inc~" Then
But you then have to remove the [ ] surrounding the *.

[CJ_London]

the implication from the OP is 'ABC*Open Item Inc~' is in the description field rather than description field = ABC*Open Item Inc~. - so examples (asked for but not provided) might be 'ABC*Open Item Inc~123' or '123 ABC*Open Item Inc~' or '123 ABC*Open Item Inc~ xyz'.

If indeed it is = (or<>) we have all been going up the wrong path for the last 6 hours

[Gicu]

@ Ajax - my understanding was that the description field has records that start with 'ABC*Open Item Inc~' as you describe. That's why I suggested the addition of the wildcard character after it (& "*") that was missing in the first posts.

Cheers,

[petes1505]

Everyone,

I appreciate the help....truly do, and the last thing I want to do is waste anyone's time. Nobody has been going down the wrong road. Here is what is known:

1. There is a payables table with 15,000 lines plus that is refreshed weekly
2. Within that table there are multiple columns, one of them being Description in unformatted text which includes information about the payment (added by a third-party)
3. There is a specific string of text that appears in the Description field that I want the query to ignore, and in each case the string begins with ABC*Open Item Inc
4. The query result is a sum total of the amount paid of all of the lines, not a line listing
5. When using Not Like "ABC*Open Item Inc" as the criteria, 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.
6. I've tried other variations, including <>, placing brackets [] around the *, adding &"*" to the end of the query, and in each case the query is ignoring data other than what I am looking for it to ignore.

When this began I thought it would be an easy question; what is the criteria for telling Access to ignore a line if a specific field begins with ABC*Open Item Inc

Thanks,
Pete

[Gicu]

Pete, have you had a chance to look at my sample, here it is again. I do not get the same results, so maybe show us the query in design view at least.

Cheers,

[petes1505]

Hi Gicu,

Yes, I did. I downloaded and did a copy/paste of the criteria just to make sure I wasn't making any keying errors. The same result as when I typed it in earlier.

I think it has something to do with using the term Not Like. I mentioned earlier that when I use the term Like "ABC*Open Item Inc" and run the query, it pulls up the exact information. The query pulls up only those lines that begin with that string. When I use Not Like "ABC*Open Item Inc" the query returns data that does not contain any of the string rather than just the entire specific string.

Just to be clear, when I say when I use the term Like "ABC*Open Item Inc" and run the query, it pulls up the exact information what I mean is it pulls the exact lines I am trying to have the query ignore if I use Not Like.