Left() Right()

[116]

Using either of the 2, can I search upto a Carriage Return or Line Feed? I
have some text that veries in length, with additional text afterwards. I am
looking to keep the beginning text and yet replace the later.

Thanks
David

 

[Jerry Whittle]

You can with the help of the InStr() function. Below finds the comma "," in
something like "Belleville, Illinois" and says where it is in the string.
Then you use the Left function to grab everything before the comma. The -1
does the before part.

Cities: Left([CityState],InStr(1,[CityState],",")-1)

This will extract the State after the comma plus deal with any spaces.
Notice that it uses the Mid function and not Right().

States: Trim(Mid([CityState],InStr(1,[CityState],",")+1))

 

[John W. Vinson]

Using either of the 2, can I search upto a Carriage Return or Line Feed? I
have some text that veries in length, with additional text afterwards. I am
looking to keep the beginning text and yet replace the later.

Thanks
David

You can use the expression

InStr(stringvariable, InStr(stringvariable, Chr(13) & Chr(10))

to find the position of the carriage-return - line-feed pair. In VBA code (but
not in a Query) you can instead use vbCrLf.

To extract the portion of the text up to the new line, you can use

Left(stringvariable, InStr(stringvariable, InStr(stringvariable, Chr(13) &
Chr(10)) - 1)

To extract the portion after the new line,

Mid(stringvariable, InStr(stringvariable, InStr(stringvariable, Chr(13) &
Chr(10)) + 2)

 

[Douglas J. Steele]

No, it would not look like

Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1)

First of all, the constants are vbCr and vbLf: no quotes around them. With
the quotes, you're looking for the literal strings.

Second, you can't Or together character constants like that. Even if you
could, Or'ing them together in the InStr function like that makes no sense
at all.

 

[David W. Fenton]

To extract the portion of the text up to the new line, you can use

Left(stringvariable, InStr(stringvariable, InStr(stringvariable,
Chr(13) & Chr(10)) - 1)

To extract the portion after the new line,

Mid(stringvariable, InStr(stringvariable, InStr(stringvariable,
Chr(13) & Chr(10)) + 2)

Or, as someone's code posted in one of the newsgroups recently
taught me:

Split(stringvariable, vbCrLf)(0) '<-before the CfLf
Split(stringvariable, vbCrLf)(1) '<-after the CfLf

This is because Split() returns an array of items separated by the
second argument you provide to it. Item 0 of the array is the first
one (before the CrLf) and Item 1 of the array is the one after.

I've been using my own SubStr() function for a long time with a
Boolean argument for returning before or after the find string, and
this makes that completely obsolete.

 

[John W. Vinson]

Or, as someone's code posted in one of the newsgroups recently
taught me:

Split(stringvariable, vbCrLf)(0) '<-before the CfLf
Split(stringvariable, vbCrLf)(1) '<-after the CfLf

Cute! Didn't actually realize that you could use the subscript in a SQL query.
I know it would work in VBA of course.

This is because Split() returns an array of items separated by the
second argument you provide to it. Item 0 of the array is the first
one (before the CrLf) and Item 1 of the array is the one after.

I've been using my own SubStr() function for a long time with a
Boolean argument for returning before or after the find string, and
this makes that completely obsolete.

That would indeed be useful.

 

[Douglas J. Steele]

Cute! Didn't actually realize that you could use the subscript in a SQL
query.
I know it would work in VBA of course.

Of course, you'll still have the restriction that you can't use vbCrLf in a
query, but it should work fine with Chr$(13) & Chr$(10).

 

[John Spencer]

What version does this work in? I've tried it in 2003 and I get an error.

SELECT Methods.Method
, Split([Method]," ")(0) As Returned
FROM Methods;

Error: Invalid use of '.', '!', or '()' in expression 'Split([Method]," ")(0)'

I've always had to use a custom vba function that uses the split function to
get the result.
fGetSection([Method]," ",0)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

My apologies. I was sure I had used it successfully, but you're right: I'm
getting the same error in Access 2003 (the only version I tested)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What version does this work in? I've tried it in 2003 and I get an error.

SELECT Methods.Method
, Split([Method]," ")(0) As Returned
FROM Methods;

Error: Invalid use of '.', '!', or '()' in expression 'Split([Method],"
")(0)'

I've always had to use a custom vba function that uses the split function
to get the result.
fGetSection([Method]," ",0)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Of course, you'll still have the restriction that you can't use vbCrLf in
a query, but it should work fine with Chr$(13) & Chr$(10).

 

[David W. Fenton]

Cute! Didn't actually realize that you could use the subscript in
a SQL query. I know it would work in VBA of course.

Another trick I recently ran onto that uses index numbers of a
default collection was:

strSQL = "SELECT Count(*) FROM MyTable"
lngRecordCount = CurrentDB.OpenRecordset(strSQL)(0)

(of course, for maximum ease of use, you wouldn't use the variable
for the SQL string, but I did that so that the line wouldn't wrap in
the middle of the SQL statement)

Another convenient use:

lngID = dbLocal.OpenRecordset("SELECT @@IDENTITY")(0)

I've starting doing both of these in the Immediate window, in fact
-- very convenient.

 

[David W. Fenton]

Of course, you'll still have the restriction that you can't use
vbCrLf in a query, but it should work fine with Chr$(13) &
Chr$(10).

FWIW, I missed the context of it being a query. If you don't want to
write a UDF (or, in this case, two UDFs), the original solution is
the one that works.

 

[Abdihafid]

Hej jag kan inte engelska... kan du svenska....?

You can use the expression

InStr(stringvariable, InStr(stringvariable, Chr(13) & Chr(10))

to find the position of the carriage-return - line-feed pair. In VBA code
(but
not in a Query) you can instead use vbCrLf.

To extract the portion of the text up to the new line, you can use

Left(stringvariable, InStr(stringvariable, InStr(stringvariable, Chr(13) &
Chr(10)) - 1)

To extract the portion after the new line,

Mid(stringvariable, InStr(stringvariable, InStr(stringvariable, Chr(13) &
Chr(10)) + 2)