CODEWITHSUNDEEP
stringms-access
AnmaSe
AnmaSe

Reputation: 13

MS Access: how to delete everything in a string after two specific characters

How can I delete the number together with the space in a string while my data looks like this:

randomtext = 567897
otherrandomtext = 3827475
evendifferentone : 483838
andsoon : 03948
type of contact 594837
other type of contact 453222223

so that it can look like this:

randomtext
otherrandomtext
evendifferentone
andsoon
type of contact
other type of contact

I managed to use update query (written below) to remove everything after "=" but how to update both (with "=" and ":") at the same time?

UPDATE [MyTable] 
SET [Name] = Left([Name], InStr([Name], "=") - 1)
WHERE [Name] Like "*=*"

I can do it in two separate queries while it is only data with "=" or ":", but I don't know how to handle it when I have also data like "type of contact 594837". Maybe there is a way to just delete everything that is a number, = and : sign?

Upvotes: 1

Views: 3904

Answers (1)

Mark C.
Mark C.

Reputation: 6450

Fairly simply.. You can write a VBA function that you can actually embed in your SQL queries to do this. I went to Database Tools (Top of MS Access), Visual Basic, Right Click on the Modules Folder, Insert -> Module. After you write/copy the code, go to Debug at the top of the Visual Basic for Applications IDE and click "Compile YourDatabaseName.

See below:

Option Compare Database
Option Explicit

Public Function TrimString(fieldToTest As Variant) As Variant

Dim test, test2 As Variant
Dim trm As Variant
Dim intSearch, lngth As Integer

TrimString = Null

test = InStr(fieldToTest, "=")
test2 = InStr(fieldToTest, ":")
lngth = Len(fieldToTest)


If test > 0 Then
    'We know it contains an equals sign

    trm = Left(fieldToTest, test - 1)
    trm = Trim(trm)
    Debug.Print trm

ElseIf test2 > 0 Then
    'We know it contains a colon
    trm = Left(fieldToTest, test2 - 1)
    trm = Trim(trm)
    Debug.Print trm

ElseIf lngth > 0 Then
    'Find out if it has integers in it
    'Rebuild the string without Integers
    For intSearch = 1 To lngth
         If Not IsNumeric(Mid$(fieldToTest, intSearch, 1)) Then
          trm = trm & Mid$(fieldToTest, intSearch, 1)
        Else
        End If
    Next

    trm = Trim(trm)
Else
    'Regular String
    'Do Nothing
    trm = fieldToTest
End If

TrimString = trm

End Function

There's not very much error handling, but I think this answers your question.

I threw it in a Table, field data type is Text:

Table

ID  stringTest (Field Name)

 1. randomtext = 123453
 2. otherrandmtext = 543555
 3. evendifferentone : 453553
 4. andsoon : 05453534

Output :

 ID Expr1

 1. randomtext
 2. otherrandmtext
 3. evendifferentone
 4. andsoon

SQL :

SELECT Table2.ID, 
TrimString([stringTest]) AS Expr1
FROM Table2;

Recall from the VBA code that TrimString is the function name.

If there is anything I overlooked - please let me know and I will do my best to correct it.

Upvotes: 1

Related Questions