Change format of name?

[option]

Hey guys,

Quick question: I have a table (tbl1) that has names stored in the format "last, first mi" and a linked table (tbl2) that has other information I need, but the names are "first mi last" format. To have my query work, since it'll be based on name, I need to format tbl1 to match tbl2 in the name field. I'd do it manually, but there are over 1500 records for me to do. Is there some sort of code that could adjust my tables name format? Thanks!

 

[SimonB1978]

Hi,

This function will do the formatting:

Public Function FormatName(strName) As String
Dim intPos As Integer
Dim strLeftPart As String
Dim strRightpart As String

intPos = InStr(strName, ",")

strLeftPart = Left(strName, intPos - 1)
strRightpart = Right(strName, Len(strName) - intPos - 1)

FormatName = strRightpart & " " & strLeftPart

End Function

Now, in you query, instead of tbl1.name = tbl2.name use FormatName(tbl1.name) = tbl2.name

You could also use this function in an update query to modify the format of the name in tbl1 if you ever want to...

Simon B.

 

[boblarson]

And this is a perfect example of why it is better to store the first name, last name, and middle name/initial as separate fields. It is much easier to put them together than it is to pull them apart. I know you don't always have the option, especially if your data is coming from another system/file, but it is best if possible to work with atomic fields.