How to seperate input from one field into two or more fields?

[dnnll]

I need to seperate a field input into two seperate fields. ex: <smith, john>
to <smith><john>. Access 2007 - thanks for any input

 

[Jeff Boyce]

If you are absolutely, positively certain that you will ONLY and ALWAYS have
"lastname comma space firstname", then you can use the Left() function and
the Mid() function to derive these values.

If you have compound last names (e.g., van De Kamp), suffixes (e.g., Jr.,
Sr.), multi-word first names (e.g., Mary Sue Ellen), or single word names
("Cher"), you will have a more difficult time getting 100% of your names
converted.

You may even have to rely on USB to finish after your conversion routine
gets the majority (using someone's brain, that is).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MGFoster]

I need to seperate a field input into two seperate fields. ex: <smith, john>
to <smith><john>. Access 2007 - thanks for any input

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I use the following routines to separate names. You can call them from
a query like this:

SELECT GetFirstName(name_column) As FirstName,
GetLastName(name_column) As LastName, ... etc. ...
FROM ...
WHERE ...


Function GetFirstName(strName As String) As Variant
' Purpose:
' Get the first name off the indicated string
' In:
' strName The indicated string.
' Format: "LastName,FirstName MiddleName"
' Out:
' Variant The First name. If no first name - a NULL.
' Created:
' mgf 15apr2003
' Modified:
'

On Error Resume Next ' This function can be called from a query

Dim varTemp As Variant

' Get the first name, if any
varTemp = Trim$(Mid$(strName, InStr(1, strName, ",") + 1))

' If there is a middle name - parse it out
If InStr(1, varTemp, " ") > 0 Then
varTemp = Trim$(Left$(varTemp, InStr(1, varTemp, " ") - 1))
End If

If varTemp = "" Then varTemp = Null

GetFirstName = varTemp

End Function

Function GetMiddleName(strName As String) As Variant
' Purpose:
' Get the middle name off the indicated string
' In:
' strName The indicated string.
' Format: "LastName,FirstName MiddleName"
' Out:
' Variant The middle name. If no middle name - a NULL.
' Created:
' mgf 15apr2003
' Modified:
'

On Error Resume Next ' This function can be called from a query

Dim varTemp As Variant

varTemp = Trim$(Mid$(strName, InStr(1, strName, ",") + 1))

If InStr(1, varTemp, " ") > 0 Then
varTemp = Trim$(Mid$(varTemp, InStr(1, varTemp, " ") + 1))
Else
varTemp = Null
End If

If varTemp = "" Then varTemp = Null

GetMiddleName = varTemp

End Function

Function GetLastName(strName As String) As Variant
' Purpose:
' Get the last name off the indicated string
' In:
' strName The indicated string.
' Format: "LastName,FirstName MiddleName"
' Out:
' Variant The last name. If no last name - a Null.
' Created:
' mgf 15apr2003
' Modified:
'

On Error Resume Next ' This function can be called from a query

Dim varTemp As Variant

If InStr(1, strName, ",") > 0 Then
varTemp = Trim$(Left$(strName, InStr(1, strName, ",") - 1))
End If

If varTemp = "" Then varTemp = Null

GetLastName = varTemp

End Function

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBST2pI4echKqOuFEgEQLE5wCgzn515jMFENSNPiKUUdiM+1cZHscAn0gZ
B5XPROEUG5RC40F2EyYtWP+X
=NGPg
-----END PGP SIGNATURE-----