Results 1 to 10 of 10
  1. #1
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38

    Unanswered: rs.movenext error

    I have a filename list in a table like:
    p1.tif
    p2.tif
    p3.tif
    p4.tif
    p5.tif
    p6.tif
    p7.tif
    p8.tif
    p9.tif
    p10.tif

    in an access 2007 table. Normally the sort ascending moves the p10.tif next to the p1.tif

    So i wrote a sort that puts the p10.tif after the p9.tif & the display of the table shows the names in that order.

    However when my vba pgm does a rs.movefirst it gets the p1.tif then i do a rs.movenext & guess what i get the p10.tif filename. So what am i doing wrong? Anyone got any ideas? TIA

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    What is the PRIMARY KEY in this table??

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looks like you need to reconsider your indexing / naming strategy
    as MStef is allusinfg to you its down to the indexes (sort order) you use

    the key problem is your file naming, if you can I'd strongly recommend that you add a leadign 0 to the file names. if you think you will realistically only ever have say 30..40 files then just add a leading 0 so p1.tiff becomes p01.tiff. if you think there is a risk you may ned more that 50 files then consider adding two leadign zeros p1 -> p001, p10 ->p010 and so on

    if you cannot then you have to find a workaround to fix the basic problem.
    ..you could put a sort sequence column in the table that holds the references to the tiff files.. not a good solution
    ..you could write a function which returns the numeric part of the file. theres a kludge workaround that may work if you know that you will only have (at most) 99 tiff images which would be to do the work in the SQL

    eg
    ORDER BY cint(mid(mycolumn,1,2))
    ..which converts the second and third characters into an integer and uses that integer value to sort by.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Thanks for the interest. There is only one field in the table & that is the master key.

  5. #5
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Quote Originally Posted by healdem View Post
    looks like you need to reconsider your indexing / naming strategy
    as MStef is allusinfg to you its down to the indexes (sort order) you use



    eg
    ORDER BY cint(mid(mycolumn,1,2))
    ..which converts the second and third characters into an integer and uses that integer value to sort by.
    I assume i can use the instr function for the start of the mid function?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not try it and see what happens
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Ok i finally figured out how to get the sql:
    ORDER BY Val(Mid([filename],InStr(17,[filename],"p")-3)), Val(Mid([filename],InStr(17,[filename],"p")+1));

    Now however i got this sql from sinnhdo & cant figure out where to put the 'order by ...'

    Function Fill_Tbl_Files(ByVal Folder As String, Optional ByVal Extension As String = "*")
    'In this example, the Table that will contain the names of the files is named _Tbl_Files_ and
    ' it has a column [FileName] that contains the full name of a file
    ' (Disk:\FullPath\FileName.Extension)in each row.
    '
    'Code:
    '---------
    Const c_strSQL As String = "INSERT INTO Tbl_Files ( FileName ) VALUES ( '{@Filename}' );"
    Dim strFileName As String
    ' ORDER BY Val(Mid([filename],InStr(17,[filename],"p")-3)), Val(Mid([filename],InStr(17,[filename],"p")+1));

    CurrentDb.Execute "DELETE FROM Tbl_Files;", dbFailOnError
    If Right(Folder, 1) <> "\" Then Folder = Folder & "\"
    strFileName = Dir(Folder & "*." & Extension)
    Do Until Len(strFileName) = 0
    CurrentDb.Execute Replace(c_strSQL, "{@Filename}", Folder & strFileName), dbFailOnError
    strFileName = Dir
    Loop
    '---------
    'This function could for instance be called when the form that uses the Tbl_Files table opens.
    '***************
    End Function
    Got any ideas? TIA

  8. #8
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    It can be done on this way.
    Look at "DemoSortP1A2000.mdb" (attachmnet, zip).
    Look at Table1, Query1. Run Query1.
    It means from P1 thru P9999.
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    FWIW:- it looks like a good kludgey fix applied to the symptoms not the cause of the problem
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2011
    Location
    poway ca
    Posts
    38
    Ok thanks MStef-ZG u set me on the right course. I wrote a sql with my 'order by ... ' & then found out i could open a query as a recordset. All those trys :-( but u sent me on the proper way. THANKS!

    If u don't use this stuff regularly i forger so soon :-(.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •