asked on

MID fuction difference form VB to VBScript

Below are 4 functions. ParseFname, ParseMName, and ParseLName came from an excel spread sheet where I parse out the name from one field that has all three names in it. It works fine In Excel. I have taken this code and put it into a DTS packeage and modified the function Main but I get an error saying my mid function is incorrect or the wrong amount of arguments.
HELP

Function Main()

DTSDestination("FirstName") = ParseFName(DTSSource("NAME"))
DTSDestination("MiddleName") = ParseMName(DTSSource("NAME"))
DTSDestination("LastName") = ParseLName(DTSSource("NAME"))
Main = DTSTransformStat_OK
End Function

Function ParseFName(name)

Dim FullName
Dim LengthName
Dim FName
Dim LName
Dim MName
Dim CommaPos
Dim SpacePos

FullName = name
LengthName = Len(FullName)

'---------------------------------------Last Name-----------------------------

CommaPos = InStr(FullName, ",")
LName = Left(FullName, CommaPos - 1)

'---------------------------------------Middle Name and First Name--------------

SpacePos = InStr(FullName, " ")

If Not SpacePos = 0 Then

MName = Right(FullName, LengthName - SpacePos)

FName = Mid(FullName, CommaPos + 1, LengthName - (CommaPos + (LengthName - SpacePos) + 1))

ParseFName = Trim(FName)

Else

'--------------------------------------First Name no Middle---------------------

FName = Right(FullName, LengthName - CommaPos)

ParseFName = Trim(FName)

End If

End Function

Function ParseLName(name)

Dim FullName
Dim LengthName
Dim FName
Dim LName
Dim MName
Dim CommaPos
Dim SpacePos

FullName = name
LengthName = Len(FullName)

'---------------------------------------Last Name-----------------------------

CommaPos = InStr(FullName, ",")
LName = Left(FullName, CommaPos - 1)

'---------------------------------------Middle Name and First Name--------------

SpacePos = InStr(FullName, " ")

If Not SpacePos = 0 Then

MName = Right(FullName, LengthName - SpacePos)

FName = Mid(FullName, CommaPos + 1, LengthName - (CommaPos + (LengthName - SpacePos) + 1))

ParseLName = Trim(LName)

Else

'--------------------------------------First Name no Middle---------------------

FName = Right(FullName, LengthName - CommaPos)

ParseLName = Trim(LName)

End If

End Function

Function ParseMName(name)

Dim FullName
Dim LengthName
Dim FName
Dim LName
Dim MName
Dim CommaPos
Dim SpacePos

FullName = name
LengthName = Len(FullName)

'---------------------------------------Last Name-----------------------------

CommaPos = InStr(FullName, ",")
LName = Left(FullName, CommaPos - 1)

'---------------------------------------Middle Name and First Name--------------

SpacePos = InStr(FullName, " ")

If Not SpacePos = 0 Then

MName = Right(FullName, LengthName - SpacePos)

FName = Mid(FullName, CommaPos + 1, LengthName - (CommaPos + (LengthName - SpacePos) + 1))

ParseMName = Trim(MName)

Else

'--------------------------------------First Name no Middle---------------------

FName = Right(FullName, LengthName - CommaPos)

ParseMName = Trim(MName)

End If

End Function

Anthony Perkins

The length in the Mid function is not optional. In other words it should be:
Mid(Data, Offset, Length)

jmoneilthe2

ASKER

Yes. I have three arguments in my mid. I just can't seem to see why this works in VB but not in my VBScript (DTS)

Anthony Perkins

Please see here:
I answered my question myself. What do I do?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70

ASKER CERTIFIED SOLUTION

ala_frosty

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

>>It doesn't look like you're using any SQL here though<<
Correct. The questioner is using VBScript in a DTS package.

muzzy2003

If you pass a string containing a space before the comma into your ParseMName function, you get an "Invalid procedure call or argument" error. You need to be a bit more careful with the possible strings that can be passed in. Consider using the optional first parameter with InStr to specify the start of the search, so that:

SpacePos = InStr(FullName, " ")

becomes:

SpacePos = InStr(CommaPos, FullName, " ")

muzzy2003

Sorry, I don't quite understand why you chose the answer you did. I apologise if I misread your question, but I don't think you were asking about SQL, rather VBScript in a DTS package?