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

Question

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?

possible duplicate of [How to Replace Multiple Characters in Access SQL?](http://stackoverflow.com/questions/748674/how-to-replace-multiple-characters-in-access-sql) — Johnny Bones, Jan 16 '15 at 21:10
@Kazark I could run two queries, but as I have just started using Access I wanted to know if there is any other way :) — AnmaSe, Jan 19 '15 at 09:09

Mark C. · Accepted Answer · 2015-01-20T15:28:20.923

1

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.

edited Jan 20 '15 at 15:28

answered Jan 16 '15 at 21:33

Mark C.

6,332
4
35
71

I left my computer haha can you make the edit? @HansUp – Mark C. Jan 16 '15 at 21:43
@HansUp Did you do anything? All I see is changing `trm` to `Variant`.. Thanks by the way – Mark C. Jan 16 '15 at 22:00
Declaring trm as Variant instead of String avoids "invalid use of Null" error at `trm = fieldToTest` when fieldToTest is Null. – HansUp Jan 16 '15 at 22:03
1

@HansUp Gotcha. Didn't know that. Thanks.. Have a good weekend – Mark C. Jan 16 '15 at 22:06
@Invent-Animate Thanks for your help! I just realized there is also different kind of data in my table which is i.e. "change of home address 59604934" and the amount of numbers changes, any ideas on how to delete these? I want to have only text in it – AnmaSe Jan 19 '15 at 13:07
@AnMaSe You stated very clearly in your question what the data looked like...Update your question, please. – Mark C. Jan 19 '15 at 13:25
@Invent-Animate I know, I haven't realized I had another type of data there as well. I did update my question, I'm new here so thanks for the advice, I won't do it again. – AnmaSe Jan 20 '15 at 14:47
@AnMaSe okay - try now. – Mark C. Jan 20 '15 at 15:29
@AnmaSe Thanks - I don't normally say this - but you can click the check mark on the left side of my answer to notify that the question has been answered and the answer was the solution to your problem. – Mark C. Jan 21 '15 at 13:20

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

1 Answers1

Table

Output :

SQL :