DATEDIFF EXPRESSION

[Guest]

When I use this expression MonthRangeateDiff("m",[DTE REF],[DTE OF PAY]),
if the [DTE REF] is, for example, 10/29/06 and [DTE OF PAY] is 11/05/07 it
calculates the result of 1. When the result is really 0.
Is there a way around this. What I am looking to do is to say if the
DateDiff of [DATE REF] AND [DTE OF PAY] is <=30 then put 0 or DateDiff of
[DATE REF] AND [DATE OF PAY] >=31 or <= 60 days then put 1.
Any help would be appreciated.
Thanks.

 

[Guest]

I assume the second date should have been 11/05/06 and if so:-

MonthRange: IIf(DateDiff("d",[DTE_REF],[DTE_OF_PAY]) Between 31 And 60,1,0)

There may be a prettier way of doing this as I'm a novice but it worked when
I tried it. Basically I have changed your DateDiff interval from months "m"
to "d" for days and then used your criteria of between 31 and 60 days in an
IIf statement.

Andy

 

[Guest]

You assumed correct. Sorry about that. I am not sure that I am able to use
the IF statement as I have 77 different months to account for. And I think
that the If statement only holds 14 expressions.

I assume the second date should have been 11/05/06 and if so:-

MonthRange: IIf(DateDiff("d",[DTE_REF],[DTE_OF_PAY]) Between 31 And 60,1,0)

There may be a prettier way of doing this as I'm a novice but it worked when
I tried it. Basically I have changed your DateDiff interval from months "m"
to "d" for days and then used your criteria of between 31 and 60 days in an
IIf statement.

Andy

When I use this expression MonthRangeateDiff("m",[DTE REF],[DTE OF PAY]),
if the [DTE REF] is, for example, 10/29/06 and [DTE OF PAY] is 11/05/07 it
calculates the result of 1. When the result is really 0.
Is there a way around this. What I am looking to do is to say if the
DateDiff of [DATE REF] AND [DTE OF PAY] is <=30 then put 0 or DateDiff of
[DATE REF] AND [DATE OF PAY] >=31 or <= 60 days then put 1.
Any help would be appreciated.
Thanks.

 

[SusanV]

You could divide the datediff result by 30, but that's not going to account
for months with 28 or 31 days...
--
hth,
SusanV

You assumed correct. Sorry about that. I am not sure that I am able to
use
the IF statement as I have 77 different months to account for. And I
think
that the If statement only holds 14 expressions.

I assume the second date should have been 11/05/06 and if so:-

MonthRange: IIf(DateDiff("d",[DTE_REF],[DTE_OF_PAY]) Between 31 And
60,1,0)

There may be a prettier way of doing this as I'm a novice but it worked
when
I tried it. Basically I have changed your DateDiff interval from months
"m"
to "d" for days and then used your criteria of between 31 and 60 days in
an
IIf statement.

Andy

When I use this expression MonthRangeateDiff("m",[DTE REF],[DTE OF
PAY]),
if the [DTE REF] is, for example, 10/29/06 and [DTE OF PAY] is 11/05/07
it
calculates the result of 1. When the result is really 0.
Is there a way around this. What I am looking to do is to say if the
DateDiff of [DATE REF] AND [DTE OF PAY] is <=30 then put 0 or DateDiff
of
[DATE REF] AND [DATE OF PAY] >=31 or <= 60 days then put 1.
Any help would be appreciated.
Thanks.

 

[Guest]

You could take a look at this past post:-

http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1

from the 2nd of March this year.


Andy

You assumed correct. Sorry about that. I am not sure that I am able to use
the IF statement as I have 77 different months to account for. And I think
that the If statement only holds 14 expressions.

I assume the second date should have been 11/05/06 and if so:-

MonthRange: IIf(DateDiff("d",[DTE_REF],[DTE_OF_PAY]) Between 31 And 60,1,0)

There may be a prettier way of doing this as I'm a novice but it worked when
I tried it. Basically I have changed your DateDiff interval from months "m"
to "d" for days and then used your criteria of between 31 and 60 days in an
IIf statement.

Andy

When I use this expression MonthRangeateDiff("m",[DTE REF],[DTE OF PAY]),
if the [DTE REF] is, for example, 10/29/06 and [DTE OF PAY] is 11/05/07 it
calculates the result of 1. When the result is really 0.
Is there a way around this. What I am looking to do is to say if the
DateDiff of [DATE REF] AND [DTE OF PAY] is <=30 then put 0 or DateDiff of
[DATE REF] AND [DATE OF PAY] >=31 or <= 60 days then put 1.
Any help would be appreciated.
Thanks.