Number of days not calculating correct in an iif expression

[Marcie]

I am trying to calculate the number of days between a hard date (7/1/2008)
and a calculated field (newstepdate) only if a field (step < 7) and
(newstepdate < 7/1/2009). the below code works except when month is 10 11
or 12. I am guessing this may be some type of converting problem but I
cannot come up with any conversion that seems to resolve it.

IIf(([PayrollDetail]![Step]<7 And
[newstepdate]<"7/1/2009"),(DateDiff("d","7/1/2008",[newstepdate])))

 

[Marshall Barton]

I am trying to calculate the number of days between a hard date (7/1/2008)
and a calculated field (newstepdate) only if a field (step < 7) and
(newstepdate < 7/1/2009). the below code works except when month is 10 11
or 12. I am guessing this may be some type of converting problem but I
cannot come up with any conversion that seems to resolve it.

IIf(([PayrollDetail]![Step]<7 And
[newstepdate]<"7/1/2009"),(DateDiff("d","7/1/2008",[newstepdate])))

Comparing Text values is not what you want here.

If newstepdate is a date type field, then you have to
compare it to a date (i.e newstepdate < #7/1/2009#). If
it's a Text field that always looks like a date, then you
have to convert it to a date (e.g. CDate(newstepdate) <
#7/1/2009#).

Even though DateDiff will automatically convert a text
string that looks like a date to a real date type value, it
is best to explicitly use date values:

If newstepdate is a date type field:
DateDiff("d", #7/1/2008#, newstepdate)
or if it's a Text field:
DateDiff("d", #7/1/2008#, CDate(newstepdate))

 

[Marcie]

Worked perfect
A huge THANK YOU

I am trying to calculate the number of days between a hard date (7/1/2008)
and a calculated field (newstepdate) only if a field (step < 7) and
(newstepdate < 7/1/2009). the below code works except when month is 10 11
or 12. I am guessing this may be some type of converting problem but I
cannot come up with any conversion that seems to resolve it.

IIf(([PayrollDetail]![Step]<7 And
[newstepdate]<"7/1/2009"),(DateDiff("d","7/1/2008",[newstepdate])))

Comparing Text values is not what you want here.

If newstepdate is a date type field, then you have to
compare it to a date (i.e newstepdate < #7/1/2009#). If
it's a Text field that always looks like a date, then you
have to convert it to a date (e.g. CDate(newstepdate) <
#7/1/2009#).

Even though DateDiff will automatically convert a text
string that looks like a date to a real date type value, it
is best to explicitly use date values:

If newstepdate is a date type field:
DateDiff("d", #7/1/2008#, newstepdate)
or if it's a Text field:
DateDiff("d", #7/1/2008#, CDate(newstepdate))