If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Parsing text into fields
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 a 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. -- S |
#2
|
|||
|
|||
Parsing text into fields
On Mon, 9 Oct 2006 10:15:01 -0700, Sharon
wrote: 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] |
#3
|
|||
|
|||
Parsing text into fields
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 "John Vinson" wrote: On Mon, 9 Oct 2006 10:15:01 -0700, Sharon wrote: 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] |
#4
|
|||
|
|||
Parsing text into fields
On Mon, 9 Oct 2006 12:04:02 -0700, Sharon
wrote: 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] |
#5
|
|||
|
|||
Parsing text into fields
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 "John Vinson" wrote: On Mon, 9 Oct 2006 12:04:02 -0700, Sharon wrote: 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] |
#6
|
|||
|
|||
Parsing text into fields
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 "John Vinson" wrote: On Mon, 9 Oct 2006 12:04:02 -0700, Sharon wrote: 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] |
Thread Tools | |
Display Modes | |
|
|