Searching from right - code mod please!

  • Thread starter Thread starter andy.bayley
  • Start date Start date
A

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


KARL DEWEY said:
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.

[email protected] said:
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.
Click to expand...
Click to expand...
 
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)
Click to expand...
InstrRev starts looking from right for the character and returns the
position from the left.
Click to expand...
"(e-mail address removed)" wrote:
Click to expand...
Click to expand...

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

[email protected] said:
Hi
Click to expand...
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!
Click to expand...
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
Click to expand...
Thanks!
Andy.
Click to expand...
Click to expand...

Hi

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

Andy.
 
Without the code you can do it this way --
   Mid([YourField], InstrRev([YourField], "/")+1)
Click to expand...
InstrRev starts looking from right for the character and returns the
position from the left.
Click to expand...

Hi

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

Andy.
Click to expand...

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.
 
Did you try it?

[email protected] said:
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.

[email protected] said:
Hi
Click to expand...
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!
Click to expand...
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
Click to expand...
Thanks!
Andy.
Click to expand...
Click to expand...

Hi

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

Andy.
Click to expand...
 
Back
Top