Stripping Characters from a Field when building a Query

[DrMack]

I am trying to build a query out of two tables from two different databases. Each has a common reference id. My only problem is that the contents of the two fields, are different.

One of them contains the following format:
last_name, first_name first_initial.

The other is as follows:
last_name first_name first_initial

Each table is similar to the other except that one does not have a comma between the last_name and first_name fields and no period after the first_initial field. There is also an occassional comment that is surrounded by () and names that are hyphenated on one database but not on the other.

What I want to do is to build a query that will extract (strip out) any special characters, such as periods, commas, left or right parenthesis, and hyphens from both fields. Essentially, I want to compare only the raw text information and thus, display only those records that are truly different.

Does anyone know of an easy way to do this?

I am very junior when it comes to MS Access but am trying to learn...

Thanks in advance for the help!!

 

[MichaelRed]

You need a procedure (FUNCTION) which will parse the field and return only the "a-z' characters. Using the function, you could procede in a few ways:

Create a new table from the old ones, appending the records from both to the new using the Function to return only the 'a-z' characters from hte field. Then do a duplicates query on the new table.

Create new fields in queries. These would be the old "name" fields passed through the function. Do a join on the two new queries on the 'NewName' field, to show the duplicates (or the NOT duplicates) depending on your real needs and how the join is set up.

Create a new table from the old ones, appending the records from both to the new using the Function to return only the 'a-z' characters from hte field. In the new table, make the name field an indexed field with duplicates not allowed. When you do theh append of the SECOND table (and, perhaps, even the first) duplicate records will automatically be excluded.

Other possabilities abound.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[THoey]

This is a function I wrote for someone to remove extra spaces from a text field (ie., "THIS STRING" becomes "THIS STRING&quot. You should be able to modify this to loop through and remove any of the characters that you want to:

Public Function RemoveSpaces()
    Dim db As Database
    Dim rst As Recordset
    Dim stringin As String
    Dim pos As Integer
        
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * from RemoveSpaces")
    
    rst.MoveFirst
   
    Do While Not rst.EOF
    
        rst.Edit
        stringin = rst!string1
        
        Do While InStr(1, stringin, "  ")
            pos = InStr(1, stringin, "  ")
            stringin = Left(stringin, pos - 1) & " " & Mid(stringin, pos + 2)
        Loop
        
        rst!string2 = stringin
        rst.Update
        rst.MoveNext
       
    Loop
    
End Function

Hope this is a start for you. Let us know if you need any more help. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[DrMack]

Thanks for the input and sorry for the delay in responding. It's been a bit busy on this end...

I have run into a snag and I don't know whether it's something I am doing or not. (Remember, I said I was very new at this Access stuff)

When I attempt to either type in or cut-n-paste the routine submitted by Terry, I get a "Compile error: User-defined type not defined".

I am running Access 2000 (9.0.4402 SR-1) and am unable to find "database" in the pick list that comes up when you enter the "dim db as ..." statement. It will take the manual entry but then gives me the above error. As a bit of further information, I am clicking on the "Modules" selection in the database screen and then clicking on "New". From there I enter in the code you sent.

Am I missing something or do I have a crippled/incomplete MS Access installation?

Any additional help/pointers would be greatly appreciated!!

 

[THoey]

That is one of those DAO/ADO, "We are MS Access and we can change our thinking in midstream" errors. I stick with Access 97, so I don't know the answer to it, but if you do a keyword search for ADO, DAO, or Access 2000, you'll probably find it... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[MichaelRed]

Dim db As DAO.Database
Dim rst As DAO.Recordset


If you go the ADO route, you need to change the edite to ADO., but you also need to instantiate the db/rst a klittle differently
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[THoey]

Thanks MichaelRed. I know I have seen that problem here a handfull of times, but since I don't use A2K, I have never remembered it. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.