Results 1 to 6 of 6
  1. #1
    wbaroudi is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10

    Find Post_Code in a memo field

    Hi guys,



    I wonder if someone can help. I have a memo field contains demographic of a patient. The data is coming from a clinical application and all the data was clustered together into this memo field. I need to extract the post code out. The issue in England the post code could be 1 letter or 2

    S26 1QB or WF1 6NG and so on. I have a table of the first part of the post codes WF1, WF2, S1, S20,DN20 etc...

    Any idea appreciate it.

    Thanks in advance
    Wessam

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Is the text in a standardized structure - is the post code always following some text that can be counted on to be present and/or info separated by a distinct character such as a semi-colon - for example:

    Some text here; more text; PostCode: S26 1QB; and more text.


    If not, would likely require VBA function that loops through recordset of post codes and tests if each one is in the string.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    wbaroudi is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10
    As a rule there should be the County such as West Yorkshire etc... So I can search for the word Yorkshire and found out that lots of records didn't bother to put a County.

    Lots of computing needs to compare 256 post code (currently) with 20,000 records per month. Arhhh

    Thank you for your suggestion.

    Reg
    W

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The UK postcode structure has so many exceptions, it is very difficult to write definitive rules
    Suggest you use a table of all postcode areas AB, BA, B, G, GIR etc (125 in all).
    The attached zip file has all postcode areas (delete any fields you aren't interested in) together with a PDF with more info on UK postcode structure.

    If you need more than this, I have several UK postcode apps in Access (both free and commercial) available from my website - see link below
    These include tools to get full postal addresses from UK postcodes
    However, there is no longer any requirement to include a county as the postcode gives the precise location for any address

    NOTE: There are currently over 2.6 million UK postcodes of which almost 1.8 million are in active use
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    wbaroudi is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10
    Thank you Colin, I appreciate it.

    Regards
    Wessam

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome. Let me know if you need anything else
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 21
    Last Post: 09-29-2017, 01:30 PM
  2. Replies: 4
    Last Post: 10-17-2016, 06:19 PM
  3. Replies: 9
    Last Post: 07-21-2014, 11:57 AM
  4. Replies: 5
    Last Post: 01-30-2014, 10:55 AM
  5. Replies: 5
    Last Post: 06-19-2012, 10:46 AM

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