Hope someone can help on this one.
I have written a function which works in the same way as the concatenate formula but where required it uses an underscore to make up the length (14 Characters) of the result. I seem however to have hit a minor snag when users input trailing spaces. I thought I could use trim to eliminate them but it doesnt seem to be working any ideas
TIA
Graeme
Code
Function HypCon(CorpAcct, Subdiv)
Dim n, i, iLen, iLen2 As Integer
Dim sCorpAcct, sSubdiv As String
Dim iLen3, iLen4 As Integer
iLen = Len(Trim(CorpAcct))
iLen2 = 7
iLen3 = Len(Trim(Subdiv))
iLen4 = 5
'Determine the length of CorpAcct
Select Case iLen
Case Is <= iLen2
i = Len(Trim(CorpAcct))
sCorpAcct = Trim(CorpAcct)
For n = i To iLen2
sCorpAcct = sCorpAcct & "_"
Next n
Case Else
sCorpAcct = CorpAcct
End Select
'Determine the length of Subdiv
Select Case iLen3
Case Is <= iLen4
i = Len(Trim(Subdiv))
sSubdiv = Trim(Subdiv)
For n = i To iLen4
sSubdiv = sSubdiv & "_"
Next n
Case Else
sSubdiv = Subdiv
End Select
HypCon = sCorpAcct & sSubdiv
End Function
Display More