Help with Instr Function (1 Viewer)

[silentwolf]

Hi guys,

just wondering if someone could help me with this below function=


I like to find words starting with AT followed by any digits and the same with DE followed with digits

This is what I got so far..

Public Function ReturnZahlung(strText As String) As String

    If strText Like "Überweisung*" Then
        mStartPos = InStr(strText, ":")
    
        If mStartPos = 35 Then
            mEndPos = InStr(strText, "AT")
        If mEndPos > 0 Then
            mStartPos = mStartPos + 1
            mEndPos = mEndPos - 2
            mIntLength = mEndPos - mStartPos
            ReturnZahlung = Mid(strText, mStartPos + 1, mIntLength)
        Else
            mEndPos = InStr(strText, "DE")
                If mEndPos > 0 Then
                    mStartPos = mStartPos + 1
                    mEndPos = mEndPos - 2
                    mIntLength = mEndPos - mStartPos
                    ReturnZahlung = Mid(strText, mStartPos + 1, mIntLength)
                End If
            End If
        End If
    End If
End Function

Problem it finds just AT or DE

I tried AT[0-9] but that did not work.

unfortunatelly I am not really a regex expert so I am using instr instead,

Many thanks

Albert

 

[Gasman]

Wait for @arnelgp as one of them.
He is a regex expert amongst a few others here.

 

[silentwolf]

Thanks Gasman I will do that

maybe arnelgp could also let me know what to do if you like to replace some string with blanks with regex ;-)
That would be fantastic

Cheers

 

[CJ_London]

Any reason you can't use?

If strText Like "Überweisung:AT*DE*"

or
If strText Like "Überweisung:*AT*DE*"

Some example data with the outcome required would help

for other like operators, see this link

Like operator (Microsoft Access SQL)

Office VBA reference topic

learn.microsoft.com

Other ways is to use the split function

Assuming your text is

"Überweisung:1234AT4567DE2000*"

Public Function ReturnZahlung(strText As String) As String


'you want the value between : and AT
if strtext like "Überweisung:*AT*DE*" then
 
     ReturnZahlung= split(strText,":")(1)
     ReturnZahlung=split( ReturnZahlung,"AT")(0)

    'or combine
     ReturnZahlung= split(split(strText,":")(1),"AT")(0)
end if

' or you want the value between AT and DE  
if strtext like "Überweisung:*AT*DE*" then
 
     ReturnZahlung= split(strText,"AT")(1)
     ReturnZahlung=split( ReturnZahlung,"DE")(0)

     'or just
     ReturnZahlung= split(split(strText,"AT")(1),"DE")(0)

end if

' or you want the value after DE  
if strtext like "Überweisung:*AT*DE*" then
 
     ReturnZahlung=split( ReturnZahlung,"DE")(1)

end if
   
end function

in the immediate window
?split(split("Überweisung:1234AT4567DE2000*",":")(1),"AT")(0)
1234
?split(split("Überweisung:1234AT4567DE2000*","AT")(1),"DE")(0)
4567
?split("Überweisung:1234AT4567DE2000*","DE")(1)
2000*

 

[Josef P.]

Are you looking for the IBAN position?

public Function IbanPos(ByVal TextToCheck As String, Optional ByRef Iban As String) As Long

   Const Pattern As String = "(AT(?:\s*\d\s*){18}|DE(?:\s*\d\s*){20})"

   Static RegEx As Object
   Dim Matches As Object
   Dim Match As Object

   If RegEx Is Nothing Then
      Set RegEx = CreateObject("VBScript.RegExp")
      With RegEx
         .Global = True
         .IgnoreCase = False
         .Pattern = Pattern
      End With
   End If

   Set Matches = RegEx.Execute(TextToCheck)
  
   If Matches.Count > 0 Then
      IbanPos = Matches(0).FirstIndex + 1
      Iban = Matches(0).Value
   End If

End Function

Unit test code:

'AccUnit:Row("1234AT345678901234567890 xxx", 5)
'AccUnit:Row("123AT678AT34 5678 9012 3456 7890xxx", 9)
'AccUnit:Row("1234AT34567890123456789 xxx", 0)
'AccUnit:Row("1234DE34567890123456789012xxx", 5)
'AccUnit:Row("123DE678DE34567890123456789012xxx", 9)
'AccUnit:Row("123DE678DE3456789012345678901xxx", 0)
Public Sub IbanPos_CheckStartPos(ByVal Text2Check As String, ByVal ExpectedStartPos As Long)
   Dim Actual As Long
   Actual = IbanPos(Text2Check)
   Assert.That Actual, Iz.EqualTo(ExpectedStartPos)
End Sub

 

[silentwolf]

Hi CJ,
the issue is that "Überweisung does continue with Dates so each of them is different and the double dots are at the end of that date.

e.g. "ÜBERWEISUNG INTERNET AM 2022-06-30: "

others are without ":" "ÜBERWEISUNG INTERNET AM 2022-08-12 AT1245xxxx" and have a different pattern so those need to be excluded from the function.

But for those with"double dots" I like to get the part in bold out "ÜBERWEISUNG INTERNET AM 2022-05-29:"78232Payment Teilzahlung Heizung AT684XXX"

 

[silentwolf]

Hi Josef,

yes I need to get the Iban position
just testing your code.. but how can I get reg ex to get this part out as dicriped in my previous post

 

[cheekybuddha]

VBA Like Operator

If strtext Like "AT#*" Or strtext  Like "DE#*" Then
' ...

 

[silentwolf]

Hi David,

thanks yes I was trying that too..

now I have it sorted but I am sure there is a much better way of doing it.

Maybe someone could adjust it a bit so it is a little nicer.

Public Function IbanPos(ByVal TextToCheck As String, Optional ByRef Iban As String) As Long

   Const Pattern As String = "(AT(?:\s*\d\s*){18}|DE(?:\s*\d\s*){20})"

   Static RegEx As Object
   Dim Matches As Object
   Dim Match As Object

   If RegEx Is Nothing Then
      Set RegEx = CreateObject("VBScript.RegExp")
      With RegEx
         .Global = True
         .IgnoreCase = False
         .Pattern = Pattern
      End With
   End If

   Set Matches = RegEx.Execute(TextToCheck)
 
   If Matches.Count > 0 Then
      IbanPos = Matches(0).FirstIndex + 1
      Iban = Matches(0).Value
   End If

End Function

Public Function ReturnZahlung(strText As String) As String

    If strText Like "*AT#*" Or strText Like "*DE#*" Then
        mStartPos = InStr(strText, ":")
        If mStartPos = 0 Then
            ReturnZahlung = ""
        Else
            mEndPos = IbanPos(strText) - 2
            If mStartPos < mEndPos Then
                mIntLength = mEndPos - mStartPos
                ReturnZahlung = Mid(strText, mStartPos + 1, mIntLength)
            Else
                ReturnZahlung = ""
            End If
        End If
    End If
    
End Function

 

[ebs17]

The first request = determining an IBAN has been solved.
Matches(0).Value returns the determined string
Matches(0).FirstIndex returns its starting position (0-based)
Matches(0).Length returns the string length

This would allow you to cut out this match from the overall string and limit your subsequent analysis to the rest.
You could proceed in the same way with other parts you are looking for (date, reason for payment) if you can reliably create the necessary patterns based on variants that occur. Finding a pattern for “free writing” is not easy.

The overall task seems to me to be that you want to isolate certain information from an entire text, so to speak, to normalize it.
I would consider it advantageous to have a list of such complete texts that contains all conceivable variants, especially the problematic outliers. Then you can better define a “simple” process that then works across everything.
In such cases, for example, I inserted my own separators using RegEx, which I then used to isolate and process individual elements using a simple split.

 

[silentwolf]

Hi Eberhard,
thanks for the info.. I am still working on how to have a working system to isolate parts of the string but I slowly getting there.
As you mentioned using a seperator is surely an option.

To start off I am using filters to limit Text Patterns.
I guess that would also speed up the query.

I will keep you guys posted on how I am going )


Cheers

 

[cheekybuddha]

Please post several examples of the different strings you need to parse, and also the expected result for each variation.

My guess is that a single regex function will be all you need.

 

[silentwolf]

Ok..)
First line is the string
Second the expected result
1.) 000001222098 4377015051912129 ( 1 UMS) AT173900000001093566 / RZKTAT2KXXX / Unser Lagerhaus Gerätewartung Rasenmäher 1222098
2.) Unser Lagerhaus Gerätewartung Rasenmäher 1222098

1 000001352036 4377015061111239 ( 1 UMS) AT413742000000036459 / RVVGAT2B420 Sigron Handels & Schulungsz GmbH Reinigungsbedarf 1352036
2.) Sigron Handels & Schulungsz GmbH Reinigungsbedarf 1352036

1.) Bankomat 02505124 K1 02.07 UM 07:52
2.) Bankomat

1.) DT-Umsatz mit Beleg / REF:120001504212151113264275553
2.) Umsatz mit Beleg

1.) Allianz Elementar Versicherung Auftraggeberreferenz:
2.) Allianz Elementar Versicherung

1.) ANDREAS WEINBAUER 5133 St. Something
2.) ANDREAS WEINBAUER

1.) BLAINKENBURG, HANS REF: 0010115012207510N7B00000B6M6
2.) BLAINKENBURG, HANS

1.) A1 Telekom Austria AG Lassallestrasse 9 A-1020 Wien Creditor-ID: AT57xxxxxx
2.) A1 Telekom Austria AG
2a.) A1 Telekom Austria AG Lassallestrasse 9 A-1020 Wien

in the last example I would not mind if the "Adress is also included.

Because there are different "Supplier names and Adresses"

 

[CJ_London]

are these all the options for what you want returned? It looks like a similar scenario I have with with my accounting apps. To handle this I simply have a table containing the required (in this case) supplier names

then using a simple query such as

SELECT [Enter SupName], trandetail FROM tblTransactions where trandetail like "*" & [Enter SupName] & "*"

or to identify transactions where you do not have a supplier identified (this uses a non standard join so can only be done in the sql window)

SELECT TranDetail
FROM tblTransactions LEFT JOIN tblSuppliers on tblTransactions.TranDetail like "*" & tblSuppliers.SupName & "*"
WHERE tblSuppliers.SupName is Null

in the case of
1.) A1 Telekom Austria AG Lassallestrasse 9 A-1020 Wien Creditor-ID: AT57xxxxxx
2.) A1 Telekom Austria AG
2a.) A1 Telekom Austria AG Lassallestrasse 9 A-1020 Wien

you would store 2a in the supplier table. If you store 2 as well you will get duplicates. But that can be handled as well if required

 

[silentwolf]

Hi CJ,
well at the moment there are not more but I do have another account where I need to check but it should be pretty much the same.

I was going down the path with having a table and compare it with a Like Operator...

My thought where like: Importing the Data "Original CSV-File" into a table, get rid of unecessary data and string and split Data into different fields.

Once that is done compare "newly importet Data" with the Table Created before. and Update those fields.

So pretty much like you got it if I am not mistaken..

But I thought it would be nice to have the system working out newly data not stored in the "table" and update it "automatically" or at least put it into the table where I can few new records wich need to be updated for next use.

 

[CJ_London]

My thought where like: Importing the Data "Original CSV-File" into a table, get rid of unecessary data and string and split Data into different fields.

if you use sql to connect to the text file (or use transfertext to link to the file) you can do all that in a single query (or one per table you are appending to) using a few functions such has the one we have been discussing and joining to other tables as lookups (such as joining on the supplier name to get the supplier PK)

it would be nice to have the system working out newly data not stored in the "table" and update it "automatically"

For this sort of thing it is rarely viable unless the table contains just the one field and even then, perhaps not reliable.

My method uses a subform in the header of a main form based on the left join query in post #14 to display the not found records whilst the main form (continuous) displays the supplier records (usually in data entry mode) so simply copy/paste the relevant name from the subform to the main form and complete the other fields. Another subform displays any duplicates which have to be avoided. I get around 10 new entries a month and it takes me less than a minute to enter them all.

In your case since these are transfers to suppliers, presumably the supplier already exists in your system in order to make the payment - so I would expect the 'supName' you are trying to extract would be a field in that table - and you might be able to use some of the fields in that table to identify what you need as a 'supName'

this is what my form looks like

 

[silentwolf]

Pretty need CJ

Some very good points there I will look into it and see if I can put it all in place in my system.

Will let you know how I am going!

Cheers !

 

[silentwolf]

So in my Case I receive BankStatements with CSV as discussed.


This Field is up to 600 chars so pretty big, therefore I split some data out of that field into diffent fields.
That is all well and good.

After that update I am left with Data discussed above.

My FieldName at that point is "UMS" and the Table is "tmpAuszug" It is called tmp as it is a Temporary Table where I import the Data via
Docmd.TransferText

Private Sub ImportCSVToTmpAuszug(fileName As String)
    DoCmd.TransferText acImportDelim, "Volksbank_CSVImport", "tmpAuszug", fileName, True, , 1252
End Sub

I have one Table called BusinessContacts here I have all my Company Contacts stored Employees, Customers, and Suppliers.

The Field DisplayName is a calculated Field returning either a CompanyName or FirstName & LastName depanding on if the
Company Field is filled with Data.

Now I like to Update the "UMS" Field in tmpAuszug with the DisplayName of the BusinessContacts Field.

Can I use a Calculated Field to do so?

Is it bad practice how I got it a the moment? Should I do it differently and if not what woud that work in CJ's approach?

I tried Cj's like this Query

SELECT tmpAuszug.UMS
FROM tmpAuszug, BusinessContacts
WHERE (((BusinessContacts.DisplayName) Is Null));

No Data is displayes

if I try it like

SELECT tmpAuszug.UMS
FROM tmpAuszug, BusinessContacts
WHERE (((BusinessContacts.Company) Is Null));

I get 48000 raws of Data :-(

Maybe someone can spot the issue and give me a hand with this.

When I Update that field like

Public Sub UpdateTmpAuszugWithBusinessContactsDisplayName()
    Dim strSQL As String

strSQL = "UPDATE tmpAUSZUG, qryDisplayName SET tmpAUSZUG.UMS = [qryDisplayName].[DisplayName] " & _
"WHERE (((tmpAUSZUG.UMS) Like ""*"" & [qryDisplayName].[DisplayName] & ""*""));"

    CurrentDb.Execute strSQL, dbFailOnError
End Sub

it works but the Name needs to be very close

e.G. if I got like Insurance Companies there name in the UMS would be something like
UNIQA Österreich Versicherungs-Aktiengesellschaft

so a pretty long name and I like to have it alot shorter somthing like UNIQA Versicherung

So if I have my CompanyName "UNIQA Versicherung" It will not change
I need to have the Name like UNIQA Österreich Versicherungs at least in order that the system reconize that name and change it

Cheers

 

[CJ_London]

I tried Cj's like this Query


SELECT tmpAuszug.UMS
FROM tmpAuszug, BusinessContacts
WHERE (((BusinessContacts.DisplayName) Is Null));

That is not my query. you are missing the non standard left join

SELECT TranDetail
FROM tblTransactions LEFT JOIN tblSuppliers on tblTransactions.TranDetail like "*" & tblSuppliers.SupName & "*"
WHERE tblSuppliers.SupName is Null