Text manipulation

[gzaepfel]

I have a database that includes legal descriptions of properties.
Included in this description is the lot number.



An example: Cimarron Phase 2 LT 9



I'd like to capture LT 9 from the above phrase. Most records have the
LT <number> at the end, but not always, so using the RIGHT function
tends to cut off some of the information I want.



In english-speak I'd like to find where LT is located, capture those two
characters and then the next 6 characters after that.



Thanks much for any help!



Gary

 

[Fredg]

I assume there is always a space after the LT.

SELECT YourTable.YourField, Mid([YourField],InStr([YourField],"LT ")) AS Exp
FROM YourTable
WHERE (((YourTable.YourField) Is Not Null) AND (InStr([YourField],"LT
")>0));

Will return everything after (and including) the "LT ".

 

[gzaepfel]

Fred:

I'm still getting errors. I'm using the expression builder in Access.
I've copied exactly what you've sent to me and replaced all of the
YourTables and YourFields to match what I have (including making sure
that case matches the table and field). I also had to put a ( in front
of SELECT and I had to remove the ;. The error message now says: "You
have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise
the SELECT statement of the subquery to request only one field." This
sounds odd, because I have 140,000+ records that I want to search.



Thanks for helping,

Gary





Originally posted by Fredg

Change the code to look for "LT" not "LT "
SELECT YourTable.YourField,
Mid([YourField],InStr([YourField],"LT")) AS Exp

FROM YourTable

WHERE YourTable.YourField Is Not Null AND InStr([YourField],"LT")>0);
I've removed all the parenthesis that Access added to the SQL.

Perhaps you included some of the extra > symbols that email
adds to line

breaks.

There is only one > symbol in this code.

InStr([YourField],"LT")>0);

Remove any others that appear if you copy this.
Copy and Paste it into the SQL view of your query.

Change all the table and field names to the actual names used in the

When you save the query, Access will put the parenthesis back.

The above syntax worked fine for me.



Please reply only to this newsgroup.

I do not reply to personal e-mail.

news:[email protected]"]bforums.com[/url]...

Thanks for helping me, Fred. I forgot to say I'm using Access 2000.

I'm getting an error message that states: "The syntax of the subquery in
this expression is incorrect. Check the subquery's syntax and enclose

the subquery in parentheses." I'm a novice with Access, so pardon my
naivete with this stuff. Occasionally their is not a space after "LT."
-Gary
Originally posted by Fredg

I assume there is always a space after the LT.

SELECT YourTable.YourField,

Mid([YourField],InStr([YourField],"LT

")) AS Exp
FROM YourTable
WHERE (((YourTable.YourField) Is Not Null) AND

(InStr([YourField],"LT

")>0));

Will return everything after (and including) the "LT ".

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


[email protected][/url]"]news:3262395.1061334327@d-

news:3262395.1061334327@d-

bforums.com[/url]...
I have a database that includes legal descriptions of
properties.
Included in this description is the lot number.
An example: Cimarron Phase 2 LT 9
I'd like to capture LT 9 from the above phrase. Most

records

have the
LT <number> at the end, but not always, so using the

RIGHT

function
tends to cut off some of the information I want.
In english-speak I'd like to find where LT is located,

capture

those two
characters and then the next 6 characters after that.
Thanks much for any help!
Gary

http://dbforums.com/http://dbforums.com"]http://dbfor- ums.com/http://dbforums.com[/URL]

--

Posted via

http://dbforums.com/http://dbforums.com

 

[Fredg]

Gary,
I see that in removing all the parenthesis that Access will add
to the SQL, I inadvertently left one in. Sorry.
However, that should give a 'You have an extra parentheses ..." error
not the kind of error you mentioned.

Here is the changed code:

SELECT YourTable.YourField, Mid([YourField],InStr([YourField],"LT")) AS Exp
FROM YourTable
WHERE YourTable.YourField Is Not Null AND InStr([YourField],"LT")>0;

There is no subquery.
You do not need a parenthesis in front of "Select".
You do need the ; at the very end.
It does not matter if you capitalize or not, though it is certainly
easier to read if you do capitalize field and table names.

Let's start again.
Click on the Queries tab of the main database folder.
Click New.
Select Design View from the new Query dialog box.
Add your table to the query when the Show Table dialog appears.
Click on the down-arrow on the Query View tool button
(usually at the extreme left of the tool bar),
and select SQL View.
or... just click View + SQL on the Menu bar.

A code window will appear with something like

Select
From YourTable;

displayed.
Notice that there is no parenthesis in front of the word Select
and that there is a ; at the end.

You may delete all of that SQL code.
Then paste the code I gave you above into the window.
Change all the table and field names to what you are using.
When done, click on the bang tool button (!).

The query should run and give you a column of records in which
the field contains "LT" followed by numbers.
It will not return any records if the YourField is Null or
if it does not contain any records with "LT" in it.

When you save the query and then re-run it,
Access will add those extra parenthesis.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

Fred:

I'm still getting errors. I'm using the expression builder in Access.
I've copied exactly what you've sent to me and replaced all of the
YourTables and YourFields to match what I have (including making sure
that case matches the table and field). I also had to put a ( in front
of SELECT and I had to remove the ;. The error message now says: "You
have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise
the SELECT statement of the subquery to request only one field." This
sounds odd, because I have 140,000+ records that I want to search.



Thanks for helping,

Gary





Originally posted by Fredg

Change the code to look for "LT" not "LT "
SELECT YourTable.YourField,
Mid([YourField],InStr([YourField],"LT")) AS Exp

FROM YourTable

WHERE YourTable.YourField Is Not Null AND InStr([YourField],"LT")>0);
I've removed all the parenthesis that Access added to the SQL.

Perhaps you included some of the extra > symbols that email
adds to line

breaks.

There is only one > symbol in this code.

InStr([YourField],"LT")>0);

Remove any others that appear if you copy this.
Copy and Paste it into the SQL view of your query.

Change all the table and field names to the actual names used in the

When you save the query, Access will put the parenthesis back.

The above syntax worked fine for me.

Fred

 

[gzaepfel]

Fred:

Everything worked like a charm. Part of the problem was that I wasn't
using the SQL view window. I can tell you that when it worked properly
the first time, I let out a big "Yes!".

Thanks much for your dilligence and patience with me. I've greatly
appreciated your help.



-Gary







Originally posted by Fredg

Gary,

I see that in removing all the parenthesis that Access will add

to the SQL, I inadvertently left one in. Sorry.

However, that should give a 'You have an extra parentheses ..." error

not the kind of error you mentioned.
Here is the changed code:
SELECT YourTable.YourField,
Mid([YourField],InStr([YourField],"LT")) AS Exp

FROM YourTable

WHERE YourTable.YourField Is Not Null AND InStr([YourField],"LT")>0;
There is no subquery.

You do not need a parenthesis in front of "Select".

You do need the ; at the very end.

It does not matter if you capitalize or not, though it is certainly

easier to read if you do capitalize field and table names.

Let's start again.

Click on the Queries tab of the main database folder.

Click New.

Select Design View from the new Query dialog box.

Add your table to the query when the Show Table dialog appears.

Click on the down-arrow on the Query View tool button

(usually at the extreme left of the tool bar),

and select SQL View.

or... just click View + SQL on the Menu bar.

A code window will appear with something like


From YourTable;


Notice that there is no parenthesis in front of the word Select

and that there is a ; at the end.

You may delete all of that SQL code.

Then paste the code I gave you above into the window.

Change all the table and field names to what you are using.

When done, click on the bang tool button (!).

The query should run and give you a column of records in which

the field contains "LT" followed by numbers.

It will not return any records if the YourField is Null or

if it does not contain any records with "LT" in it.

When you save the query and then re-run it,

Access will add those extra parenthesis.



Please reply only to this newsgroup.

I do not reply to personal e-mail.

news:[email protected]"]bforums.com[/url]...

Fred:
I'm still getting errors. I'm using the expression builder in Access.

I've copied exactly what you've sent to me and replaced all of the

YourTables and YourFields to match what I have (including making sure

that case matches the table and field). I also had to put a ( in front
of SELECT and I had to remove the ;. The error message now says: "You
have written a subquery that can return more than one field without

using the EXISTS reserved word in the main query's FROM clause. Revise

the SELECT statement of the subquery to request only one field." This

sounds odd, because I have 140,000+ records that I want to search.

Thanks for helping,
Gary
Originally posted by Fredg

Change the code to look for "LT" not "LT "

SELECT YourTable.YourField,
Mid([YourField],InStr([YourField],"LT")) AS Exp
FROM YourTable
WHERE YourTable.YourField Is Not Null AND

InStr([YourField],"LT")>0);

I've removed all the parenthesis that Access added to the

SQL.

Perhaps you included some of the extra > symbols that

email

adds to line
breaks.

There is only one > symbol in this code.
InStr([YourField],"LT")>0);

Remove any others that appear if you copy this.

Copy and Paste it into the SQL view of your query.
Change all the table and field names to the actual names used

in the

database.
When you save the query, Access will put the parenthesis

back.

The above syntax worked fine for me.
--

Fred