Remove periods and commas from Last Name Field 1

[jwkolker]

Hello:

I have a requirement to pass the FirstName, LastName, Middle Name, Suffix and Degree fields without periods or commas to a spreadsheet...

I want to run a query to parse out the periods and the commas - here is an example of the data I need to cleanup

Norman H. Liu  Jr.    M.D.
First  MI Last Suffix Degree

I need to pass to the spreadsheet

Norman H Liu  Jr    MD
First  MI Last Suffix Degree

Any recommendations on an update query that will do the trick to the output table that is created from the master called "Candidates" (we can not take the periods and commas out of the the master table) just out of the one I create to output to excel - let me hear from ya.

Thanx



John Kolker
Programmer
jwkolker@comcast.net

 

[SgtJarrow]

see thread181-727777

my answer there will work for you...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[dhookom]

The thread referenced above suggests Replace() isn't available in Access. It is available in most versions of Access however, you can't reference it directly in a query in versions of Access 2000 without the latest service pack updates (there was a bug).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SgtJarrow]

dhookom...

you will notice I did not reference the Replace() function....and I indicated in this thread that MY answer in the other thread will work....

I have never been too keen on the replace function, as I "grew up" in Access in the pre-Replace() days and had to do it the hard way....still hard to change my thinking....I just use my referenced function for all my deletion needs....

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[dhookom]

I also still "roll my own" rather than use conditional formatting and lots of other new-fangled features. I just wanted to make it clear that Replace() is supported in Access.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jwkolker]

Hi all - this is really beating me up - I have many tables have values within fields that have dashes or commas or periods that do not belong - let's use an example - In the table Candidates I have telephone numbers with xxx-xxx-xxxx in field Phone and I want to run a query or create a module to remove the "-"s how do I do it?

Please include full sql for a query or full vba for a module - make this table first to make it easier

Table Name = Candidates
Phone = text - put in a bunch of xxx-xxx-xxxx records and a mistake or two xx-xxx-x-xxxx for example - run your vba or query and update the table or make a table with all xxxxxxxxxx values...

I hope someone can help me figure this one out.

Regards,


John Kolker
Programmer
jwkolker@comcast.net

 

[dhookom]

This function can be copied into a new, blank module and saved with the name "basStringStuff".
[blue]

Function GarbageIn(pvarText As Variant) As Variant
    Dim intChars As Integer
    Dim varGarbageOut As Variant
    Dim strJunkChars As String
    strJunkChars = &quot;!@#$%.,<>?/\|][{};:^&*()_-+='~`&quot;
    Dim intChar As Integer
    If IsNull(pvarText) Then
        varGarbageOut = Null
     Else
        intChars = Len(pvarText)
        For intChar = 1 To intChars
            If InStr(strJunkChars, Mid(pvarText, intChar, 1)) = 0 Then
                varGarbageOut = varGarbageOut & Mid(pvarText, intChar, 1)
            End If
        Next
    End If
    GarbageIn = varGarbageOut
End Function

[/blue]
Then, create an update query with SQL like:

UPDATE Candidates
SET Phone = GarbageIn(Phone);


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jwkolker]

Hey Duane - thank you very much - this is what I ended up using and it worked like a charm - take a look -

Function GarbageInExtra(pvarText As Variant) As Variant

'gets rid of everything except numerals

    Dim intChars As Integer
    Dim varGarbageOut As Variant
    Dim strJunkChars As String
    strJunkChars = &quot; !@#$%.,<>?/\|][{};:^&*()_-+='~`abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ&quot;
    Dim intChar As Integer
    If IsNull(pvarText) Then
        varGarbageOut = Null
     Else
        intChars = Len(pvarText)
        For intChar = 1 To intChars
            If InStr(strJunkChars, Mid(pvarText, intChar, 1)) = 0 Then
                varGarbageOut = varGarbageOut & Mid(pvarText, intChar, 1)
            End If
        Next
    End If
    GarbageInExtra = varGarbageOut
End Function


Function GarbageIn(pvarText As Variant) As Variant

    'Gets rid of special characters
    
    Dim intChars As Integer
    Dim varGarbageOut As Variant
    Dim strJunkChars As String
    strJunkChars = &quot;!@#$%.,<>?/\|][{};:^&*()_-+='~`&quot;
    Dim intChar As Integer
    If IsNull(pvarText) Then
        varGarbageOut = Null
     Else
        intChars = Len(pvarText)
        For intChar = 1 To intChars
            If InStr(strJunkChars, Mid(pvarText, intChar, 1)) = 0 Then
                varGarbageOut = varGarbageOut & Mid(pvarText, intChar, 1)
            End If
        Next
    End If
    GarbageIn = varGarbageOut
End Function

Here is the SQL I used to clean up the CREW table...

UPDATE [tbl-CrewRegOutPut] SET [tbl-CrewRegOutPut].Phone = GarbageInExtra([Phone]), [tbl-CrewRegOutPut].LastName = GarbageIn([LastName]), [tbl-CrewRegOutPut].FirstName = GarbageIn([FirstName]), [tbl-CrewRegOutPut].MiddleInitial = GarbageIn([MiddleInitial]);

Thanks again - for the great post - works like a charm and has multiple applications!

John Kolker
Programmer
jwkolker@comcast.net