custom SQL HELP!!!

  • Thread starter Thread starter skc
  • Start date Start date
S

skc

Hi,

I have generated a table that contains paths to files:

e.g.

C:\program files\something\file.xls 7888 asdasd
C:\my documents\somthing\something\anotherfile.xls 555 asd

I want to create a custom SQL in Access2000 that picks up
the filename at the end of the last "\", so in the case of
the examples above the filenames: file.xls and
anotherfile.xls.

How can I do this using a custom SQL that senses the
last "\" and picks up text after that excluding spaces as
there is some junk after the filename.

I can then use the DRW in FP2000 to list the files.

Skc
 
Hi SKC,

Use the built in function instrrev to locate the last "\" then use the
function mid to extract the file name.

Mid(myfieldname,instrrev(myfieldname,1))
 
SELECT Left(fieldName, InStrRev(fieldName, "\") - 1) FROM tableName...

Note, however, that using your SQL to trim the right-most character from the
field is going to be much more expensive than fetching the data straight out
from the database and trimming it in your program code.

HTH,

Kevin Spencer
Microsoft FrontPage MVP
Internet Developer
http://www.takempis.com
Some things just happen.
Everything else occurs.
 
InStrRev is not reconised in Access2000.

I tried to create a query using custom SQL.

skc
 
Kevin,

That will not work. The syntax of Left is left(strexpr, n) where n is the
number of characters from the left of the string. Thus
left(fieldname,InStrRev(fieldName,"\")) with return the first 'n' charaters
of the string where 'n' is the length of the file name.
 
how do i do that?
-----Original Message-----
Upgrade the database using ADO.

--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
FrontPage Add-ins Since '97 2003 / 2002 / 2000 Compatible
---------------------------------------------------------- ------------------
--------------------
If you think I'm doing a good job, let MS know at (e-mail address removed)




.
Click to expand...
 
In a nutshell, I want to do this:

Sense the last instance of "\"
List out everything after "\"

OR

better still....

the path is enclosed by " ".

So I have:

"C:\Program files\something\hello.htm" "junk"

All I want to do is sense the last occurence of "\" and
list the filename that will be before the last inverted
comma.

Skc
 
That is beyond the scope of this forum. There are some good books on this
stuff, SKC.
 
Back
Top