best way to deal with names?

[merlin777]

In my database I will have a table of names (e.g. for customers) and in some places I will be using the whole name and in some cases just the first name (e.g. 'Dear John').

I assume this means the first and last names have to be entered in their own fields. In which case, what's the easiest way to handle them so that every time I enter a name e.g. when creating an order, I only have to enter one item to get the whole name instead of entering both individually?

Put another way, if I have a customer called John Smith, I might want to address them as 'Dear John' or 'Dear Mr. Smith' but when I create their order I want to select 'John Smith' from a list and not each name individually.

Sorry for the basic question - just trying to get my head round access!

 

[stopher]

Take a look at the example in the other thread here.

Look at the form and inparticularly the combo for choosing the therapist. It shows as the full name even though the therapist is stored as first name and surname. So the user just chooses the name they want from the combo. The combo is based on a query so take a look at the query. Note that the combo actually stores the ID when the name is chosen not the actual name.

 

[moke123]

I usually have 5 fields for a name. A prefix or salutation(Mr.,Mrs.,etc) a first, middle and last name, and a suffix(Jr., Sr. III)
You can then use 1 or all of them by concatenating them. It is also often useful to use the law of propegating nulls when doing so.
for instance

(prefix + " ") & (firstname + " ") & (Middlename + " ") & (lastname) & (" " + suffix)

this will preserve the spacing should any of thse fields be null.

 

[merlin777]

I usually have 5 fields for a name. A prefix or salutation(Mr.,Mrs.,etc) a first, middle and last name, and a suffix(Jr., Sr. III)
You can then use 1 or all of them by concatenating them. It is also often useful to use the law of propegating nulls when doing so.
for instance

(prefix + " ") & (firstname + " ") & (Middlename + " ") & (lastname) & (" " + suffix)

this will preserve the spacing should any of thse fields be null.

so if any of the fields are null the result leaves out the whole contents of those brackets?

 

[moke123]

a null + anything is null.
if you had firstname & " " & middlename & " " & lastname and there were no middlename you would have

Tom  Jones. (2 spaces between the name)

so if anything is null between the parenthesis the spaces are left out only for those values.

 

[merlin777]

presumably this isn't logical operations.

Is this where a null basically means 'unknown' so anything + 'unknown' must also be null which for practical purposes is a string with nothing in it?

 

[MarkK]

Any operation performed on a Null yields a Null, except the ampersand '&' concatenation operator, which, yes, yields an empty string. So when moke does...

prefix + " " & firstname + " "

...then if prefix is Null, the...

Null + " "

...expression yields a Null, conditionally removing the trailing space, even though it exists in the overall expression as a literal. This is tidy way to make a sort of "display template" for data that may or may not be present in the source table.

 

[moke123]

heres a pretty good explanation of the law of propagating nulls

http://www.utteraccess.com/wiki/index.php/Nulls_And_Their_Behavior

I think it is a really important thing to know for display purposes. i use it extensively with names. Also with addresses where you may not have a complete address or some records will have apartment numbers or P.O. boxes.

 

[The_Doc_Man]

I always used separate fields for prefix, first name, middle name/initial, last name, and suffix in the table, but then in my query that I actually used, I added one more field that was the concatenation of all of those. Then when I wanted names, I could pick first name or full name or last name.

My expression was something like this:

Trim( Replace( & _
        NZ( [Prefix], "" ) & " " & _
        NZ( [FirstName], "" ) & " " & _
        NZ( [MNI], "" ) & " " _
        NZ( [LastName], "" ) & " " _
        NZ( [Suffix], "" ), _
    "  ", " ", 1, -1 ) )

This took care of leading blanks, trailing blanks, and embedded double-blanks. For clarity, the last four parameters of the Replace function are TWO quoted spaces, ONE quoted space, numeric 1, numeric -1. Yeah, it's ugly, but you type it once and forget it. You COULD also make it a function, which I eventually did. Doing it this way? No nulls to be found.

 

[merlin777]

I always used separate fields for prefix, first name, middle name/initial, last name, and suffix in the table, but then in my query that I actually used, I added one more field that was the concatenation of all of those. Then when I wanted names, I could pick first name or full name or last name.

My expression was something like this:

Trim( Replace( & _
        NZ( [Prefix], "" ) & " " & _
        NZ( [FirstName], "" ) & " " & _
        NZ( [MNI], "" ) & " " _
        NZ( [LastName], "" ) & " " _
        NZ( [Suffix], "" ), _
    "  ", " ", 1, -1 ) )

This took care of leading blanks, trailing blanks, and embedded double-blanks. For clarity, the last four parameters of the Replace function are TWO quoted spaces, ONE quoted space, numeric 1, numeric -1. Yeah, it's ugly, but you type it once and forget it. You COULD also make it a function, which I eventually did. Doing it this way? No nulls to be found.

forgive my ignorance but where does this code go?

 

[The_Doc_Man]

It's a partial expression that could go in a query field (of course, using the expression builder in that case), or you could build a function that took those five fields as input arguments to return the concatenated string. It doesn't go anywhere as-is because it is a fragment to be adapted.

Eventually, I used this in a function with five inputs and one concatenated output, but that might not be what you prefer.