Searching from right - code mod please!

[andy.bayley]

Hi

I'm using this bit of code (thanks to Lyle Fairfield) to help me find
the text after the third forward slash in a field.
An example field may be 1/2345/6/78 - and I wish to return 78.
Please could someone let me know hot to amend this code so that if
there is no third forward slash, the function will return a zero?
Currently it returns some other number!

Function RAT(strSearchIn As String, strSearchFor As String, occurences
As Long)
As Integer
Dim aSearchIn() As Byte, intCounter As Integer, bytSearchFor As
Byte
Dim occurence As Long
For occurence = 1 To occurences
aSearchIn = StrConv(strSearchIn, vbFromUnicode)
bytSearchFor = Asc(strSearchFor)
For intCounter = UBound(aSearchIn) To LBound(aSearchIn) Step -1
If aSearchIn(intCounter) = bytSearchFor Then
RAT = intCounter + 1
Exit For
End If
Next intCounter
Next
End Function

Thanks!
Andy.

 

[KARL DEWEY]

Without the code you can do it this way --
Mid([YourField], InstrRev([YourField], "/")+1)

InstrRev starts looking from right for the character and returns the
position from the left.

 

[Douglas J. Steele]

Not sure that helps with the issue of no third forward slash.

I haven't tested the following very thoroughly, but I think it does what's
wanted:

Function RAT(strSearchIn As String, _
strDelimiter As String, _
occurences As Long _
) As Integer

Dim varInput As Variant

varInput = Split(strSearchIn, strDelimiter)
If occurences >= 0 And _
UBound(varInput) >= occurences Then
RAT = varInput(occurences)
End If

End Function

?Rat("1/2345/6/78", "/", 3)
78
?Rat("1/2345/6", "/", 3)
0



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Without the code you can do it this way --
Mid([YourField], InstrRev([YourField], "/")+1)

InstrRev starts looking from right for the character and returns the
position from the left.

Hi

I'm using this bit of code (thanks to Lyle Fairfield) to help me find
the text after the third forward slash in a field.
An example field may be 1/2345/6/78 - and I wish to return 78.
Please could someone let me know hot to amend this code so that if
there is no third forward slash, the function will return a zero?
Currently it returns some other number!

Function RAT(strSearchIn As String, strSearchFor As String, occurences
As Long)
As Integer
Dim aSearchIn() As Byte, intCounter As Integer, bytSearchFor As
Byte
Dim occurence As Long
For occurence = 1 To occurences
aSearchIn = StrConv(strSearchIn, vbFromUnicode)
bytSearchFor = Asc(strSearchFor)
For intCounter = UBound(aSearchIn) To LBound(aSearchIn) Step -1
If aSearchIn(intCounter) = bytSearchFor Then
RAT = intCounter + 1
Exit For
End If
Next intCounter
Next
End Function

Thanks!
Andy.

 

[andy.bayley]

Not sure that helps with the issue of no third forward slash.

I haven't tested the following very thoroughly, but I think it does what's
wanted:

Function RAT(strSearchIn As String, _
    strDelimiter As String, _
    occurences As Long _
) As Integer

Dim varInput As Variant

    varInput = Split(strSearchIn, strDelimiter)
    If occurences >= 0 And _
        UBound(varInput) >= occurences Then
        RAT = varInput(occurences)
    End If

End Function

?Rat("1/2345/6/78", "/", 3)
 78
?Rat("1/2345/6", "/", 3)
 0

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Without the code you can do it this way --
  Mid([YourField], InstrRev([YourField], "/")+1)

InstrRev starts looking from right for the character and returns the
position from the left.

"(e-mail address removed)" wrote:

Hi

Thanks for your reply. I am using Access 2000 (sorry for not
mentioning this) and I get an error. I'm guessing it's due to the
Split function which may not be compatible with my version?

Cheers.
Andy.

 

[andy.bayley]

Without the code you can do it this way --
   Mid([YourField], InstrRev([YourField], "/")+1)

InstrRev starts looking from right for the character and returns the
position from the left.

Hi

I'm using this bit of code (thanks to Lyle Fairfield) to help me find
the text after the third forward slash in a field.
An example field may be 1/2345/6/78 - and I wish to return 78.
Please could someone let me know hot to amend this code so that if
there is no third forward slash, the function will return a zero?
Currently it returns some other number!

Function RAT(strSearchIn As String, strSearchFor As String, occurences
As Long)
As Integer
    Dim aSearchIn() As Byte, intCounter As Integer, bytSearchFor As
Byte
    Dim occurence As Long
    For occurence = 1 To occurences
    aSearchIn = StrConv(strSearchIn, vbFromUnicode)
    bytSearchFor = Asc(strSearchFor)
    For intCounter = UBound(aSearchIn) To LBound(aSearchIn) Step -1
        If aSearchIn(intCounter) = bytSearchFor Then
            RAT = intCounter + 1
            Exit For
        End If
    Next intCounter
    Next
End Function

Thanks!
Andy.

Hi

I don't think my Access version (2000) supports InstrRev - as its not
in the functions list.

Andy.

 

[andy.bayley]

Without the code you can do it this way --
   Mid([YourField], InstrRev([YourField], "/")+1)

InstrRev starts looking from right for the character and returns the
position from the left.

Hi

I don't think my Access version (2000) supports InstrRev - as its not
in the functions list.

Andy.

Hi again!

I've done a work-around by using a criteria of checking if the
difference in length of the field between normal and replacing / with
blanks is three!

Thanks again.

Andy.

 

[KARL DEWEY]

Did you try it?

Without the code you can do it this way --
Mid([YourField], InstrRev([YourField], "/")+1)

InstrRev starts looking from right for the character and returns the
position from the left.

Hi

I'm using this bit of code (thanks to Lyle Fairfield) to help me find
the text after the third forward slash in a field.
An example field may be 1/2345/6/78 - and I wish to return 78.
Please could someone let me know hot to amend this code so that if
there is no third forward slash, the function will return a zero?
Currently it returns some other number!

Function RAT(strSearchIn As String, strSearchFor As String, occurences
As Long)
As Integer
Dim aSearchIn() As Byte, intCounter As Integer, bytSearchFor As
Byte
Dim occurence As Long
For occurence = 1 To occurences
aSearchIn = StrConv(strSearchIn, vbFromUnicode)
bytSearchFor = Asc(strSearchFor)
For intCounter = UBound(aSearchIn) To LBound(aSearchIn) Step -1
If aSearchIn(intCounter) = bytSearchFor Then
RAT = intCounter + 1
Exit For
End If
Next intCounter
Next
End Function

Thanks!
Andy.

Hi

I don't think my Access version (2000) supports InstrRev - as its not
in the functions list.

Andy.