Reading a string from right to left

[JVanDyne]

I am string to get read a string value from right to left up to a certain character. I can't seem to get it to work. Does anybody know what i may be doing incorrect?

Here is some of the code:

strTable = InputBox("What table do you want to import the file to?", "Professional File Load")

If strTable = "" Then
MsgBox "The Table name must be enter.", vbInformation, "Table Error"
Exit Function
End If

Set fs = Application.FileSearch
With fs
' Get the folder.
.LookIn = strImportFolder
.FileName = "*.csv"
If .Execute() > 0 Then
For i = 1 To .foundfiles.Count

DoCmd.TransferText acImportDelim, "HistoricalClaimDataProf", strTable, .foundfiles(i)
' If error Then
' module.routine
' Else

strFileLocation = .foundfiles(i)
strFileLocation.ReadingOrder = 2
'This is where my error is occuring
intCharacterValue = InStr(strFileLocation, "\")

strFilename = Right(strFileLocation, intCharacterValue)

sql = "Update " & strTable & " " _
& "SET " & strTable & ".FileName = '" & strFilename & "'" _
& "WHERE (((" & strTable & ".filename) Is Null));"

DoCmd.RunSQL sql
Next i
Else
MsgBox "There were no files found."
End If
End With
strImportResub = foundfiles
End Function

Thanks for any help that you may be able to give.
James

 

[mhartman]

Hello: You would want to use the RIGHT function:
'
Right Function
Returns a Variant (String) containing a specified number of characters from the right side of a string.

Syntax

Right(string, length)

The Right function syntax has these named arguments:

Part Description
string Required. String expression from which the rightmost characters are returned. If string contains Null, Null is returned.
length Required; Variant (Long). Numeric expression indicating how many characters to return. If 0, a zero-length string ("") is returned. If greater than or equal to the number of characters in string, the entire string is returned.



Remarks

To determine the number of characters in string, use the Len function.

Note Use the RightB function with byte data contained in a string. Instead of specifying the number of characters to return, length specifies the number of bytes.

 

[pdx_man]

Hmmm ... too complicated. I use the InStrRev function for this type of stuff.

 

[raskew]

Hi -

You would want to use the RIGHT function:

The code does call the right() function and that's the problem. The location of the backslash has no relation to the characters to its right. To return all text to the right of the backslash, use the Mid() function in conjunction with the Instr() function. Example from the debug (immediate) window:

MyStr = "abc\12345"
x = mid(Mystr,instr(Mystr, "\") + 1)
? x
12345

HTH - Bob

 

[JVanDyne]

Thank you all for your help.

However the string is a files location, so there are multiple "\"'s
ex: C:\Drive\Folder\Subfolder\File

I am trying to extract just the file name, after the last "\". The amounts of "\"'s could vary sometime to time.

James

 

[raskew]

Hi -

pdx_man provided the solution using the InStrRev() function. I'm using A97, which unfortunately doesn't include that function. I'm using a roll-your-own replica (shown below). Here's how it would work from the debug window.

mystr = "The quick brown fox\jumped over\the lazy dog"
x = mid(mystr, InStrRev97(mystr, "\")+1)
? x
the lazy dog

...with InStrRev97() being:

Function InStrRev97(ByVal pstr As String, pItem As String) As Integer
'*******************************************
'Purpose:   Return location of last instance of a character or phrase.
'Inputs:    ? InStrRev("the quick brown fox jumped the lazy dog", "the")
'Output:    28 - Location of last occurence of "the"
'*******************************************

Dim i As Integer, n As Integer, tLen As Integer

    n = 0
    tLen = Len(pItem)
    For i = Len(RTrim(pstr)) To 1 Step -1
    
      If Mid(pstr, i, tLen) = pItem Then
          n = i
          Exit For
      End If
    Next i

    InStrRev97 = n

End Function

Assuming you're using a later version, lookup the InStrRev() function in the Help File and replace my InStrRev97() UDF with the built-in function.

Bob

 

[JVanDyne]

Thank you. I will try that.
I am using the 2000 version.

I have this database at work, have to try it on monday.

Again thank you.

 

[raskew]

James -

What is it that you want to read a string "right to left''. Don't understand where that's going and there's nothing in your code that addresses that concept. Seriously confused. Can provide code to reverse a string e.g. "12345" becomes "54321", but don't think that's what you're really after. Please advise.

Bob

 

[pdx_man]

InStrRev is what you should use then:

Sub test1()
Dim FileLoc As String
Dim SlashPos As Integer

FileLoc = "C:\Drive\Folder\Subfolder\File.xls"
SlashPos = InStrRev(FileLoc, "\")

Debug.Print Right(FileLoc, Len(FileLoc) - SlashPos)

End Sub