SUGGEST to get min max date from txt string

[luca90]

I use the tipical free file eof .... to read line by line a txt file.

During the loop a get in a mid postion of string a date form each lines and store the date value it in mYDate var.

How to get the min and max date when the loop is finished?

Peraphs i can store the date value in a collection, in a dictionary...

note:
In txt are approx 350.xxx lines

[Bonnie West]

Private Sub Routine()
    Dim MaxDate As Date, MyDate As Date
    Dim MinDate As Date, sLine  As String

    Open "File.txt" For Input As #1
        If Not EOF(1) Then
            Line Input #1, sLine
            MyDate = CDate(Mid$(sLine, 1&))
           'Initialize both MaxDate & MinDate to MyDate
            MaxDate = MyDate
            MinDate = MyDate
           'Do whatever needs to be done with MyDate here
        End If

        Do Until EOF(1)
            Line Input #1, sLine
            MyDate = CDate(Mid$(sLine, 1&))

            If MyDate > MaxDate Then
                MaxDate = MyDate
            ElseIf MyDate < MinDate Then
                MinDate = MyDate
            End If

           'Do whatever needs to be done with MyDate here
        Loop
    Close #1
End Sub

[Spoo]

Luca

One possible way would be to create 2 more vars .. myMaxDate, and myMinDate. They probably should be Date type.

Then, each time you update your myDate var, test it against the two
new vars.
1. If myDate < myMinDate .. replace myMinDate with myDate
2. If myDate > myMaxDate .. replace myMaxDate with myDate.

BTW, the first time you "get" myDate, you'll want to initiate both MyMinDate and MyMaxDate with that first date.

EDIT:

Ha! Bonnie definitely types faster than I do.

Spoo

[dilettante]

You might need to set MinDate to some high value before the loop.

This part confused me though:

MyDate = CDate(Mid$(sLine, 1&))

Why not use:

MyDate = CDate(sLine)

What am I missing?

Also, writing simple constants with & meaning Long (as in 1& above) is overkill. It is a rare situation when the compiler doesn't know what type to use, typically only when using the ByVal override in API calls. It doesn't hurt but it adds a lot of clutter.

[Bonnie West]

BTW, the first time you "get" myDate, you'll want to initiate both MyMinDate and MyMaxDate with that first date.

You might need to set MinDate to some high value before the loop.

I think initialization of both Max and Min dates aren't necessary. I can't see how leaving them uninitialized can affect the final outcome.

What am I missing?

During the loop a get in a mid postion of string a date form each lines and store the date value it in mYDate var.

Also, writing simple constants with & meaning Long (as in 1& above) is overkill. It is a rare situation when the compiler doesn't know what type to use, typically only when using the ByVal override in API calls. It doesn't hurt but it adds a lot of clutter.

Well, I've gotten used to doing it that way and I'm not sure I'd want to break this habit. Anyway, it's just an example and it isn't the most important point here.

[Spoo]

Bonnie

If you don't initialize MinDate, then wouldn't there be a problem (using your code snippet) comparing myDate to MinDate in the line ElseIf myDate <= MinDate Then ,, ?

MinDate would be empty, and thus would never be assigned a value, right?

Spoo

[Bonnie West]

MinDate would be empty, and thus would never be assigned a value, right?

Um, no. Date variables actually have a default value (December 30, 1899 12:00:00 AM) so there shouldn't be any problem comparing minimum and maximum dates. If MyDate happened to be less than or exactly 12/30/1899 12:00:00 AM, then it becomes the MinDate. If it happened to be later than that date, then it becomes the MaxDate. MinDate/MaxDate would only be Empty if they were declared as a Variant.

[techgnome]

Um, no. Date variables actually have a default value (December 30, 1899 12:00:00 AM) so there shouldn't be any problem comparing minimum and maximum dates. If MyDate happened to be less than or exactly 12/30/1899 12:00:00 AM, then it becomes the MinDate. If it happened to be later than that date, then it becomes the MaxDate. MinDate/MaxDate would only be Empty if they were declared as a Variant.

Right... so think about that.... if the min date in the data is Feb 2, 2000 ... it would never get found now would it? Because MyDate would NEVER be less than the default date... ergo... when you get the first date from the data stream... you set both Min and Max to that date, then do your comparisons from there... eventually the min date would work its way down to Feb 2, 2000 .... Otherwise the date would have to be nearly two centuries old in order for it to become the min date.


-tg

[Bonnie West]

Right... so think about that.... if the min date in the data is Feb 2, 2000 ... it would never get found now would it? Because MyDate would NEVER be less than the default date... ergo... when you get the first date from the data stream... you set both Min and Max to that date, then do your comparisons from there... eventually the min date would work its way down to Feb 2, 2000 .... Otherwise the date would have to be nearly two centuries old in order for it to become the min date.


-tg

Ah, now I get it. So Spoo's earlier advice was actually right. Thanks for the correction! The code in post #2 has now been amended.

[Spoo]

Bonnie

I knew you'd get it
My earlier post was a little too terse ,, TG explained it much better.

Spoo