Auto insert in Access database

[Mayzio]

I need a helping hand :I'm using access 2000 to manage a database I've created. When I type a name into the 'name' field (in form view) I want my database to insert a matching salutation automatically into the 'salutation' field - example

Mr J S Smith Mr Smith
Professor John Jones Professor Jones
Miss C Doe Miss Doe

I also want the option to amend this manually when a suffix is involved - eg

Dr J S Smith MD Dr MD corrected to Dr Smith
Mr Peter Jones QC Mr QC corrected to Mr Jones

I have no VBA or SQL skills so using the expression builder in access is not always straight forward for me. Can anyone write the necessary commands or show me how to do this? help

 

[downwitchyobadself]

If you want to store separate salutation, name, and potential suffix, you should have at least 3--and probably 4--fields. You're trying to put apples and oranges into the same field, and then have Access sort it out for you. This is a violation of the most basic normalization rules, and is most easily dealt with by resolving your field structure.

Otherwise, you're looking at fancy programming, which is not meant to be used to compensate for poor table design.

 

[Mayzio]

It appears I've been misunderstood. My objective is simply :-
To convert one text format into another where the program will simply select the first word
and last word in a name field and insert it into a salutation field.

Example:
aa bb ccccc - becomes aa ccccc
Mr J Doe - becomes Mr Doe


To describe this objective us 'fancy programming' defies belief. I rather suspect that competent
and experienced programmers will crack this in seconds.

 

[daizee97]

Your best bet is to create 3 fields: Prefix, First and Last.

Then, your "call 'em" field (where you want it to say Mr. Doe or Dr. Doe), just put the default value as [Prefix] & " " & [Last].

So for Mrs. Jane Smith you get Mrs. Smith
Or for Dr. James Foster you get Dr. Foster

But you can't ask Access to take the first word and last word from one field and add them together.

Simple, effective.