Need help on making an expression (Simple)

[Leopardfist]

OK, I have 3 fields in my table: First Name (FNAME), Middle Name (MNAME), and Last Name (LNAME). The table is named EMPLOYEES and I need to make an expression that will give me an employees full name (LNAME, FNAME MNAME) in that structure.

Now I created a new Query which included the three names, but I cannot figure out how to do the expression for the full name, it keeps giving me errors.

I tried using the builder and doing basically:

[LNAME] & ", " & [FNAME] & " " & [MNAME]

That gave me an error saying it contained incorrect expressions or something. I also substituted + for &, and "AND" for &, and still got those errors.

I only have one report where the name format needs to be like this, and thought I could use a simple query to collect the data and format the name. If anyone can help me it would be greatly appreciated.

Thank You!

 

[Keith Nichols]

Expr1: [FName] & " " & ([MName]+" ") & [LName]

The bracketed middle part of the expression will not produce anything if there is no middle name and so avoid having 2 spaces.

See attached example db.

Hope this helps

Just reread your post and realized I missed the order you wanted, (LNAME, FNAME MNAME). Try this.

LastFirstMiddle: [LName] & ", " & [FName] & (" "+[MName])

Again, the (" "+ does not propagate if there is no middle name and so there is no redundant space after the result. It wouldn't necesarily be a problem in this order in which case you can use:

LastFirstMiddle: [LName] & ", " & [FName] & " " & [MName], which is pretty much where you started so I don't know what you were doing wrong. This should work if you paste it directly into a column of the query grid.

Sample DB updated to match.

Cheers.

 

[Leopardfist]

AWESOME! Thank you a bunch!

 

[Keith Nichols]

No worries LeapardFist - glad to give back a little for all the help I have had from thsi forum

From my sample can you see what you were doing wrong as it had to be something simple like a spelling mistake.

Regards,