Date comparisons 
Author Message
 Date comparisons

Hi there:
    I'm really stimmied on this one.  I've created a function that
returns true or false.  The function is called GreaterThan(TodaysDate As
Date) and simply compares date years first, then date months, then date
days.
This function is called to determine if an expiry date is GreaterThan
the current date on the computer.  Now, when I run the function on the
design computer, it operates flawlessly.  However, I packaged the
project and loaded it up onto a  couple test computers.  Each test
computer returns a false value for the function when it should not.  I
narrowed down the problem with the comparisons being made for the
months.  What I've discovered is that, for some reason, when I use the
VB function: mm =  Month(TodaysDate)  I wind up with a value that is not
the month number but the day number. So for the month of April you would
expect mm to equal 4.  That's not what is happening, instead I get mm
equals 6. I'm testing this on April 6, 1999.  So, I'm stuck.  Why is mm
getting the day figure when it should be getting the month figure?  I
keep thinking it has to do with the systems date formatting, but I'm not
sure how to ensure the formatting remains consistance from one computer
to the next.
I tried using the Format() function to force a specific format:
mm/dd/yy  but that didn't seen to matter any.
Please look at my code and recommend a surefire way to get the proper
results I'm looking for.
Here is how my code looked originally.  I will follow it up with how it
looks now with a couple of changes I made which didn't seem to make any
difference.

Original Code:

Public Function GreaterThan(TodaysDate As Date) As Boolean
Dim mm As Integer
Dim dd As Integer
Dim yy As Integer

'We compare the year, month and day of TodaysDate
'to determine if it is prior to the date which is
'an instance of this class
mm = Month(TodaysDate)
dd = Day(TodaysDate)
yy = Year(TodaysDate)

'yr is the expiry year
'mth is the expiry month
'dy is the expiry day

If yr < yy Then
    GreaterThan = False
    Exit Function
ElseIf yr > yy Then
    GreaterThan = True
    Exit Function
ElseIf mth < mm Then  '<< here is the problem comparison on April 6
1999, mm has a value of 6 instead of 4
    GreaterThan = False
    Exit Function
ElseIf mth > mm Then
    GreaterThan = True
    Exit Function
ElseIf dy > dd Then
    GreaterThan = True
    Exit Function
Else
    GreaterThan = False
    Exit Function
End If
End Function

After some changes:

Public Function GreaterThan(TodaysDate As Date) As Boolean
Dim mm As Integer
Dim dd As Integer
Dim yy As Integer
Dim st As String
Dim st1 As String
Dim st2 As String
Dim i As Integer
Dim j As Integer
'We compare the year, month and day of TodaysDate
'to determine if it is prior to the date which is
'an instance of this class

st = Format(TodaysDate, "Short Date")
st1 = Left(st, 2)
If st1 Like "#/" Then
    st1 = Left(st, 1)
    mm = CInt(st1)
Else
    mm = CInt(st1)
End If
MsgBox "mm = " & CStr(mm), vbInformation
j = Len(st)
If j = 8 Then
    st2 = Mid(st, 4, 2)
    dd = CInt(st2)
ElseIf j = 7 Then
    st2 = Mid(st, 3, 2)
    If st2 Like "/#" Then
        st2 = Mid(st, 4, 1)
        dd = CInt(st2)
    Else
        dd = CInt(st2)
    End If
ElseIf j = 6 Then
    st2 = Mid(st, 3, 1)
    dd = CInt(st2)

End If
yy = Year(TodaysDate)

'yr is the expiry year
'mth is the expiry month
'dy is the expiry day

If yr < yy Then
    GreaterThan = False
    Exit Function
ElseIf yr > yy Then
    GreaterThan = True
    Exit Function
ElseIf mth < mm Then  '<< This is still a problem comparison
    GreaterThan = False
    Exit Function
ElseIf mth > mm Then
    GreaterThan = True
    Exit Function
ElseIf dy > dd Then
    GreaterThan = True
    Exit Function
Else
    GreaterThan = False
    Exit Function
End If
End Function

If you can help, I'd very much appreciate it.

Alan Shiers



Sun, 23 Sep 2001 03:00:00 GMT  
 Date comparisons
When working with dates it's always better to avoid parsing them if you can.
Try something like this instead:

Public Function bGreaterThan(ByVal dtTodaysDate As Date) As Boolean

    'Assuming that msYY, msMM, and msDD are module level variables
equivalent to the original
    'code's yr, mth, and dy variables and that these variables contain the
date parts of the date you
    'want to test against (somehow, I wasn't clear on this from the original
note)...

    bGreaterThan = DateSerial(CInt(msYY),CInt(msMM), CInt(msDD)) >
dtTodaysDate

    'Note: You'd be better off if your year representation included 4 digits
too
    '(otherwise your going to let Windows decide what century it is!)

End Function

Regards,

Joel Brown


    Hi there:
        I'm really stimmied on this one.  I've created a function that
returns true or false.  The function is called GreaterThan(TodaysDate As
Date) and simply compares date years first, then date months, then date
days.
    This function is called to determine if an expiry date is GreaterThan
the current date on the computer.  Now, when I run the function on the
design computer, it operates flawlessly.  However, I packaged the project
and loaded it up onto a  couple test computers.  Each test computer returns
a false value for the function when it should not.  I narrowed down the
problem with the comparisons being made for the months.  What I've
discovered is that, for some reason, when I use the VB function: mm =
Month(TodaysDate)  I wind up with a value that is not the month number but
the day number. So for the month of April you would expect mm to equal 4.
That's not what is happening, instead I get mm equals 6. I'm testing this on
April 6, 1999.  So, I'm stuck.  Why is mm getting the day figure when it
should be getting the month figure?  I keep thinking it has to do with the
systems date formatting, but I'm not sure how to ensure the formatting
remains consistance from one computer to the next.
    I tried using the Format() function to force a specific format: mm/dd/yy
but that didn't seen to matter any.
    Please look at my code and recommend a surefire way to get the proper
results I'm looking for.
    Here is how my code looked originally.  I will follow it up with how it
looks now with a couple of changes I made which didn't seem to make any
difference.
    Original Code:

    Public Function GreaterThan(TodaysDate As Date) As Boolean
    Dim mm As Integer
    Dim dd As Integer
    Dim yy As Integer

    'We compare the year, month and day of TodaysDate
    'to determine if it is prior to the date which is
    'an instance of this class
    mm = Month(TodaysDate)
    dd = Day(TodaysDate)
    yy = Year(TodaysDate)

    'yr is the expiry year
    'mth is the expiry month
    'dy is the expiry day

    If yr < yy Then
        GreaterThan = False
        Exit Function
    ElseIf yr > yy Then
        GreaterThan = True
        Exit Function
    ElseIf mth < mm Then  '<< here is the problem comparison on April 6
1999, mm has a value of 6 instead of 4
        GreaterThan = False
        Exit Function
    ElseIf mth > mm Then
        GreaterThan = True
        Exit Function
    ElseIf dy > dd Then
        GreaterThan = True
        Exit Function
    Else
        GreaterThan = False
        Exit Function
    End If
    End Function

    After some changes:

    Public Function GreaterThan(TodaysDate As Date) As Boolean
    Dim mm As Integer
    Dim dd As Integer
    Dim yy As Integer
    Dim st As String
    Dim st1 As String
    Dim st2 As String
    Dim i As Integer
    Dim j As Integer
    'We compare the year, month and day of TodaysDate
    'to determine if it is prior to the date which is
    'an instance of this class

    st = Format(TodaysDate, "Short Date")
    st1 = Left(st, 2)
    If st1 Like "#/" Then
        st1 = Left(st, 1)
        mm = CInt(st1)
    Else
        mm = CInt(st1)
    End If
    MsgBox "mm = " & CStr(mm), vbInformation
    j = Len(st)
    If j = 8 Then
        st2 = Mid(st, 4, 2)
        dd = CInt(st2)
    ElseIf j = 7 Then
        st2 = Mid(st, 3, 2)
        If st2 Like "/#" Then
            st2 = Mid(st, 4, 1)
            dd = CInt(st2)
        Else
            dd = CInt(st2)
        End If
    ElseIf j = 6 Then
        st2 = Mid(st, 3, 1)
        dd = CInt(st2)

    End If
    yy = Year(TodaysDate)

    'yr is the expiry year
    'mth is the expiry month
    'dy is the expiry day

    If yr < yy Then
        GreaterThan = False
        Exit Function
    ElseIf yr > yy Then
        GreaterThan = True
        Exit Function
    ElseIf mth < mm Then  '<< This is still a problem comparison
        GreaterThan = False
        Exit Function
    ElseIf mth > mm Then
        GreaterThan = True
        Exit Function
    ElseIf dy > dd Then
        GreaterThan = True
        Exit Function
    Else
        GreaterThan = False
        Exit Function
    End If
    End Function

    If you can help, I'd very much appreciate it.

    Alan Shiers



Sun, 23 Sep 2001 03:00:00 GMT  
 Date comparisons

I pass dates as strings and then do

if CDate(sStartDate) > CDate(sEndDate) Then
    Return True
else
   Return False
end if

This has always worked for me

Enzo

    Hi there:
        I'm really stimmied on this one.  I've created a function that returns true or false.  The function is called GreaterThan(TodaysDate As Date) and simply compares date years first, then date months, then date days.
    This function is called to determine if an expiry date is GreaterThan the current date on the computer.  Now, when I run the function on the design computer, it operates flawlessly.  However, I packaged the project and loaded it up onto a  couple test computers.  Each test computer returns a false value for the function when it should not.  I narrowed down the problem with the comparisons being made for the months.  What I've discovered is that, for some reason, when I use the VB function: mm =  Month(TodaysDate)  I wind up with a value that is not the month number but the day number. So for the month of April you would expect mm to equal 4.  That's not what is happening, instead I get mm equals 6. I'm testing this on April 6, 1999.  So, I'm stuck.  Why is mm getting the day figure when it should be getting the month figure?  I keep thinking it has to do with the systems date formatting, but I'm not sure how to ensure the formatting remains consistance from one computer to the next.
    I tried using the Format() function to force a specific format: mm/dd/yy  but that didn't seen to matter any.
    Please look at my code and recommend a surefire way to get the proper results I'm looking for.
    Here is how my code looked originally.  I will follow it up with how it looks now with a couple of changes I made which didn't seem to make any difference.
    Original Code:

    Public Function GreaterThan(TodaysDate As Date) As Boolean
    Dim mm As Integer
    Dim dd As Integer
    Dim yy As Integer

    'We compare the year, month and day of TodaysDate
    'to determine if it is prior to the date which is
    'an instance of this class
    mm = Month(TodaysDate)
    dd = Day(TodaysDate)
    yy = Year(TodaysDate)

    'yr is the expiry year
    'mth is the expiry month
    'dy is the expiry day

    If yr < yy Then
        GreaterThan = False
        Exit Function
    ElseIf yr > yy Then
        GreaterThan = True
        Exit Function
    ElseIf mth < mm Then  '<< here is the problem comparison on April 6 1999, mm has a value of 6 instead of 4
        GreaterThan = False
        Exit Function
    ElseIf mth > mm Then
        GreaterThan = True
        Exit Function
    ElseIf dy > dd Then
        GreaterThan = True
        Exit Function
    Else
        GreaterThan = False
        Exit Function
    End If
    End Function

    After some changes:

    Public Function GreaterThan(TodaysDate As Date) As Boolean
    Dim mm As Integer
    Dim dd As Integer
    Dim yy As Integer
    Dim st As String
    Dim st1 As String
    Dim st2 As String
    Dim i As Integer
    Dim j As Integer
    'We compare the year, month and day of TodaysDate
    'to determine if it is prior to the date which is
    'an instance of this class

    st = Format(TodaysDate, "Short Date")
    st1 = Left(st, 2)
    If st1 Like "#/" Then
        st1 = Left(st, 1)
        mm = CInt(st1)
    Else
        mm = CInt(st1)
    End If
    MsgBox "mm = " & CStr(mm), vbInformation
    j = Len(st)
    If j = 8 Then
        st2 = Mid(st, 4, 2)
        dd = CInt(st2)
    ElseIf j = 7 Then
        st2 = Mid(st, 3, 2)
        If st2 Like "/#" Then
            st2 = Mid(st, 4, 1)
            dd = CInt(st2)
        Else
            dd = CInt(st2)
        End If
    ElseIf j = 6 Then
        st2 = Mid(st, 3, 1)
        dd = CInt(st2)

    End If
    yy = Year(TodaysDate)

    'yr is the expiry year
    'mth is the expiry month
    'dy is the expiry day

    If yr < yy Then
        GreaterThan = False
        Exit Function
    ElseIf yr > yy Then
        GreaterThan = True
        Exit Function
    ElseIf mth < mm Then  '<< This is still a problem comparison
        GreaterThan = False
        Exit Function
    ElseIf mth > mm Then
        GreaterThan = True
        Exit Function
    ElseIf dy > dd Then
        GreaterThan = True
        Exit Function
    Else
        GreaterThan = False
        Exit Function
    End If
    End Function

    If you can help, I'd very much appreciate it.

    Alan Shiers



Sun, 23 Sep 2001 03:00:00 GMT  
 Date comparisons
You may desire to work with the serial parts of your expiry date,
I don't know.  Even so, I would convert the expiry date to a Date type,
and simply compare the (Date) parameter, with the expiry date:

Public Function GreaterThan(TodaysDate As Date) As Boolean
    GreaterThan = (DateSerial(yr, mth, dy) > TodaysDate)
End Function

Quote:

> Hi there:
>     I'm really stimmied on this one.  I've created a function that returns true or false.
> The function is called GreaterThan(TodaysDate As Date) and simply compares date years
> first, then date months, then date days.   This function is called to determine if an
> expiry date is GreaterThan the current date on the computer.

If you always test the current date against the expiry date, I would think you are actually
looking to see if 'today' is past the expiration date, or not:

Public Function Expired (ExpiryDate As Date) As Boolean
    Expired = (Now > ExpiryDate)  'Expires AFTER...
    'Expired = (Now >= ExpiryDate) 'Expires ON...
End Function

HTH
LFS



Sun, 23 Sep 2001 03:00:00 GMT  
 Date comparisons
Hi there:
    Well, it certainly appears that my approach to this function was convoluted.  I was
approaching the problem from a certain angle and it turned out to be a bad angle. Much thanks
to Larry Serflaten for his recommendation.  I wasn't even aware of a VB Function named
DateSerial(yr, mth, dy).  Now that I'm aware of it, I'll be sure to remember to use it in
future.  Amazing!  One line of code to replace all those lines of code I was using earlier.
Thanks,

Alan Shiers

Quote:

> You may desire to work with the serial parts of your expiry date,
> I don't know.  Even so, I would convert the expiry date to a Date type,
> and simply compare the (Date) parameter, with the expiry date:

> Public Function GreaterThan(TodaysDate As Date) As Boolean
>     GreaterThan = (DateSerial(yr, mth, dy) > TodaysDate)
> End Function


> > Hi there:
> >     I'm really stimmied on this one.  I've created a function that returns true or false.
> > The function is called GreaterThan(TodaysDate As Date) and simply compares date years
> > first, then date months, then date days.   This function is called to determine if an
> > expiry date is GreaterThan the current date on the computer.

> If you always test the current date against the expiry date, I would think you are actually
> looking to see if 'today' is past the expiration date, or not:

> Public Function Expired (ExpiryDate As Date) As Boolean
>     Expired = (Now > ExpiryDate)  'Expires AFTER...
>     'Expired = (Now >= ExpiryDate) 'Expires ON...
> End Function

> HTH
> LFS



Sun, 23 Sep 2001 03:00:00 GMT  
 Date comparisons

None of this is necessary if you keep your dates in Date type variables. Internally Date type variables store the number of days since 30/12/1899. Thus
    dteToday > dteYesterday
is always true no matter what the local date display format is.

You should never hold or try to process dates as strings. They should always be converted to Date type variables as soon as they are input by the user. They should be stored in databases as Date types and should only be converted to strings for display to the user. Always use the formats of "Short Date" and "Long Date" and never hard code format strings such as "mm/dd/yy".
--

Simon Jones
MillStream Designs Ltd
Independent IT Consultants



Sun, 23 Sep 2001 03:00:00 GMT  
 Date comparisons
What is the form of "TodaysDate".  Note that with some country
settings (that is, nearly all except the US <g>) a string date
aa-bb-cccc will interpret the aa as Day.

I'd suggest you convert dates to a DateSerial format.  If you're
trying to get the system date, use the NOW function.

Dan



Quote:
>Hi there:
>    I'm really stimmied on this one.  I've created a function that
>returns true or false.  The function is called GreaterThan(TodaysDate As
>Date) and simply compares date years first, then date months, then date
>days.
>This function is called to determine if an expiry date is GreaterThan
>the current date on the computer.  Now, when I run the function on the
>design computer, it operates flawlessly.  However, I packaged the
>project and loaded it up onto a  couple test computers.  Each test
>computer returns a false value for the function when it should not.  I
>narrowed down the problem with the comparisons being made for the
>months.  What I've discovered is that, for some reason, when I use the
>VB function: mm =  Month(TodaysDate)  I wind up with a value that is not
>the month number but the day number. So for the month of April you would
>expect mm to equal 4.  That's not what is happening, instead I get mm
>equals 6. I'm testing this on April 6, 1999.  So, I'm stuck.  Why is mm
>getting the day figure when it should be getting the month figure?  I
>keep thinking it has to do with the systems date formatting, but I'm not
>sure how to ensure the formatting remains consistance from one computer
>to the next.
>I tried using the Format() function to force a specific format:
>mm/dd/yy  but that didn't seen to matter any.
>Please look at my code and recommend a surefire way to get the proper
>results I'm looking for.
>Here is how my code looked originally.  I will follow it up with how it
>looks now with a couple of changes I made which didn't seem to make any
>difference.

>Original Code:

>Public Function GreaterThan(TodaysDate As Date) As Boolean
>Dim mm As Integer
>Dim dd As Integer
>Dim yy As Integer

>'We compare the year, month and day of TodaysDate
>'to determine if it is prior to the date which is
>'an instance of this class
>mm = Month(TodaysDate)
>dd = Day(TodaysDate)
>yy = Year(TodaysDate)

>'yr is the expiry year
>'mth is the expiry month
>'dy is the expiry day

>If yr < yy Then
>    GreaterThan = False
>    Exit Function
>ElseIf yr > yy Then
>    GreaterThan = True
>    Exit Function
>ElseIf mth < mm Then  '<< here is the problem comparison on April 6
>1999, mm has a value of 6 instead of 4
>    GreaterThan = False
>    Exit Function
>ElseIf mth > mm Then
>    GreaterThan = True
>    Exit Function
>ElseIf dy > dd Then
>    GreaterThan = True
>    Exit Function
>Else
>    GreaterThan = False
>    Exit Function
>End If
>End Function

>After some changes:

>Public Function GreaterThan(TodaysDate As Date) As Boolean
>Dim mm As Integer
>Dim dd As Integer
>Dim yy As Integer
>Dim st As String
>Dim st1 As String
>Dim st2 As String
>Dim i As Integer
>Dim j As Integer
>'We compare the year, month and day of TodaysDate
>'to determine if it is prior to the date which is
>'an instance of this class

>st = Format(TodaysDate, "Short Date")
>st1 = Left(st, 2)
>If st1 Like "#/" Then
>    st1 = Left(st, 1)
>    mm = CInt(st1)
>Else
>    mm = CInt(st1)
>End If
>MsgBox "mm = " & CStr(mm), vbInformation
>j = Len(st)
>If j = 8 Then
>    st2 = Mid(st, 4, 2)
>    dd = CInt(st2)
>ElseIf j = 7 Then
>    st2 = Mid(st, 3, 2)
>    If st2 Like "/#" Then
>        st2 = Mid(st, 4, 1)
>        dd = CInt(st2)
>    Else
>        dd = CInt(st2)
>    End If
>ElseIf j = 6 Then
>    st2 = Mid(st, 3, 1)
>    dd = CInt(st2)

>End If
>yy = Year(TodaysDate)

>'yr is the expiry year
>'mth is the expiry month
>'dy is the expiry day

>If yr < yy Then
>    GreaterThan = False
>    Exit Function
>ElseIf yr > yy Then
>    GreaterThan = True
>    Exit Function
>ElseIf mth < mm Then  '<< This is still a problem comparison
>    GreaterThan = False
>    Exit Function
>ElseIf mth > mm Then
>    GreaterThan = True
>    Exit Function
>ElseIf dy > dd Then
>    GreaterThan = True
>    Exit Function
>Else
>    GreaterThan = False
>    Exit Function
>End If
>End Function

>If you can help, I'd very much appreciate it.

>Alan Shiers



Sun, 23 Sep 2001 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Date Comparison

2. Date Comparison in a Bubble Sort

3. ASP VBScript Date COMPARISON ??

4. date comparison query

5. Dates Comparison VB textbox to Access 95 (v7)

6. Dates Comparison VB textbox to Access 95 (v7)

7. Date Comparison VB variable / Now to Access V7.0

8. date comparison query

9. Date Comparison Issue

10. Confused over Date comparisons

11. Date comparisons not working?

12. date comparisons

 

 
Powered by phpBB® Forum Software