Calc Date Difference with an IIF statement.

[drober]

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

Thanks

 

[Golfinray]

You probably need to use a datediff command.
Datediff(["d", [first date field], second date field]) That will give you
the number of days between first and second date.

 

[drober]

How do I apply the DateDiff to my if statement? Thanks

You probably need to use a datediff command.
Datediff(["d", [first date field], second date field]) That will give you
the number of days between first and second date.

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

Thanks

 

[Golfinray]

Please do a search - you should find many examples.I cannot see your
application, so I am a bit unsure.

How do I apply the DateDiff to my if statement? Thanks

You probably need to use a datediff command.
Datediff(["d", [first date field], second date field]) That will give you
the number of days between first and second date.

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

Thanks

 

[KARL DEWEY]

You did not say what the error was.
You can try this --
NumDays: IIf([Bonus Eligible Date] > [EarningsBEGINdate], DateDiff("d",
[EarningsENDdate], [Bonus Eligible Date]), DateDiff("d", [EarningsENDdate],
[EarningsBEGINdate]))

 

[Marshall Barton]

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

The #Error could indicate that your dates are not Date type
fields. If they are Text fields, then you either have to
convert them to dates using the CDate function:

IIf(Cdate([Bonus Eligible Date])>Cdate([EarningsBEGINdate]),
,Cdate([EarningsENDdate]) - Cdate([Bonus Eligible Date]),
Cdate([EarningsENDdate]) - Cdate([EarningsBEGINdate]))

OR, better, use DateDiff, which will also convert the
strings to dates.

OR, best, fix your table to make the fields real date type
fields.

 

[drober]

Hi, Karl

Sorry, I am receiving #Error in my NumDays result field. I have my dates
formatted as dates. Can't figure out why I am not receiving the result.
I am calculating this from another query; will that cause the error? should
I convert the query to a table?

Thanks for your assitance.

You did not say what the error was.
You can try this --
NumDays: IIf([Bonus Eligible Date] > [EarningsBEGINdate], DateDiff("d",
[EarningsENDdate], [Bonus Eligible Date]), DateDiff("d", [EarningsENDdate],
[EarningsBEGINdate]))

--
KARL DEWEY
Build a little - Test a little

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

Thanks

 

[drober]

Hi, Marshall

I tried both of your suggestions and double checked to make sure my date
field were formatted as dates. But with both suggestions my NumDays result
field shows
#ERROR.

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

The #Error could indicate that your dates are not Date type
fields. If they are Text fields, then you either have to
convert them to dates using the CDate function:

IIf(Cdate([Bonus Eligible Date])>Cdate([EarningsBEGINdate]),
,Cdate([EarningsENDdate]) - Cdate([Bonus Eligible Date]),
Cdate([EarningsENDdate]) - Cdate([EarningsBEGINdate]))

OR, better, use DateDiff, which will also convert the
strings to dates.

OR, best, fix your table to make the fields real date type
fields.

 

[KARL DEWEY]

I have my dates formatted as dates.
It is not a matter of formatting but whether the field in the table is
defined as a DateTime datatype.
It can if that query is returning text instead of DateTime data.

--
KARL DEWEY
Build a little - Test a little

Hi, Karl

Sorry, I am receiving #Error in my NumDays result field. I have my dates
formatted as dates. Can't figure out why I am not receiving the result.
I am calculating this from another query; will that cause the error? should
I convert the query to a table?

Thanks for your assitance.

You did not say what the error was.
You can try this --
NumDays: IIf([Bonus Eligible Date] > [EarningsBEGINdate], DateDiff("d",
[EarningsENDdate], [Bonus Eligible Date]), DateDiff("d", [EarningsENDdate],
[EarningsBEGINdate]))

--
KARL DEWEY
Build a little - Test a little

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

Thanks

 

[drober]

I changed the query to a table so I could make sure the dates were formatted
correctly. Now the calculation does work but now my calculation is not
correct.
Here are my 3 scenieros:
EarningsBEGINdate Bonus Eligible Date EarningsENDdate
7/1/2008 8/25/2008 9/30/2008
7/1/2008 11/24/2008 9/30/2008
7/1/2008 9/30/2008

How do I write the calc to give me the following:
If(Bonus Eligible Date > EarningsBEGINdate and Bonus Eligilbe Date <
EarningsENDdate, EarningsENDdate-Bonus Eligible Date

and also If(Bonus Eligible Date > EarningsENDdate return 0

Also If(Bonus Eligible Date blank then EarningsENDdate-EarningsBEGINdate.

Thank you SO Much for your assistance.

It is not a matter of formatting but whether the field in the table is
defined as a DateTime datatype.
It can if that query is returning text instead of DateTime data.

--
KARL DEWEY
Build a little - Test a little

Hi, Karl

Sorry, I am receiving #Error in my NumDays result field. I have my dates
formatted as dates. Can't figure out why I am not receiving the result.
I am calculating this from another query; will that cause the error? should
I convert the query to a table?

Thanks for your assitance.

You did not say what the error was.
You can try this --
NumDays: IIf([Bonus Eligible Date] > [EarningsBEGINdate], DateDiff("d",
[EarningsENDdate], [Bonus Eligible Date]), DateDiff("d", [EarningsENDdate],
[EarningsBEGINdate]))

--
KARL DEWEY
Build a little - Test a little


:

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

Thanks

 

[KARL DEWEY]

IIf([Bonus Eligible Date] > [EarningsBEGINdate] And [Bonus Eligilbe Date] <
[EarningsENDdate], [EarningsENDdate]-[Bonus Eligible Date], SOMETHING FOR THE
FALSE)

--
KARL DEWEY
Build a little - Test a little

I changed the query to a table so I could make sure the dates were formatted
correctly. Now the calculation does work but now my calculation is not
correct.
Here are my 3 scenieros:
EarningsBEGINdate Bonus Eligible Date EarningsENDdate
7/1/2008 8/25/2008 9/30/2008
7/1/2008 11/24/2008 9/30/2008
7/1/2008 9/30/2008

How do I write the calc to give me the following:
If(Bonus Eligible Date > EarningsBEGINdate and Bonus Eligilbe Date <
EarningsENDdate, EarningsENDdate-Bonus Eligible Date

and also If(Bonus Eligible Date > EarningsENDdate return 0

Also If(Bonus Eligible Date blank then EarningsENDdate-EarningsBEGINdate.

Thank you SO Much for your assistance.

I have my dates formatted as dates.

It is not a matter of formatting but whether the field in the table is
defined as a DateTime datatype.

I am calculating this from another query; will that cause the error?

It can if that query is returning text instead of DateTime data.

--
KARL DEWEY
Build a little - Test a little

Hi, Karl

Sorry, I am receiving #Error in my NumDays result field. I have my dates
formatted as dates. Can't figure out why I am not receiving the result.
I am calculating this from another query; will that cause the error? should
I convert the query to a table?

Thanks for your assitance.

:

You did not say what the error was.
You can try this --
NumDays: IIf([Bonus Eligible Date] > [EarningsBEGINdate], DateDiff("d",
[EarningsENDdate], [Bonus Eligible Date]), DateDiff("d", [EarningsENDdate],
[EarningsBEGINdate]))

--
KARL DEWEY
Build a little - Test a little


:

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

Thanks

 

[drober]

Thank you!!! You have been very helpful!!!! You are MUCH Appreciated!!!

IIf([Bonus Eligible Date] > [EarningsBEGINdate] And [Bonus Eligilbe Date] <
[EarningsENDdate], [EarningsENDdate]-[Bonus Eligible Date], SOMETHING FOR THE
FALSE)

--
KARL DEWEY
Build a little - Test a little

I changed the query to a table so I could make sure the dates were formatted
correctly. Now the calculation does work but now my calculation is not
correct.
Here are my 3 scenieros:
EarningsBEGINdate Bonus Eligible Date EarningsENDdate
7/1/2008 8/25/2008 9/30/2008
7/1/2008 11/24/2008 9/30/2008
7/1/2008 9/30/2008

How do I write the calc to give me the following:
If(Bonus Eligible Date > EarningsBEGINdate and Bonus Eligilbe Date <
EarningsENDdate, EarningsENDdate-Bonus Eligible Date

and also If(Bonus Eligible Date > EarningsENDdate return 0

Also If(Bonus Eligible Date blank then EarningsENDdate-EarningsBEGINdate.

Thank you SO Much for your assistance.

I have my dates formatted as dates.
It is not a matter of formatting but whether the field in the table is
defined as a DateTime datatype.

I am calculating this from another query; will that cause the error?
It can if that query is returning text instead of DateTime data.

--
KARL DEWEY
Build a little - Test a little


:

Hi, Karl

Sorry, I am receiving #Error in my NumDays result field. I have my dates
formatted as dates. Can't figure out why I am not receiving the result.
I am calculating this from another query; will that cause the error? should
I convert the query to a table?

Thanks for your assitance.

:

You did not say what the error was.
You can try this --
NumDays: IIf([Bonus Eligible Date] > [EarningsBEGINdate], DateDiff("d",
[EarningsENDdate], [Bonus Eligible Date]), DateDiff("d", [EarningsENDdate],
[EarningsBEGINdate]))

--
KARL DEWEY
Build a little - Test a little


:

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

Thanks

 

[Marshall Barton]

I see that your discussion with Karl has resolved the issue.
So, unless you have a different question, I will consider
this thread done.
--
Marsh
MVP [MS Access]

I tried both of your suggestions and double checked to make sure my date
field were formatted as dates. But with both suggestions my NumDays result
field shows
#ERROR.

NumDays: IIf([Bonus Eligible
Date]>[EarningsBEGINdate],[EarningsENDdate]-[Bonus Eligible
Date],[EarningsENDdate]-[EarningsBEGINdate])

The above gives me #ERROR as result. How can I get it to return the number
of days.

The #Error could indicate that your dates are not Date type
fields. If they are Text fields, then you either have to
convert them to dates using the CDate function:

IIf(Cdate([Bonus Eligible Date])>Cdate([EarningsBEGINdate]),
,Cdate([EarningsENDdate]) - Cdate([Bonus Eligible Date]),
Cdate([EarningsENDdate]) - Cdate([EarningsBEGINdate]))

OR, better, use DateDiff, which will also convert the
strings to dates.

OR, best, fix your table to make the fields real date type
fields.