delete initial characters from field

[RichardMc]

Hi,
I am trying to check 1 table against another for unmatched records, using a name field. In Table 1 the name field contains first and last name, e.g. John Smith. In Table 2, some (but not all) of the records also contain a title e.g. Dr John Smith.
Any ideas on how to strip these titles from the beginning of the field?
Thanks, Richard

 

[dcx693]

Maybe someone around here has a pre-made functon just for this. If not, here's how you can proceed.

In the best case, if you know that there will be a finite number of unique titles, search for just those titles and remove them using an update query and the Right() function.

If you have more than a handful of unique titles though, you may want to search for records that contain more than 1 space (and therefore, more than just a first and last name). You need to be careful to look out for "non-typical" name entries like Jo Ann or Sue Ellen as first names. Or someone could put in a middle initial and throw you off.

 

[RichardMc]

There are jsut 3 or 4 titles, so the Right() function might do the trick. Any idea where I can find some examples of the Right() function, as I am not familiar with how to use it?
Thanks!!

 

[dcx693]

Argh! I actually meant the Mid() function. The first place to look is always the Access built-in online help. It has an explanation of every function, including Mid(). But to summarize, use the Mid() function to take a certain number of characters in the middle of the string.

Examples:
Mid("Mr. Joe Smith",5,3) returns "Joe"
Mid("Mr. Joe Smith",5) returns "Joe Smith"
Leaving out the last parameter tells Mid() to take all characters from the starting position of 5 all the way until the end.

 

[RichardMc]

Aha! Thanks!
I think this would work fine if all records started with Dr or Mr, but some start with Prof, and some just start with the first name e.g.
John Snowe
Mr Jay Turner
Dr Antony P Smith
Mary Tyler
Prof Michael Brown
If I understand Mid() correctly it will not work for those records which start with the first name. Or am I missing something?

 

[dcx693]

You are correct. What I could do is search for each different title separately (you said there were a small number) in a query, then remove just that title from those records. Then proceed onto the next title, etc...

 

[RichardMc]

This works. However when I searched for Like 'Dr*' I get 2191 records come up!
(When I said small number I was referring to the various titles (Mr, Dr, Prof) rather than the instance of each.)
So what I then did was to do a Replace (Using Replace on the Edit menu) Dr with _ (blank), at start of field.
This worked great, but left me with a blank character at the start of each field.
Any ideas on how I can get rid of that?
BTW, I REALLY appreciate all tyhe assistance!

 

[dcx693]

Don't replace each instance manually. Use an update query. When you use Like 'Dr*' to find the 2191 instances, you are using a select query. An update query is simply a query that takes values and updates them. In that query, go to the Query menu, and choose Update Query. You'll see a new line in your grid called "Update To:". On that line, use an expression like:
Mid([full_name],4)
Make sure your criteria is still Like 'Dr*'. Run the query. That would get rid of all instances of "Dr" at the beginning of your field.

It's always a good idea to work on a copy of the table if you are making mass changes like these.

 

[RichardMc]

The Update Query worked like a charm!! And yes, I had all the data safely backed up before I started - first lesson I learned!

Thanks again! Richard