Sorting with DIR function

Sorting with DIR function

am 29.12.2007 13:29:22 von anthony

What sort order does the DIR function use? I can't see any options
within the function to force it to sort in a particular order. Does
the sort order depend on the Windows Explorer setting for that folder?

Re: Sorting with DIR function

am 29.12.2007 13:37:22 von Baz

From Access 2003 help for the Dir function:

"Because file names are retrieved in no particular order, you may want to
store returned file names in an array, and then sort the array."

"anthony" wrote in message
news:1e63cf1b-8c67-43ff-8705-dd652c15bfc5@v4g2000hsf.googleg roups.com...
> What sort order does the DIR function use? I can't see any options
> within the function to force it to sort in a particular order. Does
> the sort order depend on the Windows Explorer setting for that folder?

Re: Sorting with DIR function

am 29.12.2007 14:18:11 von anthony

Ah, that's hiden away in the Access 2007 help under "Tip", which is
why I missed it. Many thanks

Actually, I'm going to need help with this because I really don't know
what an array is. I'm thinking in terms of putting the filenames into
a temporary table, sorting that and then processing them. Is that much
the same thing?

Re: Sorting with DIR function

am 29.12.2007 14:34:37 von Baz

Not really, no. A table will be in the Access file (i.e. it will be on the
disk) whereas an array exists only in memory. In theory, then, it should be
much quicker to create and sort an array, but you would have to "roll your
own" sort by writing a pile of code to do it (although a bit of Googling
would probably reveal loads of array sorts that people have already written
in VB). With your table idea, all you need to do is to query the table with
an appropriate ORDER BY clause.

Another in-memory option is a Collection object. Unlike an array, these
allow you to insert new items in between existing items, so with a little
bit of ingenuity you could effectively sort your collection while you were
creating it, which would obviate the need to implement a complex sort
algorithm.

"anthony" wrote in message
news:0a1b946a-ccac-4630-ae45-61455b06642f@n20g2000hsh.google groups.com...
> Ah, that's hiden away in the Access 2007 help under "Tip", which is
> why I missed it. Many thanks
>
> Actually, I'm going to need help with this because I really don't know
> what an array is. I'm thinking in terms of putting the filenames into
> a temporary table, sorting that and then processing them. Is that much
> the same thing?
>

Re: Sorting with DIR function

am 29.12.2007 14:54:22 von anthony

Your help is much appreciated. Many thanks

Re: Sorting with DIR function

am 29.12.2007 15:08:16 von Stuart McCall

"anthony" wrote in message
news:0a1b946a-ccac-4630-ae45-61455b06642f@n20g2000hsh.google groups.com...
> Ah, that's hiden away in the Access 2007 help under "Tip", which is
> why I missed it. Many thanks
>
> Actually, I'm going to need help with this because I really don't know
> what an array is. I'm thinking in terms of putting the filenames into
> a temporary table, sorting that and then processing them. Is that much
> the same thing?

If you'd like to explore array creation and sorting, try this:

http://www.groupacg.com/files/sorts01.zip

It's written in Access 97 but will convert without change to a later version
(not tested under Access 2007 yet).

Re: Sorting with DIR function

am 29.12.2007 17:21:20 von lyle

On Dec 29, 9:08 am, "Stuart McCall" wrote:
> "anthony" wrote in message
>
> news:0a1b946a-ccac-4630-ae45-61455b06642f@n20g2000hsh.google groups.com...
>
> > Ah, that's hiden away in the Access 2007 help under "Tip", which is
> > why I missed it. Many thanks
>
> > Actually, I'm going to need help with this because I really don't know
> > what an array is. I'm thinking in terms of putting the filenames into
> > a temporary table, sorting that and then processing them. Is that much
> > the same thing?
>
> If you'd like to explore array creation and sorting, try this:
>
> http://www.groupacg.com/files/sorts01.zip
>
> It's written in Access 97 but will convert without change to a later version
> (not tested under Access 2007 yet).

Sub APostFromYearsAgo()
Dim s As String
Dim a() As String
s = s & "Some File.ext"
s = s & ";"
s = s & "1 File.ext"
s = s & ";"
s = s & "A File.ext"
a = Split(s, ";")
WizHook.SortStringArray a
s = Join(a, ";")
Debug.Print s
'1 File.ext;A File.ext;Some File.ext
End Sub

Re: Sorting with DIR function

am 29.12.2007 17:40:45 von Stuart McCall

> Sub APostFromYearsAgo()
> Dim s As String
> Dim a() As String
> s = s & "Some File.ext"
> s = s & ";"
> s = s & "1 File.ext"
> s = s & ";"
> s = s & "A File.ext"
> a = Split(s, ";")
> WizHook.SortStringArray a
> s = Join(a, ";")
> Debug.Print s
> '1 File.ext;A File.ext;Some File.ext
> End Sub

Interesting. I've not run across this before. It doesn't load a wizard
library, either. Thanks for that.

Re: Sorting with DIR function

am 30.12.2007 16:25:55 von anthony

TVM Stuart and Lyle

Re: Sorting with DIR function

am 30.12.2007 17:59:15 von Tom van Stiphout

On Sat, 29 Dec 2007 08:21:20 -0800 (PST), lyle
wrote:

Still works in A2007.
Code window > F2 to open the Object Browser > RightClick in the
Members section and select Show Hidden Members > Search for WizHook.

-Tom.


>On Dec 29, 9:08 am, "Stuart McCall" wrote:
>> "anthony" wrote in message
>>
>> news:0a1b946a-ccac-4630-ae45-61455b06642f@n20g2000hsh.google groups.com...
>>
>> > Ah, that's hiden away in the Access 2007 help under "Tip", which is
>> > why I missed it. Many thanks
>>
>> > Actually, I'm going to need help with this because I really don't know
>> > what an array is. I'm thinking in terms of putting the filenames into
>> > a temporary table, sorting that and then processing them. Is that much
>> > the same thing?
>>
>> If you'd like to explore array creation and sorting, try this:
>>
>> http://www.groupacg.com/files/sorts01.zip
>>
>> It's written in Access 97 but will convert without change to a later version
>> (not tested under Access 2007 yet).
>
>Sub APostFromYearsAgo()
> Dim s As String
> Dim a() As String
> s = s & "Some File.ext"
> s = s & ";"
> s = s & "1 File.ext"
> s = s & ";"
> s = s & "A File.ext"
> a = Split(s, ";")
> WizHook.SortStringArray a
> s = Join(a, ";")
> Debug.Print s
> '1 File.ext;A File.ext;Some File.ext
>End Sub

Re: Sorting with DIR function

am 30.12.2007 18:19:32 von lyle

On Dec 30, 11:59 am, Tom van Stiphout wrote:

> Still works in A2007.
> Code window > F2 to open the Object Browser > RightClick in the
> Members section and select Show Hidden Members > Search for WizHook.

Wizhook has lotsa stuff that might be useful, such as a bold-line
enabled message box (long gone from normal VBA calls, it seems).

WizHook.WizMsgBox ".Key = 51488399 @Not so much lately!@", _
"Used to Need", vbOK, 0, ""

Re: Sorting with DIR function

am 30.12.2007 19:03:26 von Tom van Stiphout

On Sun, 30 Dec 2007 09:19:32 -0800 (PST), lyle
wrote:

Curiously this one doesn't seem to be working anymore in A2007. No
error message, nothing.

Later found out that the ".Key=51488399" should be set on the WizHook
object, like this:
Public Sub blahblah()
With WizHook
..Key = 51488399
..WizMsgBox "Bold Line @ @ Not So Bold Line", _
"The Caption", vbYesNo, 23, "Path To Some Help File"
End With
End Sub

-Tom.


>WizHook.WizMsgBox ".Key = 51488399 @Not so much lately!@", _
> "Used to Need", vbOK, 0, ""

Re: Sorting with DIR function

am 30.12.2007 20:17:46 von lyle

On Dec 30, 1:03 pm, Tom van Stiphout wrote:
> On Sun, 30 Dec 2007 09:19:32 -0800 (PST), lyle
>
> wrote:
>
> Curiously this one doesn't seem to be working anymore in A2007. No
> error message, nothing.
>
> Later found out that the ".Key=51488399" should be set on the WizHook
> object, like this:
> Public Sub blahblah()
> With WizHook
> .Key = 51488399
> .WizMsgBox "Bold Line @ @ Not So Bold Line", _
> "The Caption", vbYesNo, 23, "Path To Some Help File"
> End With
> End Sub
>
> -Tom.
>
> >WizHook.WizMsgBox ".Key = 51488399 @Not so much lately!@", _
> > "Used to Need", vbOK, 0, ""

WizHook.Key = 51488399

Seems to enable Wizhook globally for the duration of the application
instance. This confused me and I thought that WizHook.Key = 51488399
was no longer required at all.

Strangely!? Wizhook stays enabled even after an runtime error.