How do you Delimited Text?

[smc678]

How would I be able to separarte this text STRANG,JENNIFER N either on a table, query, or on a form.

[June7]

Let x represent the field that contains this data.

Left(x, InStr(x,",")-1) will return STRANG

Left(Mid(x, InStr(x,",")+1),InStr(Mid(x, InStr(x,",")+1)," ")) will return JENNIFER

Right(x,1) will return N

However, if your real data does not follow this pattern for every single record, the expressions will probably fail and a custom function will be needed to accomplish this.

[smc678]

Works Sorta. So in my query I have some names like DOWD,DWAYNE and CARBAJAL,EDER J some have a middle int and some dont. Any Ideas?

[June7]

Breaking the name based on comma is easy, after that gets complicated. Need custom function.

Google: Access VBA split names

Review:
http://www.access-programmers.co.uk/...d.php?t=157134
http://www.access-programmers.co.uk/...ad.php?t=93148

Create function that will parse the last name and initial but only call the function if the space is present which indicates initial also present.

Something like:

Function BreakName(strName As String, strPart As String) As String
If strPart = "Last" Then
BreakName = Left(strName, InStr(strName, " "))
Else
BreakName = Right(strName, 1)
End If

Then call the function in expression:

IIf(InStr(Mid(x, InStr(x,",")+1)," ")>0, BreakName(Mid(x, InStr(x,",")+1),"Last"), Mid(x, InStr(x,",")+1)

IIf(InStr(Mid(x, InStr(x,",")+1)," ")>0, BreakName(Mid(x, InStr(x,",")+1),"Initial"), Null)