[djs425]

I have a column in a table that has lastname, firstname and I want to output it as first name last name. Can anyone help with this? Thanks

[P4L]

What you can do is when you write your report, or write a query, list the first name before the last name. Or you can do something like this

Select ([Table]![FIRST_NME]) & ", " & ([Table]![LAST_NME]) as Name
From Table
Where x=y

To my knowledge, the only other way to do what you want is to have the column names in the exact order you want them.

[eclipsed4utoo]

P4L, on 1 Dec, 2008 - 10:14 AM, said:

What you can do is when you write your report, or write a query, list the first name before the last name. Or you can do something like this

Select ([Table]![FIRST_NME]) & ", " & ([Table]![LAST_NME]) as Name
From Table
Where x=y

To my knowledge, the only other way to do what you want is to have the column names in the exact order you want them.

from my understanding, the firstname and lastname are in the same column. so one column would have "Doe, John" as it's value.

so he would need to do this:

-- if no space after the comma
SELECT SUBSTRING(column_Name, CHARINDEX(',', column_Name) + 1, LEN(column_Name) - CHARINDEX(',', column_Name) + 1)
		+ ' ' + SUBSTRING(column_Name, 1, CHARINDEX(',', column_Name) - 1)
FROM TableName 


-- if there is a space after the comma
SELECT SUBSTRING(column_Name, CHARINDEX(',', column_Name) + 2, LEN(column_Name) - CHARINDEX(',', column_Name) + 1)
		+ ' ' + SUBSTRING(column_Name, 1, CHARINDEX(',', column_Name) - 1)
FROM TableName

This post has been edited by eclipsed4utoo: 01 December 2008 - 12:04 PM

[AdaHacker]

By the way, you didn't mention what database you're using. The string manipulation functions and operators available to you can and will vary with the product you're using. In particular, the use of & and + for string concatenation in the above posts are not in the ANSI standard and are not supported by all database servers (for example, PostgreSQL and MySQL will choke on those). So just be aware that you may need to adjust these solutions to your environment.

[eclipsed4utoo]

AdaHacker, on 1 Dec, 2008 - 10:53 PM, said:

By the way, you didn't mention what database you're using. The string manipulation functions and operators available to you can and will vary with the product you're using. In particular, the use of & and + for string concatenation in the above posts are not in the ANSI standard and are not supported by all database servers (for example, PostgreSQL and MySQL will choke on those). So just be aware that you may need to adjust these solutions to your environment.

agreed. My example works with SQL Server.

[xerxes333]

You should store the first name and last name values in separate fields.