Results 1 to 5 of 5
  1. #1
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88

    Proper Case Format with Directions in Upper Case

    I have created a form in Access in that form I have a field named address. In the property sheet under Event Tab, After Update section I added the VBA Code:
    Me.Address = StrConv(Me.Address, vbProperCase)
    However, When I add and address with a direction it looks like this:
    1212 Main Street Sw


    I would like it to read “1212 Main Street SW”
    So I need to figured out how to Add the Upper Case function to NW , SW, NE, SE

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is this the same issue as reported here?

    My guess it you will have to analyze your data to see what/if any patterns exist.
    You could check for your street directions and replace s,sw,se,n,ne, nw... with S,SW,SE,N,NE,NW etc as appropriate.
    As per the older post, you will probably want to use the BeforeUpdate event --last spot to check before updating/changing a record.
    You may also want to review the UCase function and the Like operator.
    Good luck.

  3. #3
    wes9659 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2014
    Location
    Dover, Ohio
    Posts
    88
    I am not sure exactly what you mean could you possibly write the VBA code out? Thank you

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It isn't the vba code, although that will be important, --it is the format and variability of the incoming address info that is the major concern.

    What does your data look like? How consistent is it? Are there patterns/groups that are similar?

    Does it look like this --starts with number, then street/road name(usually) but may include a direction, then Apt or unit and possibly direction?
    4103 SANTA ROSALIA DR #D
    12763 DE HAVEN AV S
    2348 W 255TH ST
    547 1/4 S BREED ST Apt 5
    1163 S HOOVER ST #1
    1909 1/2 TALMADGE ST
    4806 1/2 FIRESTONE BL NW
    5544 YOLANDA AV #307
    4859 1/4 N LONG BEACH AV WEST 27


    In order to create the vba to process the data, you need to know the format of the data you are working with.
    Then create some sample records -both good and bad format - and test your vba, adjust and retest.

    Try Google with this parameter msaccess vba example parsing address info and look at some of the results to get some insight in to the issues involved and possible solutions.

    Many people have spent careers parsing and formatting name and address data. It usually requires "human eyeballs" at some point. This is not a trivial subject - typos, missing data, punctuation, spelling, abbreviations etc are often found in real data. Analysis of your needs and your actual data and attention to detail in design and testing are key.

    You may find this Canadian Addressing Guide of some value - it does mention US Postal Addresses.

    Good luck with your project.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree with Orange; far too many variables and hiccups to deal with. Better to just force all to UPPER CASE. What would be so bad about 1212 MAIN ST. SW ?
    Or is this for something more than just displaying/entering/storing data, such as mailing labels? Even then, I'd be happy with UCase as long as the boss is.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change text case from upper to proper case
    By s.nolan in forum Access
    Replies: 1
    Last Post: 12-02-2015, 10:56 AM
  2. Replies: 1
    Last Post: 06-29-2014, 01:40 PM
  3. Replies: 5
    Last Post: 10-23-2012, 03:55 PM
  4. Upper & Lower Case in table
    By Forums10 in forum Access
    Replies: 4
    Last Post: 01-31-2011, 09:01 AM
  5. Is there a way to force a field do be upper case?
    By newtoAccess in forum Access
    Replies: 1
    Last Post: 11-28-2010, 04:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums