• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

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
0
Jintonix415
Asked:
Jintonix415
  • 5
  • 4
1 Solution
 
Bill PrewCommented:
Try this approach.

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

Open in new window

~bp
0
 
Jintonix415Author Commented:
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.
0
 
Bill PrewCommented:
What's the data type of LM_Key_to_Subledg ?

~bp
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jintonix415Author Commented:
Varchar (255)
0
 
Bill PrewCommented:
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.

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

Open in new window

~bp
0
 
Jintonix415Author Commented:
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_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


However I am getting a syntax error:

Error 1002 on Line 10, Column 0:

Function LPad(strText, intLen, chrPad )

Syntax error
0
 
Bill PrewCommented:
I guess I would need to see your full script.   To test here, I did the following and it worked fine, producing the following output:

01
001
0000001
001
01-001-0000001-001


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")

x = 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

Function DTSSource(s)
   DTSSource = 1
End Function

Open in new window

~bp
0
 
Jintonix415Author Commented:
Sorry for being really late on this. I did attempt this code and it has worked. Thank you!
0
 
Bill PrewCommented:
Welcome, thanks for wrapping this one up.

~bp
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now