Comparing dates driving me crazy

  • Thread starter Thread starter JerryC
  • Start date Start date
J

JerryC

NDD = Format(Me![NextDueDate], "Short Date")
PMRange = 8
TD = Format(Date, "Short Date")

If NDD > DateAdd("d", PMRange * 2, TD) = True Then
MsgBox "Do This"
End If

Why is If statment always true ???

Thanks for your input.
 
Assuming that you didn't Dim NDD as a date variable, it's likely a variant
variable. When you set it equal to the result of a Format statement, you'll
get a string.

Thus, you're always testing a string against a date value in the If
statement. Because they're not the same data type, ACCESS will change one to
match the other, and I'm guessing that it is changing the result of the
DateAdd function to a string. As such, the > operator will test based on
string comparison, not based on date value comparison.

Change this line
NDD = Format(Me![NextDueDate], "Short Date")

to this
NDD = DateValue(Me![NextDueDate])

I also would add
Dim NDD As Date

to the procedure's code.
 
Thanks Ken, the DIM NDD As Date solved the problem


-----Original Message-----
Assuming that you didn't Dim NDD as a date variable, it's likely a variant
variable. When you set it equal to the result of a Format statement, you'll
get a string.

Thus, you're always testing a string against a date value in the If
statement. Because they're not the same data type, ACCESS will change one to
match the other, and I'm guessing that it is changing the result of the
DateAdd function to a string. As such, the > operator will test based on
string comparison, not based on date value comparison.

Change this line
NDD = Format(Me![NextDueDate], "Short Date")

to this
NDD = DateValue(Me![NextDueDate])

I also would add
Dim NDD As Date

to the procedure's code.
--

Ken Snell
<MS ACCESS MVP>

NDD = Format(Me![NextDueDate], "Short Date")
PMRange = 8
TD = Format(Date, "Short Date")

If NDD > DateAdd("d", PMRange * 2, TD) = True Then
MsgBox "Do This"
End If

Why is If statment always true ???

Thanks for your input.
Click to expand...


.
Click to expand...
 
Couple of thoughts (working off of 4 hours of sleep, facing a 16 hour
day)...

It shouldn't be neccessary to use the Format() function to compare two
date values. As long as the values are encapsulated in ## the compare
should work. Use IsDate() to confirm that [NextDueDate] is a date, if
not, use DateSerial() or CDate() to convert it. As to debuging, snoop
around the code by replacing the variable with actual values, this will
let you know if the problem is with the logic or somethign with the
variables. And of course DEBUG.PRINT
 
Wouldn't it be better to DIM NDD as Variant to allow [NextDueDate] to be
null and then test for null? I've had several instances where I declared
a function as a particular type only to pass it a NULL value and have it
crap out.
 
While a variant variable declaration would enable the variable to hold a
Null value, I (and others) would not recommend that you use Variant just for
that reason. If the intended data to be held in that variable is date/time,
then you should declare it as Date. That avoids any confusion in the code
and the calculations/processing regarding what the data type actually is.

If one suspects that a Null value might be an issue, then the code can use
the Nz function to capture it and to handle it as the programmer intends.

--

Ken Snell
<MS ACCESS MVP>


David C. Holley said:
Wouldn't it be better to DIM NDD as Variant to allow [NextDueDate] to be
null and then test for null? I've had several instances where I declared a
function as a particular type only to pass it a NULL value and have it
crap out.
Assuming that you didn't Dim NDD as a date variable, it's likely a
variant variable. When you set it equal to the result of a Format
statement, you'll get a string.

Thus, you're always testing a string against a date value in the If
statement. Because they're not the same data type, ACCESS will change one
to match the other, and I'm guessing that it is changing the result of
the DateAdd function to a string. As such, the > operator will test based
on string comparison, not based on date value comparison.

Change this line
NDD = Format(Me![NextDueDate], "Short Date")

to this
NDD = DateValue(Me![NextDueDate])

I also would add
Dim NDD As Date

to the procedure's code.
Click to expand...
Click to expand...
 
Back
Top