Parsing text into fields

[Sharon]

Below is an example of text in a field [Cite] that I want to parse into
separate fields:

Abaza and Atassi, "Effects of Amino Acid Substitutions Outside an Antigenic
Site on Protein Binding to Monoclonal Antibodies of Predetermined Specificity
Obtained by Peptide Immunization: Demonstration with Region 94-100
(Antigenic Site 3) of Myoglobin", J. Protein Chemistry, 11(5):433-444, 1992.

The separate fields are:
Author (Abaza and Atassi), Title (Effects of Amino Acid Substitutions . .
..), Journal (J. Protein Chemistry), Cite (11(5):433-444, 1992).

Using this Code in the criteria field in a query:

Title: Right$([Cite],Len([Cite])- InStr(1,[Cite],",")-1)


Returned this:
"Effects of Amino Acid Substitutions Outside an Antigenic Site on Protein
Binding to Monoclonal Antibodies of Predetermined Specificity Obtained by
Peptide Immunization: Demonstration with Region 94-100 (Antigenic Site 3) of
Myoglobin", J. Protein Chemistry, 11(5):433-444, 1992.

At least I got something out of reading all of the other threads. Would
somebody please explain to me exactly what this code means:

For example:

Title: Right$([Cite],Len([Cite])- InStr(1,[Cite],",")-1)

This means:

Return in the field "Title": (Starting from the Right?) of the beginning of
the field ([Cite]), returns the value of the number of characters in the
string [Cite] minus . . . .

and from there it goes downhill. I am really trying to understand what this
actually means so that I can apply in different circumstances without having
to beg for help each time.

Also, where would I insert the actual code in the query window? I put this
on the criteria line, but when I tried to use actual code with dim
statements, etc. I am not sure where to insert it.

Desperately in need of help. Thanks.

 

[John Vinson]

At least I got something out of reading all of the other threads. Would
somebody please explain to me exactly what this code means:

For example:

Title: Right$([Cite],Len([Cite])- InStr(1,[Cite],",")-1)

Take it from the inside out. The InStr() function takes three
arguments (actually the first is optional) and finds the position of a
substring in a string; in this case it finds the first (the 1)
occuance in the string in [Cite] of the text string ",". This will
find the first comma in the citation. Note that if there is a comma in
the title of the article, it will find THAT comma - the wrong one!!

The Len() function returns the length of the string in Cite.

So if [Cite] is 165 bytes long, and the first comma occurs at byte
105, you'll call the Right() function: Right([Cite], 60). This will
return the rightmost 60 characters of the string in Cite.

You *can* get help from Access. Open the VBA editor; put the cursor
onto the word Right (or InStr, or Len); press the F1 key. You'll get
an explanation of the function, how to call it, and what it does.

Also, where would I insert the actual code in the query window? I put this
on the criteria line, but when I tried to use actual code with dim
statements, etc. I am not sure where to insert it.

If you're using this as a calculated field, put the expression into a
vacant Field cell in the top row of the query grid.

Based on your data... I'd REALLY suggest that you invest in a copy of
EndNote or one of the other commercial scientific-reference citation
programs. Building the functionality you need into Access will be a
*huge* and difficult job; for a few bucks you can get software that's
had many person-years of optimization that will do far more.

John W. Vinson[MVP]

 

[Sharon]

So, the expression: Left$([Cite]), Len([Cite])-InSt(1,[Cite], ",")-1) should
return the author? Isn't this the 1st occurrence in the String in [Cite] of
the text string "," and shouldn't return the leftmost characters of the
string in [Cite].

BTW - I will look into and appreciate your suggestion of the EndNote
citation program. But now that I have started on this road, I would like to
understand the concept for future use. *lol*
--
S

At least I got something out of reading all of the other threads. Would
somebody please explain to me exactly what this code means:

For example:

Title: Right$([Cite],Len([Cite])- InStr(1,[Cite],",")-1)

Take it from the inside out. The InStr() function takes three
arguments (actually the first is optional) and finds the position of a
substring in a string; in this case it finds the first (the 1)
occuance in the string in [Cite] of the text string ",". This will
find the first comma in the citation. Note that if there is a comma in
the title of the article, it will find THAT comma - the wrong one!!

The Len() function returns the length of the string in Cite.

So if [Cite] is 165 bytes long, and the first comma occurs at byte
105, you'll call the Right() function: Right([Cite], 60). This will
return the rightmost 60 characters of the string in Cite.

You *can* get help from Access. Open the VBA editor; put the cursor
onto the word Right (or InStr, or Len); press the F1 key. You'll get
an explanation of the function, how to call it, and what it does.

Also, where would I insert the actual code in the query window? I put this
on the criteria line, but when I tried to use actual code with dim
statements, etc. I am not sure where to insert it.

If you're using this as a calculated field, put the expression into a
vacant Field cell in the top row of the query grid.

Based on your data... I'd REALLY suggest that you invest in a copy of
EndNote or one of the other commercial scientific-reference citation
programs. Building the functionality you need into Access will be a
*huge* and difficult job; for a few bucks you can get software that's
had many person-years of optimization that will do far more.

John W. Vinson[MVP]

 

[John Vinson]

So, the expression: Left$([Cite]), Len([Cite])-InSt(1,[Cite], ",")-1) should
return the author? Isn't this the 1st occurrence in the String in [Cite] of
the text string "," and shouldn't return the leftmost characters of the
string in [Cite].

Not quite. Play computer for a bit... get VERY tedious and
literalminded!

Write the value of [Cite] out on paper, and count characters. What is
the position of the first comma? Just work it through as if you were
the computer doing it.

Then to check your work, type Ctrl-G to open the VBA Immediate window
and type

?Left(1, "<your citation string here>", InStr(1, "<your citation
string here again>", ",") -1)

and see what you get.

John W. Vinson[MVP]

 

[Sharon]

Perfect! Exactly what I wanted, someone to *nudge* me in the right direction
so I could figure it out on my own so the next time I will know which way to
go. Thanks.
--
S

So, the expression: Left$([Cite]), Len([Cite])-InSt(1,[Cite], ",")-1) should
return the author? Isn't this the 1st occurrence in the String in [Cite] of
the text string "," and shouldn't return the leftmost characters of the
string in [Cite].

Not quite. Play computer for a bit... get VERY tedious and
literalminded!

Write the value of [Cite] out on paper, and count characters. What is
the position of the first comma? Just work it through as if you were
the computer doing it.

Then to check your work, type Ctrl-G to open the VBA Immediate window
and type

?Left(1, "<your citation string here>", InStr(1, "<your citation
string here again>", ",") -1)

and see what you get.

John W. Vinson[MVP]

 

[Sharon]

Thanks, John! I really appreciate your *nudging* me in the right direction.
I love this stuff, but sometimes have trouble "seeing the big picture."
Thanks.
--
S

So, the expression: Left$([Cite]), Len([Cite])-InSt(1,[Cite], ",")-1) should
return the author? Isn't this the 1st occurrence in the String in [Cite] of
the text string "," and shouldn't return the leftmost characters of the
string in [Cite].

Not quite. Play computer for a bit... get VERY tedious and
literalminded!

Write the value of [Cite] out on paper, and count characters. What is
the position of the first comma? Just work it through as if you were
the computer doing it.

Then to check your work, type Ctrl-G to open the VBA Immediate window
and type

?Left(1, "<your citation string here>", InStr(1, "<your citation
string here again>", ",") -1)

and see what you get.

John W. Vinson[MVP]