DATEDIFF EXPRESSION

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I use this expression MonthRange:DateDiff("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.
 
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
 
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.

Andy Bailey said:
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

nazzoli said:
When I use this expression MonthRange:DateDiff("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.
Click to expand...
Click to expand...
 
You could divide the datediff result by 30, but that's not going to account
for months with 28 or 31 days...
--
hth,
SusanV


nazzoli said:
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.

Andy Bailey said:
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

nazzoli said:
When I use this expression MonthRange:DateDiff("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.
Click to expand...
Click to expand...
Click to expand...
 
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

nazzoli said:
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.

Andy Bailey said:
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

nazzoli said:
When I use this expression MonthRange:DateDiff("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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top