S D
accessd667 at yahoo.com
Thu Jul 8 08:01:16 CDT 2004
Hi group, thnx for the replies but I feel i'm taking up you're time without getting any closer to the solution, perhaps I wasn't clear with describing the problem. To finish the discussion: I do not know how the filenames look like! So it could be that filenames are: customer01 customer02 but it could also be customera customerb or customer customer_backup1 customer_backup2 That's why we decided to (try to :-)) create a list that displayed records that filtered out the first 15 positions of the filename. I'm going to build a dumb app that puts all records in a table, create a new table with the first 15 chars of the filenames found in table 1. Then do some magic to get the first record in table1 where a 'key' was found in table2....or something haha my got this is going to be messy!! My advise: don't drink until 3:00 am and think you can work the next day :-) Again thnx! Sander Gustav Brock <gustav at cactus.dk> wrote: Hi Sander OK. But that what was it looked like from your sample data. Also, if you don't know what to look for, you're in trouble ... Anyway, my suggestion will give you less than 12.000 names. Next step could be to detract the numeric last part of the names. You can do that with Val(InstrRev()). What you also can do is to look up the left part of the filename: Select Distinct Left([Filename],[n]) As FileStart From YourTable If n=1, you'll probably get the alphabet; if n=40 you'll get all 12.000 records. Increment from 1 and you'll get an idea of the names. Write these selections to a temp table. /gustav > 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 > Gustav Brock wrote: > Hi Sander > Well, you need the first word of the filename. > Try selecting on > Select Distinct > Left([Filename], > IIf(InStr([Filename]," ")=0, > Len([Filename]), > InStr([Filename], " ")-1)) As File > From > tblFiles; > /gustav >> Thanx Gustav! That's exactly what I need! >> Can you please provide me a query for the other 12.000(+) records? Oh, btw I do not know all filenames...yet! >> :-) there's a little bit of sarcasme in the above :-) >> I need to know which files where accessed during the last 7 days and I want to filter out 'period versioning'. So i've got a lot of files like: >> Customer Aug 03.xls >> Customer Sept 03.xls >> Expens Aug 03.xls >> Expens Sept 03.xls >> etc etc >> I want the query to return: >> Customer >> Expens >> This way I know how many Excel-apps we've got...remember the 12000 records? that wasn't sarcasme :-( >> HTH >> Gustav Brock wrote: >> Hi S >>> i've got a query problem. >>> I've got a table in A2k with 27 fields. The first field is "Filename". I've got the following data in the table: >>> Filename Field2, Field3, Field4, etc... >>> "Filename1", blabla, blabla, blabla, blabla, >>> "Filename2", blabla, blabla, blabla, blabla, >>> "Filename3", blabla, blabla, blabla, blabla, >>> I need a query that displays: >>> "Filename1", blabla, blabla, blabla, blabla, >>> Problem: All other fields have diff info for all records!! >> Well >> Select * From tblSD Where Filename = 'Filename1'; >> will do that. >> But that's too obvious. What are you trying to do? >> /gustav -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com --------------------------------- Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses.