InStrRev Not Giving Correct Results

[Vaulcul]

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.

In order to do this I do the following in unbound text boxes on the form:
 
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])
I find the number of characters that happen from the left up to the "--": LPageVar = InStr([pagesset],"--")
I find the number of characters that happen from the right up to the "--": RPageVar = InStrRev([pagesset],"--")
I find the actual page number on the left side of the "--":  LVal = Left([pagesset],[LPageVar]-1)
I find the actual page number on the right side of the "--":  RVal = Right([pagesset],[RPageVar]-1)
I calculate the number of pages that appear:  Pgcnt = RVal - LVal
 

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.

[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.

[Vaulcul]

Ah... I see what I was doing wrong.
Thanks Rabbit.

[WannaKatana2]

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