none
How many characters are in a line?

    Question

  • Using Access 2007, I’m evaluating data in a multi line memo field [Contents] that looks like the below.

     

     

    Company: Fox Television Stations, Inc.

    Company Code: 33783713

    Agent Firstname: Audrey

    Agent Middlename: Rose

    Agent Lastname: Simon

     

    I’m trying to extract the company name using the below SQL.

     

    SELECT tblRequests.From, IIf(InStr([Contents],"Company: "),Mid$([Contents],(InStr([Contents],"Company: ")+9),29),"N/A") AS Company

    FROM tblRequests;

     

    My problem is the 29 in the Mid$.   Not all companies have 29 characters in their name.

    I need a way to get all characters starting at (InStr([Contents],"Company: ")+9) to end of the line.

    Is there a way to find how many characters are in a line containing the string "Company: "?

    Any suggestions are appreciated.


    Thursday, May 19, 2011 12:48 PM

Answers

  • Thanks again John for the advice, but I was never comfortable with arrays.

    You did help by making me think of consistencies in the data “assumptions”.

    I decided to go with something I call string math, since “Company Code: ” always follows “Company: ”

    Below is the SQL that worked for me.

     

    SELECT IIf(InStr([Contents],"Company:"),Mid$([Contents],(InStr([Contents],"Company:")+9),(InStr([Contents],"Company Code:")-2)-(InStr([Contents],"Company:")+9)),"N/A") AS Company

    FROM tblRequests;

     

    • Marked as answer by h2fcell Thursday, May 19, 2011 5:49 PM
    Thursday, May 19, 2011 5:49 PM

All replies

  • Assuming that there is a new line at the end of the line, you can use Instr to find the first Chr(13) & Chr(10) (new line in access) to locate the end of the a line.

    Personnally, I would use custom VBA function such as the one below.

    In your query use something like the following.  I am assuming that the first line is always the one you want and that the first line is always the company line.
       Mid(fGetSection([Contents],CHr(13) & Chr(10),1),9)

    Public Function fgetSection(strIn, _
       Optional strDelimiter As String = ";", _
       Optional intSectionNumber As Integer = 1)
    '==============================================================
    ' Procedure : fgetSection
    ' Author    : John Spencer
    ' Purpose   : Return section of string
    ' Access 2000 and later
    ' Handles strings, dates, numbers, and null values
    '==============================================================

    Dim strArray As Variant

       If Len(strIn & vbNullString) = 0 Then
          getSection = strIn
       Else
          strArray = Split(strIn, strDelimiter, -1, vbTextCompare)
         
          If UBound(strArray) >= intSectionNumber - 1 Then
             fgetSection = strArray(intSectionNumber - 1)
          Else
            fgetSection = Null
          End If
         
       End If

    End Function


    John Spencer Access MVP 2002-2005, 2007-2011 The Hilltop Institute University of Maryland Baltimore County
    Thursday, May 19, 2011 1:02 PM
  • Thank you for you response John.

    Your code is useful but your assumptions are incorrect.

    The “Company: “ is not always found in the first line.  Sometimes “Company: “ is not in the data at all, which is why I use the IIf(InStr([Contents],"Company: "), Mid$(), “N/A”).

     

    First I need to find the line containing “Company: “

    Second I need to get the total number of characters in that line.

    Then I could replace my 29 with:

    (Total line character count )- (InStr([Contents],"Company: ")+9)

     

    Thursday, May 19, 2011 1:45 PM
  • Ok, then modify the routine to step though all the lines and check for one starting with Company:

    Now Call fGetSection with

    fGetSection([Contents],CHr(13) & Chr(10),,"Company:")

    Public Function fGetSection(strIn As Variant, _
                               Optional strDelim As String = ",", _
                               Optional IntSection As Integer = 1, _
                               Optional strContains As String)
    'If strContains has a value, then intSection is ignored
    'If strContains has no value, then intsection is used
    Dim sArray As Variant
    Dim i As Long

       If Len(strIn & vbNullString) = 0 Then
          fGetSection = strIn

       Else

          sArray = Split(strIn, strDelim)
          If Len(strContains) > 0 Then
             For i = LBound(sArray) To UBound(sArray)
                If sArray(i) Like "*" & strContains & "*" Then
                   fGetSection = sArray(i)
                   Exit For
                End If
             Next i
          Else
             'adjust in case array is not zero-based
             IntSection = IntSection - 1 + LBound(sArray)
      
             If IntSection < LBound(sArray) Then
                fGetSection = Null
      
             ElseIf UBound(sArray) < IntSection Then
       '      Handle asking for a section that doesn't exist
                fGetSection = Null
             Else
                fGetSection = sArray(IntSection - LBound(sArray))
             End If
          End If
       End If
    End Function

     


    John Spencer Access MVP 2002-2005, 2007-2011 The Hilltop Institute University of Maryland Baltimore County
    Thursday, May 19, 2011 2:33 PM
  • Thank you, for providing code, I will check it.
    Thursday, May 19, 2011 3:36 PM
  • Thanks again John for the advice, but I was never comfortable with arrays.

    You did help by making me think of consistencies in the data “assumptions”.

    I decided to go with something I call string math, since “Company Code: ” always follows “Company: ”

    Below is the SQL that worked for me.

     

    SELECT IIf(InStr([Contents],"Company:"),Mid$([Contents],(InStr([Contents],"Company:")+9),(InStr([Contents],"Company Code:")-2)-(InStr([Contents],"Company:")+9)),"N/A") AS Company

    FROM tblRequests;

     

    • Marked as answer by h2fcell Thursday, May 19, 2011 5:49 PM
    Thursday, May 19, 2011 5:49 PM