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