Extracting certain information from a column

[Rita D via AccessMonster.com]

Hello,

I'm trying to extract certain information from a column and I can't seem to
do it. This column is called information and it contains a description for
each row . Within the description there is a number like BO1589. currently, I
have about 4000 rows. Each number will repeat or will change. I'd like to
build a query where I can only get the numbers for all the rows and not the
whole description (Sentence). If I do "*BO*", I get all the rows with "BO" in
it along with the other words. Sometimes the letter changes to ZO, and IO.
Please help.
The Query will look like,
BO1589
ZO2052
IO6589
BO1589
BO1589

Thanks,

Rita

 

[Guest]

Are the numbers in the column always in the same location? ex:BO1589 blah
blah blah, and you only want the BO1589 out of the field?

 

[Rita D via AccessMonster.com]

Are the numbers in the column always in the same location? ex:BO1589 blah
blah blah, and you only want the BO1589 out of the field?

Hello,

[quoted text clipped - 16 lines]

Rita

Thanks for replying.
No, they are not. It will look like something like this;
blahblahblahBO1589blahblah
blahblahZ-81blahblahblahblahblah
blahblahBO1589blahblahblah
blahblahblahZO1589blahblah

I'd like to get this,
BO1589
Z-81
BO1589
ZO1589

I tried doing this Mid(Column_Name,44,8) and I get really close but it leaves
some data out.
Any suggestion.

 

[Gary Walter]

Hi Rita,

I don't think you are going to
get a simple solution.

This is what I might do:

Add a new field to your original
table (say "InfoNum")

Create a new table (say "tblExtract")
with 2 fields:

StartDigits type Text
NumDigits type Long

So, from your example,

StartDigits NumDigits
BO 6
Z- 4
ZO 6


Create a routine that opens a recordset
to tblExtract.

As you read each record in the recordset,
create an update SQL and execute.

{something close to following air code}

With rs
..MoveFirst
Do While Not rs.EOF
strStart = !StartDigits
lngLen = !NumDigits

strSQL = "UPDATE originaltable " _
& "SET [InfoNum]=Mid([Information],InStr([Information],'" _
& strStart & "'," & lngLen & ") " _
& "WHERE [Information] LIKE '*" & strStart & "[0-9][0-9]*';"
CurrentDB.Execute strSQL, dbFailOnError

.MoveNext
Loop
End With

That may be your best hope.

(Of course, try on a *copy* of your db first)

good luck,

gary

Are the numbers in the column always in the same location? ex:BO1589 blah
blah blah, and you only want the BO1589 out of the field?

Hello,

[quoted text clipped - 16 lines]

Rita

Thanks for replying.
No, they are not. It will look like something like this;
blahblahblahBO1589blahblah
blahblahZ-81blahblahblahblahblah
blahblahBO1589blahblahblah
blahblahblahZO1589blahblah

I'd like to get this,
BO1589
Z-81
BO1589
ZO1589

I tried doing this Mid(Column_Name,44,8) and I get really close but it
leaves
some data out.
Any suggestion.

 

[Gary Walter]

Hi Rita,

I don't think you are going to
get a simple solution.

This is what I might do:

Add a new field to your original
table (say "InfoNum")

Create a new table (say "tblExtract")
with 2 fields:

StartDigits type Text
NumDigits type Long

So, from your example,

StartDigits NumDigits
BO 6
Z- 4
ZO 6


Create a routine that opens a recordset
to tblExtract.

As you read each record in the recordset,
create an update SQL and execute.

{something close to following air code}

With rs
..MoveFirst
Do While Not rs.EOF
strStart = !StartDigits
lngLen = !NumDigits

strSQL = "UPDATE originaltable " _
& "SET [InfoNum]=Mid([Information],InStr([Information],'" _
& strStart & "'," & lngLen & ") " _
& "WHERE [Information] LIKE '*" & strStart & "[0-9][0-9]*';"
CurrentDB.Execute strSQL, dbFailOnError

.MoveNext
Loop
End With

That may be your best hope.

(Of course, try on a *copy* of your db first)

good luck,

gary

Are the numbers in the column always in the same location? ex:BO1589 blah
blah blah, and you only want the BO1589 out of the field?

Hello,

[quoted text clipped - 16 lines]

Rita

Thanks for replying.
No, they are not. It will look like something like this;
blahblahblahBO1589blahblah
blahblahZ-81blahblahblahblahblah
blahblahBO1589blahblahblah
blahblahblahZO1589blahblah

I'd like to get this,
BO1589
Z-81
BO1589
ZO1589

I tried doing this Mid(Column_Name,44,8) and I get really close but it
leaves
some data out.
Any suggestion.

 

[Rita D via AccessMonster.com]

Hi Rita,

I don't think you are going to
get a simple solution.

This is what I might do:

Add a new field to your original
table (say "InfoNum")

Create a new table (say "tblExtract")
with 2 fields:

StartDigits type Text
NumDigits type Long

So, from your example,

StartDigits NumDigits
BO 6
Z- 4
ZO 6

Create a routine that opens a recordset
to tblExtract.

As you read each record in the recordset,
create an update SQL and execute.

{something close to following air code}

With rs
.MoveFirst
Do While Not rs.EOF
strStart = !StartDigits
lngLen = !NumDigits

strSQL = "UPDATE originaltable " _
& "SET [InfoNum]=Mid([Information],InStr([Information],'" _
& strStart & "'," & lngLen & ") " _
& "WHERE [Information] LIKE '*" & strStart & "[0-9][0-9]*';"
CurrentDB.Execute strSQL, dbFailOnError

.MoveNext
Loop
End With

That may be your best hope.

(Of course, try on a *copy* of your db first)

good luck,

gary
[quoted text clipped - 22 lines]

some data out.
Any suggestion.

Thanks Gary.
Is there any other way to accomplish this? I'm new to Access and that seems
rather confusing.
Where would I put that code?

Rita

 

[Gary Walter]

Thanks Gary.
Is there any other way to accomplish this? I'm new to Access and that
seems
rather confusing.
Where would I put that code?

Hi Rita,

I'm sorry...I completely forgot all
about the new function that came out
in Access2003:

RemoveBlahblahblah()

{just kidding}

Probably through no fault of your own,
you have a poorly designed data structure.
You have some important, distinct data
embedded in a text field that should have
been stored in its own field.

This has happened to me many times over
my career. For example, at one university,
a coworker was given every week a ~20MB
file of all the web searches performed against
their web site, successes, failures, what they
tried next, etc.

In order to analyze it, this blahblahblah file
had to be parsed out into a properly designed
data structure in Access.

If that is similar to your situation, then there
will be a point where you get this "blahblahblah"
data. Possibly on a form you have a command
button that when clicked goes out and gets this
data?

Or, was this a just one-time thing?


If you are allowing your users to enter data
like this, then please stop right now and buy
a good book on Access data structure, like:

"Database Design for Mere Mortals: A Hands-On Guide to Relational Database
Design, Second Edition"
Michael J. Hernandez


Where to put this code will depend
on you explaining how you get (got)
this blahblahblah data.

Please respond back with how you ended up
with this data.


hang in there....
toughing it out to structure
your data properly will make
everything down the road easier.

gary