Exporting field containg a carriage return

[Doug]

Help, Help, Help!!

I have to export info into a CVS file (comma delimited) to import into
another application and have one field that is a real problem.

In a company directory, I have combined the company name and street address
into a single field though there are carriage returns in the text to show it
as multiple lines in the field text box. I need to export only the second
and/or third lines from this field.

How can I write an equation in a query to pull only the second and/or third
line ? The text in the first line varies a lot from company to company so
there is a varying number of text characters to filter out but there is
always a carriage return. Any way to utilize this ?

Any help is appreciated.

Thanks,

Doug

 

[Joe Fallon]

Sure.
Write a query and search for the CR using Instr function.
Then use the Mid function + Instr to isolate everything after the first CR.

The actual character for CR may be Chr(13) or Chr(13) & Chr(10).

 

[Doug]

Thank you very much.

Now, not to sound like an Idiot, but I am unable to find an explanation of
the syntax for the InStr function. In the expression builder, I get: InStr
(«start», «stringexpr1», «stringexpr2», «compare») . I all my searches, I
found only the VB syntax which is: InStr(<string being searched>, <string
being searched for>)

Here is an example of the text in my field (Field name: [Address] ):

Roberts Company, Inc.
123 Speedway Drive
Suite B

At a minimum, I want to pull the second & third lines out and potentially
pull them out individually though not all entries will have a third line. A
very few may have a fourth but that is pretty limited.

If you could provide me with a sample expression, I will be most thankful.

Again, your help is greatly appreciated.

Doug

 

[Ted]

Following is the help description of the four arguments:

start Optional. Numeric expression that sets the starting
position for each search. If omitted, search begins at
the first character position. If start contains Null, an
error occurs. The start argument is required if compare
is specified.

string1 Required. String expression being searched.

string2 Required. String expression sought.

compare Optional. Specifies the type of string
comparison. If compare is Null, an error occurs. If
compare is omitted, the Option Compare setting determines
the type of comparison. Specify a valid LCID (LocaleID)
to use locale-specific rules in the comparison.

Following are the values that can be used for the
optional compare statement (you would probably want to
use vbTextCompare, or 1 which is the numeric equivalent):

The compare argument settings are:

Constant Value Description
vbUseCompareOption -1 Performs a comparison using the
setting of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a
comparison based on information in your database.

Following describes what the function will return in
different cases:

string1 is zero-length 0
string1 is Null Null
string2 is zero-length start
string2 is Null Null
string2 is not found 0
string2 is found within string1 Position at which match
is found
start > string2 0

Following are some examples from MS Access Help:

Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".

' A textual comparison starting at position 4. Returns 6.
MyPos = Instr(4, SearchString, SearchChar, 1)

' A binary comparison starting at position 1. Returns 9.
MyPos = Instr(1, SearchString, SearchChar, 0)

' Comparison is binary by default (last argument is
omitted).
MyPos = Instr(SearchString, SearchChar) ' Returns 9.

MyPos = Instr(1, SearchString, "W") ' Returns 0.

 

[Doug]

I think I got it working.

Many thanks to both who answewred this.

Doug