URGENT - Need InStrRev / StrReverse Functions for Access 97

[Jado]

Hi

I urgently need either an InStrRev Function replacement or StrReverse
Function replacement.

I'm using Access 97

There is a peace of code i want to use.

'Code
'FromLine is a string taken from a text file

LastSpacePosition = InStrRev(FromLine, " ", -1, vbTextCompare)
'-------------------------------------------------------------------

this gives an error 'Sub or Function not defined'

i've read that InStrRev is only availible in Access 2000, but i've also read
that StrReverse is the fuction used to achieve the same result prior to
InStrRev.

i've tried
LastSpacePosition = InStr(StrReverse(FromLine), " ") + 1

but i get the same error.


i don't really mind how i achieve LastSpacePosition, as long as it works.

any help would be great.

thanks

Jado

 

[John Spencer (MVP)]

Here is an InStrRev Function written and posted by John Viescas. It works fine
in Access 97.

Public Function InStrRev(strCheck As Variant, _
strMatch As Variant, _
Optional intStart As Integer = -1, _
Optional intCompare As Integer = 2) As Variant
'-----------------------------------------------------------
' Inputs: String to check,
' match string,
' optional starting position (default = -1),
' optional string compare value (default vbDatabaseCompare)
' Outputs: Position of match string, starting from the end
' Created By: JLV 11/15/01
' Last Revised: JLV 11/15/01
' ** Duplicates the functionality of the VB 6 INSTRREV function.
'-----------------------------------------------------------
Dim intS As Integer, intR As Integer
Dim intI As Integer, intLenC As Integer, intLenM As Integer

' Do some initial checks
If (intCompare < 0) Or (intCompare > 2) Then
Err.Raise 5
Exit Function
End If
If IsNull(strCheck) Then
InStrRev = Null
Exit Function
End If
If VarType(strCheck) <> vbString Then
Err.Raise 5
Exit Function
End If
If IsNull(strMatch) Then
InStrRev = Null
Exit Function
End If
If VarType(strMatch) <> vbString Then
Err.Raise 5
Exit Function
End If
If Len(strCheck) = 0 Then
InStrRev = 0
Exit Function
End If
If Len(strMatch) = 0 Then
InStrRev = intStart
Exit Function
End If
If intStart > Len(strMatch) Then
InStrRev = 0
Exit Function
End If
If Len(strMatch) > Len(strCheck) Then
InStrRev = 0
Exit Function
End If

' OK, have some work to do!
intS = intStart
intLenC = Len(strCheck)
intLenM = Len(strMatch)
If intS = -1 Then intS = intLenC
' Set default not found
InStrRev = 0
' Now loop to see if we can find it
For intI = intS To 1 Step -1
intR = InStr(intI, strCheck, strMatch, intCompare)
If intR <> 0 Then
InStrRev = intR
Exit For
End If
Next intI

End Function

John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/

 

[Jado]

Great!

Thanks John

Here is an InStrRev Function written and posted by John Viescas. It works fine
in Access 97.

Public Function InStrRev(strCheck As Variant, _
strMatch As Variant, _
Optional intStart As Integer = -1, _
Optional intCompare As Integer = 2) As Variant
'-----------------------------------------------------------
' Inputs: String to check,
' match string,
' optional starting position (default = -1),
' optional string compare value (default vbDatabaseCompare)
' Outputs: Position of match string, starting from the end
' Created By: JLV 11/15/01
' Last Revised: JLV 11/15/01
' ** Duplicates the functionality of the VB 6 INSTRREV function.
'-----------------------------------------------------------
Dim intS As Integer, intR As Integer
Dim intI As Integer, intLenC As Integer, intLenM As Integer

' Do some initial checks
If (intCompare < 0) Or (intCompare > 2) Then
Err.Raise 5
Exit Function
End If
If IsNull(strCheck) Then
InStrRev = Null
Exit Function
End If
If VarType(strCheck) <> vbString Then
Err.Raise 5
Exit Function
End If
If IsNull(strMatch) Then
InStrRev = Null
Exit Function
End If
If VarType(strMatch) <> vbString Then
Err.Raise 5
Exit Function
End If
If Len(strCheck) = 0 Then
InStrRev = 0
Exit Function
End If
If Len(strMatch) = 0 Then
InStrRev = intStart
Exit Function
End If
If intStart > Len(strMatch) Then
InStrRev = 0
Exit Function
End If
If Len(strMatch) > Len(strCheck) Then
InStrRev = 0
Exit Function
End If

' OK, have some work to do!
intS = intStart
intLenC = Len(strCheck)
intLenM = Len(strMatch)
If intS = -1 Then intS = intLenC
' Set default not found
InStrRev = 0
' Now loop to see if we can find it
For intI = intS To 1 Step -1
intR = InStr(intI, strCheck, strMatch, intCompare)
If intR <> 0 Then
InStrRev = intR
Exit For
End If
Next intI

End Function

John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/