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.
--

Ken Snell
<MS ACCESS MVP>

"JerryC" <(E-Mail Removed)> wrote in message
news:550101c5233b$f553a730$(E-Mail Removed)...
> 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.

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>
>
>"JerryC" <(E-Mail Removed)> wrote in
message
>news:550101c5233b$f553a730$(E-Mail Removed)...
>> 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.
>
>
>.
>

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

JerryC wrote:
> 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.

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.

Ken Snell [MVP] wrote:
> 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.

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" <(E-Mail Removed)> wrote in message
news:Ov%(E-Mail Removed)...
> 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.
>
> Ken Snell [MVP] wrote:
>> 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.