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
jmoneilthe2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
The length in the Mid function is not optional.  In other words it should be:
Mid(Data, Offset, Length)
0
jmoneilthe2Author Commented:
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)
0
Anthony PerkinsCommented:
Please see here:
I answered my question myself. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ala_frostyCommented:
In SQL, you need to use 'substring' rather than 'mid'. It doesn't look like you're using any SQL here though, so maybe this has nothing to do with it...

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>It doesn't look like you're using any SQL here though<<
Correct.  The questioner is using VBScript in a DTS package.
0
muzzy2003Commented:
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, " ")
0
muzzy2003Commented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.