left function with param after comma

[EBee]

I have name field with 150,000 records and due to some data enrty error i need to compare names for '00 vs. '01. I need some help with the left function.

Dominguez, Sandra L.
Smith, Linda

I need to have a end result of :

Dominguez, S
Smith, L

can I use

Left([CUSTOMER],"," 1)

thanks . . need help with syntax

erwin

 

[JohnYingling]

Dim strLast As String
Dim strInit As String
Dim aryName() As String
aryName = Split(strName,",&quot
Redim Preserve aryName(1) ' In case no first name
strLast = aryName(0)
strInit = Left$(aryName(1),1)

http://www.VBResizer.com

Generate Forms/Controls Resizing/Tabbing Class

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[EBee]

I am getting error when i Applied it as a query function

Function splitNow()

Dim strLast As String
Dim strInit As String
Dim aryName() As String

aryName = split([CUSTOMER_N], ",&quot
ReDim Preserve aryName(1) ' In case no first name

strLast = aryName(0)
strInit = Left$(aryName(1), 1)

End Function


------------
Query: splitNow([CUSTOMER_N])

ERROR: "Wrong number of arguments used with function in query expression.

what 'em i doing wrong??

 

[JohnYingling]

What happens wth this?

Dim strLast As String
Dim strInit As String
SplitNow("Linda, Sandra", strLast, strFirst)
Debug.Print "Last=" & strLast & " Init=" & strInit
.....
Sub splitNow(strName As String, strLast As String, strFirstInit As String)

Dim aryName() As String

aryName = split(strName, ",&quot
ReDim Preserve aryName(1) ' In case no first name

strLast = aryName(0)
strInit = Left$(aryName(1), 1)

End Sub

http://www.VBResizer.com

Generate Forms/Controls Resizing/Tabbing Class

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[Craig0201]

Assuming all of the text is formatted the same.....

Left([YourField],InStr(1,[YourField], ",", 1) + 2)

For example.....

Dominguez, Sandra L.

InStr(1,Dominguez, Sandra L., ",", 1) = 10
InStr(1,Dominguez, Sandra L., ",", 1) + 2 =12
Left(Dominguez, Sandra L., 12) = Dominguez, S

Hope that helps.....

Craig