[AccessD] Query problem

On 8 Jul 2004 at 3:41, S D wrote:

> No that didn't do the trick. The problem is I do NOT know the values!
> So it's not just the first word (filename could be: customer01.xls,
> customer02.xls) It gets the first word, true but it didn't solve the
> problem. As mentioned before there are a lot of other fields that need
> to be displayed... A real nasty one. I've got some SQL-guru's over here
> looking at the problem. They've came up with the most exotic
> things..none of them worked. I'll have another look at it. Thanx anyway.
> Sander 
> 

In that case, strip it out to the first not alpha character rather than to the 
first space.

Function Rootname(filename as string) as string
dim loopcount as long
dim strTest as String
strTest = "ABCDEFGHUJKLMNOPQRSTUVWXYZ"
filename = ucase$(filename)
For loopcount = 1 to len(filename)
  if instr(strTest, mid$(filename, loopcount,1)) = 0 then
     Rootname = left$(filename, loopcount -1)
    exit for
next
End Function

Select Distinct Rootname((Filename) From tblFiles;

 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.