extract database name from path string

[brightstar]

anyone know how i can extract just the name of a database from its full path in a query?
i have the full path in a field, but i would also like another field to just hold the name of the database. there's over 300 records so i would rather not do it by hand!

cheers

http://www.agonyonline.co.uk

free, anonymous advice provided by the whole world

 

[scriverb]

Post an example of one the data in the field and I can do this for you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[glalsop]

This will extract everything to the right of the last "\" in a path name:

Right(YourFullPathField,Len(YourFullPathField)-InStrRev(YourFullPathField,"\&quot)

-Gary

 

[scriverb]

Copy and paste the following Function into a database module.
Public Function InStrRight(vSearchStr As String, vTargetStr As String, vStart As Long) As Long
Dim I As Integer
For I = vStart To 1 Step -1
If InStr(I, vSearchStr, vTargetStr, 1) = I Then
InStrRight = I 'Position of vTargetStr
Exit For
End If
Next I
End Function

Here is code for an Update query to strip off the database name and update a field.

UPDATE tblYourTableName as A SET A.DB = Mid$([PathToDB],InStrRight([PathToDB],"\",Len([PathToDB]))+1);

Update the SQL with the appropriate table name and field name for the path information.

Post back with questions or concerns.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Brightstar: glalsop's code uses the built in function InStrRev which is not available in A97. I am still using A97 and had to write my own function to do the same thing. Both will work fine.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[brightstar]

marvekous, cheers all for the info and the solution!

http://www.agonyonline.co.uk

free, anonymous advice provided by the whole world