Jay Holovacs help required!

Hi Jay,
sorry if I'm writing to you but I have a question that I don't known
how to solve.

To handle names with mixed cases, on my form, I'm using the code you
wrote, I found it at this web-link: http://www.mvps.org/access/strings/str0008.htm
Of course, it works great and as I read it should be modular so that
it possible to add additional rules to handle special sequences of
characters.

What I'd like to do is to add in it a piece of code, to be able to
transform for example, string like that ''John spa'' in ''John
S.p.A.''
My trouble is that any time I write S.p.A., I do that hundred of
times, I have to remember to hit the point after each letters and at
same time I have to mind the case of the words.
It will be great if it doesn't matter how I to write ''spa'', the code
would change it in ''S.p.A.'' for me.
If you look the last part of the code you'll see a function to handle
roman number. If you write ''vii'' the code will change in ''VII'' for
you.
Could you think is possible to do something like that for my sequences
of words?
I'm not absolutely able to that, so I was thinking about you.

The words I'd like to be able to handle are: S.r.l. S.p.A. s.n.c.
s.a.s. S.r.l.u.

I hope you will be give to me an hand.....!
Thanks,
Stefano.

0
riccifs
9/20/2007 6:58:59 AM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
700 Views

Similar Articles

[PageSpeed] 43

Stefano,

The easiest way would be to add the acronyms to the Autocorrect option of 
the spell checker.

But, just for grins, try the modification below. Replace the current sub 
"special_name" with the one below (or add the code between the ***'s)

'==========================
Private Sub special_name(str As String, ps As Integer)
    'expects str to be a lower case string, ps to be the
    'start of name to check, returns str modified in place
    'modifies the internal character (not the initial)

    Dim char2 As String
    char2 = Mid$(str, ps, 2)    'check for Scots Mc
    If (char2 = "mc") And Len(str) > ps + 1 Then    '3rd char is CAP
        Mid$(str, ps + 2) = UCase$(Mid$(str, ps + 2, 1))
    End If

    char2 = Mid$(str, ps, 2)    'check for ff
    If (char2 = "ff") And Len(str) > ps + 1 Then    'ff form
        Mid$(str, ps, 2) = LCase$(Mid$(str, ps, 2))
    End If

    char2 = Mid$(str, ps + 1, 1)    'check for apostrophe as 2nd char
    If (char2 = "'") Then    '3rd char is CAP
        Mid$(str, ps + 2) = UCase$(Mid$(str, ps + 2, 1))
    End If

    Dim char3 As String
    char3 = Mid$(str, ps, 3)    'check for scots Mac
    If (char3 = "mac") And Len(str) > ps + 1 Then    'Mac form
        Mid$(str, ps + 3) = UCase$(Mid$(str, ps + 3, 1))
    End If

    Dim char4 As String
    char4 = Mid$(str, ps, 4)    'check for Fitz
    If (char4 = "fitz") And Len(str) > ps + 1 Then    'Fitz form
        Mid$(str, ps + 4) = UCase$(Mid$(str, ps + 4, 1))
    End If

    Dim char5 As String
    char5 = Mid$(str, ps, 5)    'check for Fitz
    If (char4 = "fitz") And Len(str) > ps + 1 Then    'Fitz form
        Mid$(str, ps + 4) = UCase$(Mid$(str, ps + 4, 1))
    End If

'****** check for special acronyms ******
    Dim spChar As String
    'strip periods
    spChar = Replace(str, ".", "")

    'check for S.r.l., S.p.A., s.n.c., s.a.s., S.r.l.u.
    Select Case spChar
        Case "srl"
            str = "S.r.l. "
        Case "srlu"
            str = "S.r.l.u."
        Case "spa"
            str = "S.p.A."
        Case "snc"
            str = "s.n.c."
        Case "sas"
            str = "s.a.s."
    End Select
'****** end check for special acronyms ******

End Sub
'==========================



HTH
-- 
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"riccifs@tin.it" wrote:

> Hi Jay,
> sorry if I'm writing to you but I have a question that I don't known
> how to solve.
> 
> To handle names with mixed cases, on my form, I'm using the code you
> wrote, I found it at this web-link: http://www.mvps.org/access/strings/str0008.htm
> Of course, it works great and as I read it should be modular so that
> it possible to add additional rules to handle special sequences of
> characters.
> 
> What I'd like to do is to add in it a piece of code, to be able to
> transform for example, string like that ''John spa'' in ''John
> S.p.A.''
> My trouble is that any time I write S.p.A., I do that hundred of
> times, I have to remember to hit the point after each letters and at
> same time I have to mind the case of the words.
> It will be great if it doesn't matter how I to write ''spa'', the code
> would change it in ''S.p.A.'' for me.
> If you look the last part of the code you'll see a function to handle
> roman number. If you write ''vii'' the code will change in ''VII'' for
> you.
> Could you think is possible to do something like that for my sequences
> of words?
> I'm not absolutely able to that, so I was thinking about you.
> 
> The words I'd like to be able to handle are: S.r.l. S.p.A. s.n.c.
> s.a.s. S.r.l.u.
> 
> I hope you will be give to me an hand.....!
> Thanks,
> Stefano.
> 
> 
0
Utf
9/23/2007 2:26:00 AM
On 23 Set, 04:26, Steve Sanford <limbim53 at yahoo dot com> wrote:
> Stefano,
>
> The easiest way would be to add the acronyms to the Autocorrect option of
> the spell checker.
>
> But, just for grins, try the modification below. Replace the current sub
> "special_name" with the one below (or add the code between the ***'s)
>
> '==========================
> Private Sub special_name(str As String, ps As Integer)
>     'expects str to be a lower case string, ps to be the
>     'start of name to check, returns str modified in place
>     'modifies the internal character (not the initial)
>
>     Dim char2 As String
>     char2 = Mid$(str, ps, 2)    'check for Scots Mc
>     If (char2 = "mc") And Len(str) > ps + 1 Then    '3rd char is CAP
>         Mid$(str, ps + 2) = UCase$(Mid$(str, ps + 2, 1))
>     End If
>
>     char2 = Mid$(str, ps, 2)    'check for ff
>     If (char2 = "ff") And Len(str) > ps + 1 Then    'ff form
>         Mid$(str, ps, 2) = LCase$(Mid$(str, ps, 2))
>     End If
>
>     char2 = Mid$(str, ps + 1, 1)    'check for apostrophe as 2nd char
>     If (char2 = "'") Then    '3rd char is CAP
>         Mid$(str, ps + 2) = UCase$(Mid$(str, ps + 2, 1))
>     End If
>
>     Dim char3 As String
>     char3 = Mid$(str, ps, 3)    'check for scots Mac
>     If (char3 = "mac") And Len(str) > ps + 1 Then    'Mac form
>         Mid$(str, ps + 3) = UCase$(Mid$(str, ps + 3, 1))
>     End If
>
>     Dim char4 As String
>     char4 = Mid$(str, ps, 4)    'check for Fitz
>     If (char4 = "fitz") And Len(str) > ps + 1 Then    'Fitz form
>         Mid$(str, ps + 4) = UCase$(Mid$(str, ps + 4, 1))
>     End If
>
>     Dim char5 As String
>     char5 = Mid$(str, ps, 5)    'check for Fitz
>     If (char4 = "fitz") And Len(str) > ps + 1 Then    'Fitz form
>         Mid$(str, ps + 4) = UCase$(Mid$(str, ps + 4, 1))
>     End If
>
> '****** check for special acronyms ******
>     Dim spChar As String
>     'strip periods
>     spChar = Replace(str, ".", "")
>
>     'check for S.r.l., S.p.A., s.n.c., s.a.s., S.r.l.u.
>     Select Case spChar
>         Case "srl"
>             str = "S.r.l. "
>         Case "srlu"
>             str = "S.r.l.u."
>         Case "spa"
>             str = "S.p.A."
>         Case "snc"
>             str = "s.n.c."
>         Case "sas"
>             str = "s.a.s."
>     End Select
> '****** end check for special acronyms ******
>
> End Sub
> '==========================
>
> HTH
> --
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
>
> "ricc...@tin.it" wrote:
> > Hi Jay,
> > sorry if I'm writing to you but I have a question that I don't known
> > how to solve.
>
> > To handle names with mixed cases, on my form, I'm using the code you
> > wrote, I found it at this web-link:http://www.mvps.org/access/strings/str0008.htm
> > Of course, it works great and as I read it should be modular so that
> > it possible to add additional rules to handle special sequences of
> > characters.
>
> > What I'd like to do is to add in it a piece of code, to be able to
> > transform for example, string like that ''John spa'' in ''John
> > S.p.A.''
> > My trouble is that any time I write S.p.A., I do that hundred of
> > times, I have to remember to hit the point after each letters and at
> > same time I have to mind the case of the words.
> > It will be great if it doesn't matter how I to write ''spa'', the code
> > would change it in ''S.p.A.'' for me.
> > If you look the last part of the code you'll see a function to handle
> > roman number. If you write ''vii'' the code will change in ''VII'' for
> > you.
> > Could you think is possible to do something like that for my sequences
> > of words?
> > I'm not absolutely able to that, so I was thinking about you.
>
> > The words I'd like to be able to handle are: S.r.l. S.p.A. s.n.c.
> > s.a.s. S.r.l.u.
>
> > I hope you will be give to me an hand.....!
> > Thanks,
> > Stefano.

Hi Steve,
many thanks for your answer, but I just solved using the regular
expressions.
I asked to my brother, his use to coding in pHp and he wrote a
function for me.
Regexp give to me great flexibility with text string and I can cover
any type any combinations....

Bye,
Stefano.

0
riccifs
9/24/2007 8:10:58 AM
Reply:

Similar Artilces:

Chart Help Please!
Hello all, I have a very simple chart... The data looks like this: A B Data1 3.65 Data2 7.00 The data charts fine and looks good but right now the bar for Data1 and Data2 are the same color. is there a way to have the bar for data1 one color while data2 is a different color? Thank you all for you excellent help!! In 2003 Select the series and Format Data Series>Options>Vary colors by point. In 2007 Select the series and Format>Date Series>Fill>Vary colors by point. Gord Dibben MS Excel MVP On Sat, 28 Nov 2009 11:32:02 -080...

mail queue help
I get some emails for users who the sender has miss spelled their or has it in the wrong format etc. I how do I have exchange to delete this from the queue automatically. I have the filter if not in active directory box checked. thanks "Eddie" <Eddie@discussions.microsoft.com> wrote: >I get some emails for users who the sender has miss spelled their or has it >in the wrong format etc. I how do I have exchange to delete this from the >queue automatically. I have the filter if not in active directory box >checked. thanks Enabling the directory checking on the...

Index | Match Formula (help)
I'm using this formula =INDEX(Sheet2!$A$1:$E$30,MATCH(A2,Sheet2!$A$1:$A$30,0),MATCH(B2,Sheet2!$A$1:$E$1,0)) to return a value from a table. I've copied this formula do a column of cells and getting the #N/A message in the cell. I understand why this is taking place, because the cells are null. Can I include in this fromula something to make this return an blank value if the cells dont have data entered yet. Sheet1 has the null cells not the table. Cell A2 and B2. I'm entering data in these cells as needed but would like to have the index formula copied and ready to find when...

Pls Help on Mapi Problem
Hi, we are getting this: "cant open this item. the mapi spooler has already begun transmitting this message" in our outlook 2000, Personal Folder, emails will not go out, they all sit in the outbox. Can someone point me in the right direction. Thanks Jay >From: jay@gjan.com (jay=A0patel) >Hi, >we are getting this: >"cant open this item. the mapi spooler has already begun >transmitting this message" >in our outlook 2000, Personal Folder, emails will not go >out, they all sit in the outbox. >Can someone point me in the right direction....

EXCEL HELP!!
I want to number a column 4000-5000, I don't want to sit and type all those numbers out by hand. Is there a way to number consecutively?? Please help?? Hi in A1 enter the following formula =ROW()+3999 copy this formula down for 1000 rows -- Regards Frank Kabel Frankfurt, Germany jenniferwillis@wrmf.com wrote: > I want to number a column 4000-5000, I don't want to sit > and type all those numbers out by hand. Is there a way to > number consecutively?? Please help?? Jennifer Frank's method will do what you want, but it will leave formulas in the cells, which you may ...

Query Expression help
What is the correct expression to get average handle time per return, rather than per batch (returns are logged in batches that vary in quantity). Currently using this espression to get AHT per batch- Handle Time: [Sign In Time]-[Sign Out Time] I would like to divide this sum by [Processed], which is already an expression Processed: [Filed]+[Sent to Accounting]+[Pended] to ger AHT per return. I tried- Handle Time: sum([Sign In Time]-[Sign Out Time])/[Processed], but that doesn't work. Help! Perhaps the following, although I suspect that will not give you the results you expect. S...

Copying Formulas in 2003 HELP HELP HELP
My question is a little unique. My formula is G1*1.1 I put the result in H1. I can drag a formula down in a column, and it increments the row number, but the resulting answer is the same in H2 as it was in H1. If I press F2 in H2 then enter, the answer is correct. My spreadsheet is almost 15,000 lines, and I do not want to have to F2 each cell in the H column....... Any ideas or suggestions? Thanks...... Roxy Hi Roxy check tools / options / calculation - ensure that it is set to automatic Cheers JulieD "RoxyB94" <RoxyB94@discussions.microsoft.com> wrote in mes...

Help please, can't start up!!
When I start the PC, after the loading bar for Microsoft thing finishes,instead of going to login screen it gives me a blue screen, and it says Page_fault_in_nonpaged_area. This just happened overnight!!!! Help please. :( -- Excessive Here are some hints ( 'Bug Check 0x50: PAGE_FAULT_IN_NONPAGED_AREA' (http://msdn.microsoft.com/en-us/library/ms793437.aspx) ). Try starting the system in safe mode (keep tapping F8 after power-on). -- whs Additional info: - I use Spyware Doctor scan every few days - I left my computer on for the night like always,and ...

help with formula #8
Thanks for reading In Cell (A1) I have a drop down menu that has a validated series of billing codes: None, 2010, 2011, 2012, 3010, 3011, 3012 In Cell (A2) I would like to assign a number value that applies to each billing code in (A1). None = 0, 2010 =27.5, 2011 =100, 2012 =12.50, 3010=27.5, 3011 =100, 3012 =12.5. So in Cell (A2), I need a forumla that will read cell (A1) once the value is selected from the drop down menu and assign the correct value (above) to cell (A2) Is this possible? Signed, Freezing in New Hampshire =LOOKUP(A1,{"None",2010,2011,2012,3010,2011,30...

Translation Help
"procedure entry point LdrLockLoaderLock could not be located in the dynamiclink library NTDLL.DLL&#8207;" I'm getting this erroe w/ Office XP, Outlook And Excell. I found the resolation on Microsoft KB http://www.microsoft.com/israel/support/kb/faq.aspx? s=47&c=6&n=1 But the only place I can find it is in Israel. I can't find any Arabic to english translation ...

Please help, problem with Outlook 2002 freezing
I am having an odd problem with Outlook 2002 hanging when started. It makes it to right to the point where it tries to check for new mail, then hangs. When I close it and do the end program deal, then try to reopen it, it seems to work fine. This happens everytime I use Outlook. Any ideas anyone??? Thanks! find and delete the *.srs file for your profile. http://www.outlook-tips.net/beginner/filepath.htm if you need help finding it. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook...

Validation
With validation you get an arrow for a pull down, when you are standing in the cell. If you not standing in this cell you do not know there is a pulldown menu. Does one of you know if it is possible that the arrow is shown everytime. thnks Not with data validation, the arrow, as you have discovered, is only visible when the cell is selected. If you want it the other way, you will unfortunately need to 'roll your own' validation with comboboxes from the 'control toolbox' and write code for the validation -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_...

help!!!
microsoft word isnt letting me make changes to an already saved document, when i go to type, the toolbar at the bottom of the screen says "this modification is not allowed because the selection is locked" it wont even let me open a fresh new document, it says the same thing when i go to type on a blank document. how do i fix this???? It sounds like you are probably using a trial version of the software that has now expired or you have not activated your copy of the software? If so, see: http://us20.trymicrosoftoffice.com/faq.aspx -- Hope this helps. Please re...

help #3
could not start the microsoft exchange internet mail service on local computer Error 1068 the dependency service or group failed to start Greetings This generally happens when one of the services that the System Attendant depends on cannot be started For details refer to: XADM: System Attendant Fails To Start With Error 1068 http://support.microsoft.com/?id=182514 Hope this helps! Regards, Fareed Mohammed Khan MCTPK User Group "lutfi@dbs.net.tr" <anonymous@discussions.microsoft.com> wrote in message news:22b4f01c45d80$ae136440$a501280a@phx.gbl... > could not start...

OWA Login requires FQDN\User Name when logging in
Ok, I have found this article before but now I cannot relocate. After installing Exchange Sp1, OWA requires FQDN\User Name login, and I want it back the way it was before. MS did come out with fix for this, anyone have the kb#? Thanks in advance! ...

I messed up and need help
I tried to change the folder that excell uses to another folder and now when i open excell it cycles thru all my excell files and opens them but withe different error msgs like html import errors problem came up in the folling areas during load missing file and no matter qwhen i click on "ok" or "cancel" it starts the recycl again and i cannot get to any file. then i get microsoft excel first pps file format is not valid and file is not in recognizable format. Can anybody explain how to get out of this and tell me how i can chang the folder for excel to use -- wwood -----...

Help, documents on desktop not loading
Hello, Hope you can help, I have saved some excel sheets to my desktop but when I double click to open all i get is the following message "cannot find the file c:/doc&sett......... make sure path and filename...etc although if i open excel and go through the file menu these open without a problem. I have went through all the usual routes and everything is pointing too the right place. Anyone got any ideas please Thank you in advance Sometimes one of these works when you're having trouble with double clicking on the file in windows explorer: Tools|Options|General|Ignore...

I need help charting
I am new to charting. I need to chart 5 different companies with a cost bid and a cost hit by company. Can you explain how. Very confused. Thanks. Hi, Don't know what a cost hit is? Maybe you can show us some sample data and tell us what kind of chart you need - pie, line, column....? -- Thanks, Shane Devenshire "mgm" wrote: > I am new to charting. I need to chart 5 different companies with a cost bid > and a cost hit by company. Can you explain how. Very confused. > > Thanks. > Hi, Thanks for your response. For example 5 companies, hit and...

Help on help
At work I use Windows XP Professional Version 2002 SP2 with Excel 2002. When I need help, I press F1 from anywhere, I get the normal help with tabs of "Contents", "Answer Wizard", and "Index". This is the same help for a long time and it's great to answer most questions. But at home I use Windows XP Home Edition with Office 2003. I press F1 in Excel and it tries to search the web. I'D REALLY LIKE THE OLD HELP THAT I HAVE AT THE OFFICE!! Does anyone know if this is a setting that can be changed??? THANK YOU! Although the help in 2003 will neve...

REQ help: powerpoint printouts have blank lines down the middle
When printing powerpoint slides at work there is a clearly defined blank rectangle running the entire length of the slide dividing the image on the slide. Oddly enough if an image is placed on top of the original image that 2nd image is not affected. IE) if I were to print out a slide with a screenshot of googlemaps and had pasted a picture in the center to depict a building or landmark the googlemap image would have a blank rectangle, but the pasted image would not be divided/affected. This issue only affects powerpoint slides. There are no issues with .jpgs, ..pdfs, word, e...

Prospect require a Portfolio Management to integrate with GP
Does anyone knows any soution that can manage client portfolio at the same time it also can link with GP for manage accounting and finance. Prospect requirements Client & Agents Mgmt - Customer Profiling - Add/Edit/Delete Customer details - Agents Profiling - Add/Edit/Delete Customer accounts (commitment, invested, redeemed) amounts - Generate monthly reports of portfolio holdings - Cheque reconciliation - Agents Commissions monitoring (up-front & trailer) - Referrals commissions monitoring - Generate monthly commissions report Portfolio Mgmt - Handle buying and selling of share...

filtering help
My boss uses filtering to to look up information. Normally she can filter on column A, which is what she wants to do. Now when she highlights column A and filters, the drop-down arrow appears on column K. What could be preventing the drop-down arrow from staying on column A? Thanks. Make sure she removed any previous Autofilter. Data|filter|autofilter (not checked) Then select the whole range (not just a column) and apply data|filter|autofilter. Excel likes to remember what you previously did. Sometimes, it's not what you want. I select the whole range and don't leave it ...

*****PLEASE HELP ****** Outlook 2007 Hangs Boot
I just did a clean install of Windows Vista Ultimate 64-bit (Works Great) but when I Installed MS Office 2007 it worked great for a few days, now when ever i start Outlook it hangs up and i get this Dialog box "Config Outlook accounts " in the upper left corner, also the round swerling circle Blue or Green and thats all i get. All of the other Office programs seem to work fine, If I start Outlook in safe mode it seems like its opening for the first time every time?? It looks like none of my view setting stay the same, If i go to disabled Items there is nothing listed, Still do...

Drop-Down menu grayed out except for "File" and "Help"
File and Help are the only drop down options on my menu bar that are not grayed out. The other drop downs Edit, View, Insert, etc are not showing a drop down option. The buttons for the same options work fine. For example, I can cut and paste without problems through buttons. Any thoughts on why the only drop downs working are FIle and Help?? Thanks in advance MW Maybe someone/something was customizing excel for their workbook and forgot to fix up your toolbar on the way out. I'd try tools|customize|Toolbars tab select worksheet menu bar click reset (You will lose any customizati...

help help help
I just installed Micosoft Office XP Professional with no problem. However, whenever I try to perform any task such as opening contacts area, I receive a dialog box with Microsoft Outlook and a yellow exclamation point. Also, included in the dialog box are the words could not open the item, try again. Could you please tell me what the problem might be and how to fix it? Do you connect to an Exchange server or do you use a pst-file? When exactly does it happen, when you try to open the Contacts folder from the folder list? Was it a clean install or an upgrade? -- Robert Sparnaaij [...