Jintonix415
asked on
concatenating 4 fields that are both numerical and text formats
DTSDestination("LM_Key_to_ Subledg") = DTSSource("LM_Company") + "-" + DTSSource("LM_Sub") + "-" + DTSSource("LM_Lease") + "-" + DTSSource("LM_Schedule")
cast DTSSource(“LM_Company”) to 2 characters with leading zeros
cast DTSSource("LM_Sub") to 3 characters with leading zeros
cast DTSSource("LM_Lease") to 7 characters with leading zeros
cast DTSSource("LM_Schedule") to 3 charcaters with leading zeros
I am trying to make the end result look like this below.
01-001-3060005-001
cast DTSSource(“LM_Company”) to 2 characters with leading zeros
cast DTSSource("LM_Sub") to 3 characters with leading zeros
cast DTSSource("LM_Lease") to 7 characters with leading zeros
cast DTSSource("LM_Schedule") to 3 charcaters with leading zeros
I am trying to make the end result look like this below.
01-001-3060005-001
ASKER
Thanks Billprew but I get an error stating a type mismatch. Am I supposed to include lines 3-6? It looks like its just a comment referencing the format.
What's the data type of LM_Key_to_Subledg ?
~bp
~bp
ASKER
Varchar (255)
Yes, lines 3-6 were required, it's a small function used for left padding. Did you have that in place?
If so, try a test like this and see what it displays.
If so, try a test like this and see what it displays.
Wscript.Echo LPad(DTSSource("LM_Company"),2,"0")
Wscript.Echo LPad(DTSSource("LM_Sub"),3,"0")
Wscript.Echo LPad(DTSSource("LM_Lease"),7,"0")
Wscript.Echo LPad(DTSSource("LM_Schedule"),3,"0")
Wscript.Echo LPad(DTSSource("LM_Company"),2,"0") & "-" & LPad(DTSSource("LM_Sub"),3,"0") & "-" & LPad(DTSSource("LM_Lease"),7,"0") & "-" & LPad(DTSSource("LM_Schedule"),3,"0")
DTSDestination("LM_Key_to_Subledg") = LPad(DTSSource("LM_Company"),2,"0") & "-" & LPad(DTSSource("LM_Sub"),3,"0") & "-" & LPad(DTSSource("LM_Lease"),7,"0") & "-" & LPad(DTSSource("LM_Schedule"),3,"0")
' Left pad a string to any length with a specified character
Function LPad( strText, intLen, chrPad )
LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function
~bp
ASKER
I copied exactly the code that you provided below:
DTSDestination("LM_Key_to_ Subledg") = LPad(DTSSource("LM_Company "),2,"0") & "-" & LPad(DTSSource("LM_Sub"),3 ,"0") & "-" & LPad(DTSSource("LM_Lease") ,7,"0") & "-" & LPad(DTSSource("LM_Schedul e"),3,"0")
' Left pad a string to any length with a specified character
Function LPad( strText, intLen, chrPad )
LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function
However I am getting a syntax error:
Error 1002 on Line 10, Column 0:
Function LPad(strText, intLen, chrPad )
Syntax error
DTSDestination("LM_Key_to_
' Left pad a string to any length with a specified character
Function LPad( strText, intLen, chrPad )
LPad = Right( String( intLen, chrPad ) & strText, intLen )
End Function
However I am getting a syntax error:
Error 1002 on Line 10, Column 0:
Function LPad(strText, intLen, chrPad )
Syntax error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for being really late on this. I did attempt this code and it has worked. Thank you!
Welcome, thanks for wrapping this one up.
~bp
~bp
Open in new window
~bp