Combining data from three fields on same tab/page in2 single

  • Thread starter Thread starter IamaTEMP
  • Start date Start date
I

IamaTEMP

Problem I have a typical database with entries to the name field
(Last, First. Middle, Suffix) and I need to automatically updat
another field, Name, with the information How can I do this? I’v
tried the Event Builder using =[Last] & ", " & [First] &
" & [Middle] &", " & [Suffix] and the Name fields remain
empty

For example

Last First Middle Suffix
Nam
Smith John J. Jr. Smith, John J., Jr
Jones-Brown Merrye Carol MD Jones-Brown, Merrye Carol, M
Marion Martin Marion, Marti

Thanks,

Jerem
 
Hi Jeremy,

If you've got separate fields for First, Last, etc. you never normally
need to combine them into a separate field in the table. Instead,
combine them as and when needed by using a calculated field in a query.
Something like this expression should do the job: it takes care of
nonexistent middle names and suffixes.

FullName: First & Nz(" " + Middle, "") & " " & Last & Nz(", " + S, "")

Note that I used "FullName" rather than "Name". It can cause a lot of
confusion if you give your fields and other objects the same names as
common functoins and properties, e.g. Name, Date, Year, Month etc.

On Wed, 29 Jun 2005 19:38:59 -0500,
Problem I have a typical database with entries to the name fields
(Last, First. Middle, Suffix) and I need to automatically update
another field, Name, with the information How can I do this? I’ve
tried the Event Builder using =[Last] & ", " & [First] &"
" & [Middle] &", " & [Suffix] and the Name fields remains
empty.

For example,

Last First Middle Suffix
Name
Smith John J. Jr. Smith, John J., Jr.
Jones-Brown Merrye Carol MD Jones-Brown, Merrye Carol, MD
Marion Martin Marion, Martin

Thanks,

Jeremy
Click to expand...
 
John, thanks for your response. I need to have the full name apprea
on the form if possible. If I can't combine the fields to display o
the form I'll have to theink of something else. It doesn't have t
be a field what anbout a text box, anything

Thanks, Jerem
 
On Wed, 29 Jun 2005 19:38:59 -0500,
Problem I have a typical database with entries to the name fields
(Last, First. Middle, Suffix) and I need to automatically update
another field, Name, with the information How can I do this? I’ve
tried the Event Builder using =[Last] & ", " & [First] &"
" & [Middle] &", " & [Suffix] and the Name fields remains
empty.
Click to expand...

The field Name SHOULD NOT EXIST in your table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]
 
Just put an expression like the one I posted earlier in a textbox on
your form (changing it to use your actual field names). You must also
ensure that the name of the textbox is not the same as the name of any
of the fields. This example is from my test database:

=[FirstName] & Nz(" "+ [MiddleName], "") & " " & [LastName]

On Fri, 01 Jul 2005 03:37:16 -0500,
 
Back
Top