highest suffix

[puffer317]

Hello,
I have a form with a command button to view a file based on the selected record. The folder has several revved files. For example:
100
100a
200
200a
200b
I would like to open the highest suffix. (100a and 200b). If anyone could tell me what function I should be trying to use I'd be happy to try to figure it out from there. I'm using Access 2000.
Thank you

 

[georgedwilkinson]

I have no clue what you're saying you want to do. However, looking at your data I notice the values you point out are longer (in length) than the values you don't point out. If you are comparing the length of values, use the len() function. Then you can do a normal sort to further refine.

 

[georgedwilkinson]

Just thought about it again: based on the title of your post, you might also want to look at the "right()" function.

 

[puffer317]

description

Thanks for the response. Sorry I didn't make myself clear. I have a folder with document revisions in it. So it might have 100A, 100B, 100C, and so on. I'm trying to choose the file with the highest letter at the end (100C) because that will be the latest revision. Right now I have a follow hyperlink command to open the file, but it opens the first file it finds, and I need the last. I think the right() function got me closer, but I still can't sort from ZtoA.

 

[georgedwilkinson]

Is this being done in a query, form, VBA?

The revision should be kept in its own field to simplify stuff like this. The way you have it, it is a smart code.

Try:
select right(myfield,1) as Revision, myfield from mytable order by Revision desc, myfield;

Should get you just a little closer but is still not the answer you're looking for.

Like I said, if your revision was in a separate field, this would be much easier.

 

[namliam]

Use the DIR command to find the filenames and loop thru them to find the last file...
then use the followhyperlink to open it.

Lookup the dir command I think there is a good (enough) sample there you could use. Post back any remaining questions you have here

Good Luck !

 

[georgedwilkinson]

Use the DIR command to find the filenames and loop thru them to find the last file...
then use the followhyperlink to open it.

Doh! Now why couldn't I have said that? Much simpler and answers your question.

Thanks Mailman!

 

[puffer317]

Sweet

Sweeeeet. Thx again for the help. Took me a while to figure out how to get the value out of the loop before it got cleared. Works like a charm.

strFile = Dir(strPath)
Do While strFile <> ""
If strFile <> "" Then
strSuffix = strFile
End If
strFile = Dir
Loop
fullPath = strWild & strFolder & strSuffix
Application.FollowHyperlink fullPath

 

[namliam]

strFile = Dir(strPath)
Do While strFile <> ""
    strSuffix = strFile
    strFile = Dir
Loop
fullPath = strWild & strFolder & strSuffix
Application.FollowHyperlink fullPath

That should do, if the strFile = "" it will exit the loop not updating strSuffix.
But it works and another problem solved that is the most important thing.