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.

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

 Page 1 of 1 [ 7 post ]

Relevant Pages