How to run a criteria based Update Query

[JamesBBB]

Hi All,

I have a little problem here which is givng me a little problem, any help would be very gratefully received

I have a table with 2 fields, one contains a full path name,, the other is empty
what I need to do is run a query that will remove the extension and the path so it would populate the second field with just the filename.

It has to be a query as the data will change very often.

ie
Field1 FIELD2
G:\ITEMS\FOOD\peppers.txt BLANK
G:\ITEMS\FOOD\bananas.txt BLANK
G:\ITEMS\FOOD\Steak.txt BLANK
G:\ITEMS\FOOD\Groundnuts.txt BLANK

and after running would look like this

Field1 FIELD2
G:\ITEMS\FOOD\Peppers.txt Peppers
G:\ITEMS\FOOD\Bananas.txt Bananas
G:\ITEMS\FOOD\Steak.txt Steak
G:\ITEMS\FOOD\Groundnuts.txt Groundnuts

I'm really stuck on this one

many thanks

James

 

[Golom]

Try

UPDATE myTable
SET Field2 = Mid(Field1, InstrRev(Field1,"\") + 1, _
                 InstrRev(Field1,".") - InstrRev(Field1,"\") - 1)

 

[JamesBBB]

Hi Golom

Many thanks for the response.

I have typed your statement into the SQL view of the update query however I am getting a

Undefined Function 'InstrRev' in Expression

when I try to un it.

Do I need to anything else to get the InStrRev to work

many thanks

James

 

[Golom]

InstrRev" is available in Access 2K and above. If you're running Access 97 then you will need to write your own UDF to provide that functionality.

Public Function myInstrRev(ByVal TextString As String, ByVal SearchFor As String) As Long
    Dim n                           As Long
    Do Until InStr(n + 1, TextString, SearchFor) = 0
        n = InStr(n + 1, TextString, SearchFor)
    Loop
    myInstrRev = n
End Function

Place that in a module and then call "myInstrRev" in your SQL.

 

[JamesBBB]

Thanks Golom,

You are indeed an excellent chap, works a treat.

Its very strange it didnt work on your first example because I am running ACCESS2000 and have been for years.

But I created the module and called it in the query and it worked a dream

Many many thanks

James