470,593 Members | 2,470 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

InStrRev Not Giving Correct Results

20
Hello,

I have an Access database where I'm importing book/journal publication data from JabRef in a CSV format.
When I import the data to Access one of the odd things that happens is that the page numbers are given two hyphens in between them, so the data in the "pages" column in Access would look something like "200--213"

I need to be able to count the number of pages that are referenced.

Expand|Select|Wrap|Line Numbers
  1. In order to do this I do the following in unbound text boxes on the form:
  2.  
  3. I find the length of the string in the "pages" column (have to rename the pages variable as it's a reserved name to pagesset):  PLen = Len([pagesset])
  4. I find the number of characters that happen from the left up to the "--": LPageVar = InStr([pagesset],"--")
  5. I find the number of characters that happen from the right up to the "--": RPageVar = InStrRev([pagesset],"--")
  6. I find the actual page number on the left side of the "--":  LVal = Left([pagesset],[LPageVar]-1)
  7. I find the actual page number on the right side of the "--":  RVal = Right([pagesset],[RPageVar]-1)
  8. I calculate the number of pages that appear:  Pgcnt = RVal - LVal
  9.  
Everything seems to work... except when the "InStrRev" hits an item that increments the number by the 10 or 100 spot, like this: "7--11", "7--23", or "92--101" as opposed to this: "102--123" or "103--110" (which causes no issues). When it hits these shorter pagethe RPageVar is too low by 1.

For each of these items on the right, RVal seems to drop the first character... so for "7--11" last page is reported as 1 or for "7--23" it would report the last page as 3 or "92--101" the last page is reported as 01. This causes these particular page counts to be negative.

Does anyone have an idea as to why I'm getting this behavior?

Thank you in advance.
Jan 17 '14 #1

✓ answered by Rabbit

Given your data, InStr and InStrRev returns the same number. You misunderstand what InStrRev does, it does the exact same thing InStr does except it finds the last occurrence of the given string rather than the first occurrence.

In your case, you only have one occurrence of the double dash, that means both functions are going to return the same value.

You just need the index of the first one anyways. You want to take the right x characters, where x is the length minus one minus the index of the occurrence of the double dash.

3 7992
Rabbit
12,516 Expert Mod 8TB
Given your data, InStr and InStrRev returns the same number. You misunderstand what InStrRev does, it does the exact same thing InStr does except it finds the last occurrence of the given string rather than the first occurrence.

In your case, you only have one occurrence of the double dash, that means both functions are going to return the same value.

You just need the index of the first one anyways. You want to take the right x characters, where x is the length minus one minus the index of the occurrence of the double dash.
Jan 17 '14 #2
Vaulcul
20
Ah... I see what I was doing wrong.
Thanks Rabbit.
Jan 21 '14 #3
WannaKatana2
2 Bit
The Microsoft Access InstrRev function returns the position of the first occurrence of a string in another string, starting from the end of the string.


@Rabbit
Apr 13 '22 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Hunter Hillegas | last post: by
2 posts views Thread by awebguynow | last post: by
45 posts views Thread by laredotornado | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.