1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Auto insert in Access database

Discussion in 'Business Applications' started by Mayzio, Jan 31, 2003.

Thread Status:
Not open for further replies.
  1. Mayzio

    Mayzio Thread Starter

    Nov 15, 2002
    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
  2. downwitchyobadself


    Oct 13, 2000
    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.
  3. Mayzio

    Mayzio Thread Starter

    Nov 15, 2002
    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.

    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.
  4. daizee97


    Jan 27, 2003
    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.
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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/116234

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice