expression Help

d9pierce · #1 (**permalink**) 01-10-12, 07:27

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

Sinndho · #2 (**permalink**) 01-10-12, 08:40

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.

d9pierce · #3 (**permalink**) 01-10-12, 08:52

Not sure how to incorporate that into my expression?

d9pierce · #4 (**permalink**) 01-10-12, 09:04

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

healdem · #5 (**permalink**) 01-10-12, 09:49

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

mariostg · #6 (**permalink**) 01-10-12, 09:57

Insert this in a module. You must have Microsoft VBScript Regular Expression reference.

Quote:

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

d9pierce · #7 (**permalink**) 01-10-12, 10:43

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

mariostg · #8 (**permalink**) 01-10-12, 10:53

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.

healdem · #9 (**permalink**) 01-10-12, 10:56

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.

healdem · #10 (**permalink**) 01-10-12, 11:07

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.

d9pierce · #11 (**permalink**) 01-10-12, 15:07

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

Internet Services

Web Development

Digital Marketing

Consumer Tech