Auto insert in Access database

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Mayzio

Thread Starter
Joined
Nov 15, 2002
Messages
20
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
 
Joined
Oct 13, 2000
Messages
941
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

Thread Starter
Joined
Nov 15, 2002
Messages
20
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.
 
Joined
Jan 27, 2003
Messages
30
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top