Calculated Name Field Help

[immortalthor]

So I've got a table for musical artists names and I'm trying to get it to format how I'm wanting it. I've got fields for [Alias], [First] and [Last], and then a calculated field called [Name]. I've got it formatted as [Alias]+" ("+[First]+" "+[Last]+")", so that when it turned out like

Madonna (Madonna Ciccone)
Ice T (Tracy Marrow)
John Lennon
etc.



The problem I'm having is that it's requiring all three to be filled in, otherwise the [Name] filled remains blank. How do I get it to return a name regardless of what's filled in and that if it's a normal name with no alias, there's no paranthesis?

[Welshgasman]

Use & and not +

[Micron]

Calculated in a query? Perhaps
Iff(IsNull([Alias]),[First] & " " & [Last],[Alias] & " (" & [First] & " " & [Last] & ")")

You would do well to research concatenation (which is what that is) and the difference between that and plus (+).

"dog" + "cat" = dogcat

"dog" & "cat" = dogcat

"DOG" & NULL = DOG

"DOG"+NULL is Null

N.B. every field name you're using is a reserved word
ReservedWords

[immortalthor]

I've tried different combinations, but it's still not coming out right. Essentially, I'm trying to make the open and close paranthesis conditional to when [First] and [Last] are filled in but NOT [Alias], or when [Alias] is filled in but NOT [First] and [Last]. I've pasted what I'm trying to get it to look like. I've created a field to show what the end result I'm wanting it to loo like.

Desired Result	Name	Alias	First	Last
John Lennon	(John Lennon)		John	Lennon
Bannana Test	Bannana Test ( )	Bannana Test
Carrot Test (Last)	Carrot Test ( Last)	Carrot Test		Last
Ice T (Tracy Marrow)	Ice T (Tracy Marrow)	Ice T	Tracy	Marrow
Madonna (Madonna Ciccone)	Madonna (Madonna Ciccone)	Madonna	Madonna	Ciccone
Pickle Test (First)	Pickle Test (First )	Pickle Test	First

[Welshgasman]

You can use IIF() function for the alias check, but what if first name is not completed, but last name is?
Like Prince, Meatloaf, Flash?, are they all Aliases.
For anything too complicated, I would write a function to return the required string.
Pay attention to Micron's comments on reserved names and change them to something different like KA, Known as, or SA Stage Name etc.

[immortalthor]

It's not 100% perfect, but extremely close. Thank you all!!

[Micron]

The possibilities are a bit too numerous for the Iff function IMO. Plus, you might encounter other situations in the future so I'd suggest writing a function to handle what's known now and have the query call it. If you use a Select Case block it should be fairly easy to add conditions if they arise later.

[Micron]

code that query calls and the results. It looks exact to me:

Function getNames(Alias, FName, LName) As String

Select Case True
    Case IsNull(Alias) And Not IsNull(FName) And Not IsNull(LName) 'alias is blank, 1st and last are not
        getNames = FName & " " & LName
        
    Case Not IsNull(Alias) And IsNull(FName) And IsNull(LName) 'alias is not blank, 1st and last are blank
        getNames = Alias
    
    Case Not IsNull(Alias) And IsNull(FName) And Not IsNull(LName) 'alias is not blank, 1st is, last is not
        getNames = Alias & " (" & LName & ")"
    
    Case Not IsNull(Alias) And Not IsNull(FName) And IsNull(LName) 'alias is not blank, 1st is not, last is blank
        getNames = Alias & " (" & FName & ")"
        
    Case Not (IsNull(Alias) Or IsNull(FName) Or IsNull(LName)) 'none are blank
        getNames = Alias & " (" & FName & " " & LName & ")"
        
End Select

End Function

Alias	FirstName	LastName	Expr1
	John	Lennon	John Lennon
Bannana Test			Bannana Test
Carrot Test		Last	Carrot Test (Last)
Ice T	Tracy	Marrow	Ice T (Tracy Marrow)
Madonna	Madonna	Ciccone	Madonna (Madonna Ciccone)
Pickle Test	First		Pickle Test (First)

The last case where I grouped each with OR is an experiment I tried rather than dragging out the Not's. Seems to work.
@immortalthor - if you try it, you must make sure the function parameters match your query field names. FName etc. will not work if you used First (bad).

[ArviLaanemets]

On fly, this expression must work for all possible variants, even for cases when some name part is not empty, but contains leading/trailing spaces, or contains only spaces.

LTrim(RTrim((Nz(Alias,""))) & Iif(OR(LTrim(RTrim((Nz(Alias,""))), LTrim(RTrim(Nz(FName,"") & " " & Nz(LName)))=""),""," ") & Iif(AND(LTrim(RTrim(Nz(FName,"") & " " & Nz(LName)))<>"",LTrim(RTrim((Nz(Alias,"")))<>""),"(","") & LTrim(RTrim(Nz(FName,"") & " " & Nz(LName))) & Iif(AND(LTrim(RTrim(Nz(FName,"") & " " & Nz(LName)))<>"",LTrim(RTrim((Nz(Alias,"")))<>""),")","")