# 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

• 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 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.

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 Thursday, May 19, 2011 5:49 PM
Thursday, May 19, 2011 5:49 PM