Combining three fields...how?

[D.M. Wrider]

I have three fields that I need combined into one. The fieds are 'Title', 'First Name' and 'Last Name'.

I want them to be presented in the new field in that order with spaces in between, but if one or more of those fields are blank, I don't want blank spaces to show up. Make sense?

I tried making the calculation with several variables, but I keep getting stuck. I know the answer must be a simple one, but I keep getting two 'Last Names', etc because I can't use "or" in the calculation only "&" - otherwise I get a big ol' "0" in the field instead of text.

Any examples of a calculation?

[aldipalo]

Create a calculation field "FullName" or whatever.

:Title & " " & ::FirstName & " " & ::LastName

HTH

[D.M. Wrider]

Well...I tried that, but then if there is no Title or no First Name, then I get extra blank spaces because of the " "'s in the calculation.

I tried formulating "If's" to take care of that, but I'm running into problems there too.

[D.M. Wrider]

Okay - 'bout three seconds after I posted, I tried it again, and it came out. Don't ask how...I must have just typed something in wrong the first time.

I ended up with:

Title & If(Title>0; " ") & First Name & If(First Name > 0 ; " ") & Last Name

It seems to do the trick.

Thanks for your help!

[aldipalo]

Did you try:

Trim(:Title & " " & ::FirstName & " " & ::LastName)

[D.M. Wrider]

Ah! I'd forgotten about the Trim function - it works just as well, and is a shorter calculation.

Thank you!

Edited March 23, 2007 by Guest

[comment]

Trim() will not work here when the middle field is empty. You need:

TrimAll ( Title & " " & FirstName & " " & LastName ; 0 ; 0 )

P.S. What does this

:Title

mean? Edited March 23, 2007 by Guest

[aldipalo]

I tried Trim() in a test app and it works whether there is data in any or all fields. I put : before a field to indicate that it is a field and not data. I saw this used several times by others, understood it, and kind of adapted it.

[comment]

If the FirstName field is empty, there will be TWO spaces beteen the title and the last name.

IMHO, "::" denotes a RELATED field, in a format of TableOccurence::FieldName. Using this symbol for LOCAL fields confuses me.

[D.M. Wrider]

comment - thanks. I thought I'd tried out every scenario, but I guess not. Now I know how to fix it though.

Glad for the help from both parties. ;)

[aldipalo]

As I said I saw the :Fieldname used by others and kind of adopted it. However, YHO is respected and I won't use it if it is confusing people. Again, no one previously asked what I meant by it and I've used it many, many times.

In my app I do not see the extra space, but, again, that doesn't mean it is not there. I set the fields to flush left and all appears correct. But again, when it comes to the proper calculation to use you are the MASTER. So, I'm glad the questioner changed it. :

[comment]

In my app I do not see the extra space, but, again, that doesn't mean it is not there.

Easy to check: place a cursor in the field and move it with left/right arrows. Or change the calc to:

Substitute ( Trim ( Title & " " & FirstName & " " & LastName ) ; " " ; "#" )

I'd suggest you never take these things as correct just because someone said so.