Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Extracting a reference from a field

Discussion in 'Information Technology' started by Statto1971, Jul 28, 2006.

  1. Statto1971

    Statto1971
    Expand Collapse
    Guest

    Hi,

    I've got a table which has an order reference field. I need to extract a 5
    character code from this field. Unfortunately, due to some being
    automatically generated by different systems and others being manually
    entered there is no consistency to the format, therefore I can't use mid,
    left functions to get the info. However, in every case the code that I need
    always begins with E and is then followed by 4 numbers - is there a formula
    that I could use that could find and extract this code?

    Any help greatly appreciated.
     
  2. Loading...


  3. BruceM

    BruceM
    Expand Collapse
    Guest

    Assuming the field in question is named OrderRef, in a query field you could
    put something like:
    ShortCode: Mid([OrderRef],InStr([OrderRef],"E"),5)
    You could do the same thing in an unbound text box by replacing "ShortCode:
    " with an = sign, or in VBA, but it sounds as if a query may be the cleanest
    option.
    The expression assumes you always have E0000 in the field, and that there is
    either only one letter E or that the first letter E is the one you need.

    "Statto1971" <Statto1971@discussions.microsoft.com> wrote in message
    news:4852048E-3D91-4530-9CBF-15909528F6AA@microsoft.com...
    > Hi,
    >
    > I've got a table which has an order reference field. I need to extract a 5
    > character code from this field. Unfortunately, due to some being
    > automatically generated by different systems and others being manually
    > entered there is no consistency to the format, therefore I can't use mid,
    > left functions to get the info. However, in every case the code that I
    > need
    > always begins with E and is then followed by 4 numbers - is there a
    > formula
    > that I could use that could find and extract this code?
    >
    > Any help greatly appreciated.
    >
    >
     
  4. Mr B

    Mr B
    Expand Collapse
    Guest

    Here is some code that should help:

    Dim varCharLoc
    Dim strSearchText As String
    Dim strCode As String

    strSearchText = Me.txtInput
    varCharLoc = InStr(1, strSearchText, "E")
    'if the letter "E" is in the string
    If varCharLoc > 0 Then
    'read the code into the variable "strCode"
    strCode = Mid(strSearchText, varCharLoc, 5)
    Me.txtCode = strCode
    End If


    This code takes the entry in a text box named "txtInput" and assigns it to a
    local vaiable named "strSearchText". Then the value in the variable is
    checked to see if the "E" is actually in the string.
    If it is there, then it will extract the "E" plus the next 4 characters
    assigning the value extracted to a local variable named "strCode". The value
    in the "strCode" variable is then placed in another text box named "txtCode".

    You can adjust these parameters as needed.
    --
    HTH

    Mr B


    "Statto1971" wrote:

    > Hi,
    >
    > I've got a table which has an order reference field. I need to extract a 5
    > character code from this field. Unfortunately, due to some being
    > automatically generated by different systems and others being manually
    > entered there is no consistency to the format, therefore I can't use mid,
    > left functions to get the info. However, in every case the code that I need
    > always begins with E and is then followed by 4 numbers - is there a formula
    > that I could use that could find and extract this code?
    >
    > Any help greatly appreciated.
    >
    >
     

Share This Page