Results 1 to 11 of 11

Thread: expression Help

  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Question Unanswered: expression Help

    Hi all,
    I am using an expression in a table field to create an account number. The expression is:
    Right([Creation],4) & Left([CompanyName],3) & [CompanyID]+111
    and what I need to find out is how do I eliminate any charactors other than letters form A-Z in the company name field? Example, if CompanyName is A.B.C. Supply, then how do I make the expression take out the .'s in and Make it ABC. I would also like to know how to force all caps in the CompanyName part of the expression, Example Construction one, CON instead of Con.
    Thanks to all.
    David Pierce

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If there are only a few characters to be replaced or removed, you can use one or more expressions with the Replace() function:
    Code:
    CompanyName = Replace(CompanyName, ".", "")
    Otherwise, you'll need to use regular expressions.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    7

    Unsure

    Not sure how to incorporate that into my expression?

  4. #4
    Join Date
    Jan 2012
    Posts
    7

    Exclamation Question Clairification

    Hi,
    Maybe i wasnt clear enough? I want to take the above expression and make it so that it only shows letters A-Z, no , . ! # or anything but the letters A-Z in the CompanyName field of my expression. So, if company name in field is A.B.C. supply or 1 Construction it gives me ABC or CON as the left 3 of it in all caps. This expression is wrote in Account field. I dont want to remove them from the actual CompanyName field, just in my expression so an account number would be such as 2012ABC112 or 2012CON113

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you cannot constrain the source data (using a format mask or similar) then you could write a function using whatever rules you specify.

    eg
    Code:
    Private Function TruncateCoName(ByVal CoName As String, Optional PadWith As String = "!", Optional NoChars As Integer = 3) As String
    'this function takes a string and returns the first n digits which are alpha
    'optional parameters
    'the number of digits to return, default is 3
    'the symbol to pad the resultant value if there are insufficent alpha symbols in the supplied name, default is "!"
    'this code works with ASCII symbols, it will not work with unicode
    'OK so lets establish some ground rules
    If NoChars < 3 Or NoChars > 10 Then NoChars = 3 'anything otuside those values demonstrates the user or developer is taking the piss, so limit to 3
    If IsNull(PadWith) Or PadWith = "" Then PadWith = "!" ' similarly if nothing is supplied for padwith then they get the default
    'Im not having some sloppy developer screwing this up with bad values
    'OK so we think we have got valid data.
    TruncateCoName = "" 'create an empty string
    Dim ThisSymbol  As String
    Dim SymbolValue As Integer
    While Len(TruncateCoName) < NoChars And Len(CoName) >= 1 'keep in this loop until we have the required number of characters AMD the source has some characters left
      ThisSymbol = UCase(Left(CoName, 1)) 'get the first character from the company namne
      SymbolValue = Asc(ThisSymbol)
      If SymbolValue >= 65 And SymbolValue <= 90 Then 'check the value of the character is between 65 and 90 [ASCII A..Z]
        'note you could expand the above test to include other 'approved' symbols
        'see http://www.google.co.uk/imgres?imgurl=http://www.asciitable.com/index/asciifull.gif&imgrefurl=http://www.asciitable.com/&h=488&w=715&sz=28&tbnid=2U31hF4tTw886M:&tbnh=77&tbnw=113&prev=/search%3Fq%3Dascii%2Bnumbers%26tbm%3Disch%26tbo%3Du&zoom=1&q=ascii+numbers&docid=JMtOixefP_tDJM&hl=en&sa=X&ei=AEwMT829NJOn8gPD7tmiBg&sqi=2&ved=0CEMQ9QEwAw&dur=486
        'for valid ascii symbols
        TruncateCoName = TruncateCoName & ThisSymbol 'append the symbol to our return value
      Else 'else do stuff all as we don't want the symbol
      End If
      CoName = Mid(CoName, 2)
    Wend
    'check the return value is of the required length
    While Len(TruncateCoName) < NoChars
      TruncateCoName = TruncateCoName & PadWith 'if not add additional PadWith symbols untill we do
    Wend
    'return TruncateCoName
    End Function
    then call the function using
    tbResult.Value = TruncateCoName(CoName, Padwith, NoDigits)
    where
    CoName is the name you want to truncate
    PadWith is the optional symbol used to pad the resultant string
    NoDigits is the number of symbols to use

    eg
    tbResult.Value = TruncateCoName("A.Aardvark Towing","",10)
    which results in "AAARDVARKT"
    tbResult.Value = TruncateCoName("123 T.a.5.x.i.s.5plc","",6)
    or
    which results in "TAXISP""
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2011
    Posts
    34
    Insert this in a module. You must have Microsoft VBScript Regular Expression reference.
    Function strip_ponctuation(LineToFix As String) As String
    Dim re As New RegExp
    Dim pattern As String
    re.pattern = "[^a-zA-Z]"
    re.Global = True
    If (re.test(LineToFix) = True) Then
    strip_ponctuation = re.Replace(LineToFix, "")
    Else
    strip_ponctuation = LineToFix
    End If
    End Function

  7. #7
    Join Date
    Jan 2012
    Posts
    7

    Question

    Hi again,
    I have tried some examples given but dont seam to be able to get them to work like I am looking for?
    Let me try to be more clear on this as I dont think it is that complicated?

    I have a table, lets say it has the fields:
    CompanyID(Auto)
    CompanyName(Txt)
    Creation(Date)
    Account(Calculated)

    OK, I want to take the [Creation] which is a date by the right 4 (2012)
    I want to take the [CompanyName] left 3
    and I wan to take the [CompanyID] +111
    to creat an account # in the [Account] Field
    by using this expression in the account field of the table:
    Right([Creation],4) & left([CompanyName],3) & [CompanyID]+111
    Field type is Calculated
    This works fine however i want to remove anything but letters or force it to only show A-Z in the "Left([CompanyName],3) portion of this expression. I did figure out that if I added > to the format on the field, all caps worked great.

    So, If I have a CompanyName called A.B.C. Supply, I want it to eliminate the .'s and just give me the ABC on the acount Number. or if I have a CompanyName 1-Construction I want it to give me CON. Just letters A-Z only, no special caractors and such. I do hope this makes some sense?

    I just cant imangine this has to be so complicated just to come up with a simple account number? Please let em know if I am not explaining this correctly.
    Thanks

  8. #8
    Join Date
    Apr 2011
    Posts
    34
    We are all volunteers here. And what you are trying to accomplish is indeed fairly simple. You said you try the proposed solutions and say but dont seam to be able to get them to work like I am looking for. you don't seem to be sure yourself...
    What output did you get from the proposed functions and what is it you expected instead? Healdem gave several exemples. I did not. but if you feed something like A.B.C_ to my function, output will be ABC. Straightforward.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so whats the problem?
    the code I supplied will contract a specified string varaible to 3 alpha characters. remvoing any non alpha values.

    the code supplied by mariostg uses VBS and regular expressions
    it won't cater well for a resultant company name of less than 3 characters, but it shoudl work. VBS had a nasty habit of triggering some anti virus software so I tend not to use it on code run on customer sites.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looking at your code I'd expect something like
    Right([Creation],4) & TruncateCoName(CompanyName],"!",3) & [CompanyID]+111
    however you code has what i'd argue are several possible flaws
    1 you are using 'creation' which you say is a date

    so what you'd probably want is
    year(creation)
    the companyid +111 is a truly horrible kludge
    ..you are taking a (presumably) autogenerated value and adding 111 to it.

    Im guessing you want to create a unique customer ID
    in which case prefixing with a year is a silly thing to do, bear in mind you can always retrieve the account creation date from your table, assuming you store that value
    what most accountancy retail packages I've seen (including Sage) is to create an accoutn using the first few digits of the customer name and suffix with an ascending numeric value

    eg lets say they used (upto) for the name bit and padded then number to make the overall account name of 10 characters

    eg
    SMITH & SONS could be SMITHS0032
    Smiths Industries could be SMITHS0123
    SMITH, J coudl be SMITH01234

    thats a different proposition and would require use of querying the table to find the current highest vlaue for, say, 'SMITHS'
    yes you'd use something simialr to mariostg's or my functions to get the name bit, but after that you'd query the db then retrieve the highest current value, add one and write it back to the table.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jan 2012
    Posts
    7

    Question Clairification

    I will take a deeper look at this tonight
    Thank you all
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •