IIF Statement

G

Guest

My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]
 
G

Guest

I do not see how it is counting. What is counted? How does 'termination'
enter in the formula?
 
G

Guest

DOT referers to date of termination and DOI refers to date of intake and then
counts how many days they were in our system for the month of september.

KARL DEWEY said:
I do not see how it is counting. What is counted? How does 'termination'
enter in the formula?
--
KARL DEWEY
Build a little - Test a little


krc547 said:
My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]
Click to expand...
Click to expand...
 
P

pietlinden

My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]
Click to expand...

use DateDiff("d",#9/30/2007#,[Client DOI])
 
G

Guest

That works to count the number of days from DOI to 9/30/07, but it still does
not count the number of days between DOT (termination date) and DOI.

[email protected] said:
My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]
Click to expand...

use DateDiff("d",#9/30/2007#,[Client DOI])
Click to expand...
 
D

dhabtem1

more specifically, i think pietlinden means:
DateDiff("d", [Client DOI], IIf([Client DOT]<#9/30/2007#,[Client
DOT],#9/30/2007#))

but more generally, it sounds like there're 4 possibilities, and i
think you're problem is with the last one:
[Client DOT] is Not Null, [Client DOI] is Not Null - calculates
correctly (simplest scenario)
[Client DOT] is Null, [Client DOI] is Null - calculates to Null (makes
sense, both null)
[Client DOT] is Not Null, [Client DOI] is Null - calculates to Null
(needs a DOI)
[Client DOT] is Null, [Client DOI] is Not Null - calculates to
#9/30/2007# - [Client DOI]

i think you're problem is with this last case scenario. access is
treating a null date as technically less than 9/30/2007. If you don't
want it to, try:
Days: IIf([Client DOT]<#9/30/2007#,IIf([Client DOT] Is Null, "",
[Client DOT]),#9/30/2007#)-[Client DOI]
 
G

Guest

Using this data ---
Client DOI Client DOT
08/04/2007
09/01/2007 09/15/2007
09/01/2007
08/04/2007 10/03/2007
with this query ---
SELECT krc547.[Client DOI], krc547.[Client DOT], DateDiff("d",IIf([Client
DOI]<[Enter month and year 09/01/2007],[Enter month and year
09/01/2007],[Client DOI]),IIf([Client DOT] Is Null,DateAdd("m",1,[Enter month
and year 09/01/2007])-1,IIf([Client DOT]>DateAdd("m",1,[Enter month and year
09/01/2007])-1,DateAdd("m",1,[Enter month and year 09/01/2007])-1,[Client
DOT])))+1 AS Expr1
FROM krc547;
this results ---
Client DOI Client DOT Expr1
08/04/2007 30
09/01/2007 09/15/2007 15
09/01/2007 30
08/04/2007 10/03/2007 30
--
KARL DEWEY
Build a little - Test a little


krc547 said:
That works to count the number of days from DOI to 9/30/07, but it still does
not count the number of days between DOT (termination date) and DOI.

[email protected] said:
My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]
Click to expand...

use DateDiff("d",#9/30/2007#,[Client DOI])
Click to expand...
Click to expand...
 
G

Guest

This comes back invalid syntax.

KARL DEWEY said:
Using this data ---
Client DOI Client DOT
08/04/2007
09/01/2007 09/15/2007
09/01/2007
08/04/2007 10/03/2007
with this query ---
SELECT krc547.[Client DOI], krc547.[Client DOT], DateDiff("d",IIf([Client
DOI]<[Enter month and year 09/01/2007],[Enter month and year
09/01/2007],[Client DOI]),IIf([Client DOT] Is Null,DateAdd("m",1,[Enter month
and year 09/01/2007])-1,IIf([Client DOT]>DateAdd("m",1,[Enter month and year
09/01/2007])-1,DateAdd("m",1,[Enter month and year 09/01/2007])-1,[Client
DOT])))+1 AS Expr1
FROM krc547;
this results ---
Client DOI Client DOT Expr1
08/04/2007 30
09/01/2007 09/15/2007 15
09/01/2007 30
08/04/2007 10/03/2007 30
--
KARL DEWEY
Build a little - Test a little


krc547 said:
That works to count the number of days from DOI to 9/30/07, but it still does
not count the number of days between DOT (termination date) and DOI.

[email protected] said:
My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]

use DateDiff("d",#9/30/2007#,[Client DOI])
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sorry, this works well except it returns 21 days for someone who termed on
9/20/07, it should return 19 because we do not count the day they leave.

KARL DEWEY said:
Using this data ---
Client DOI Client DOT
08/04/2007
09/01/2007 09/15/2007
09/01/2007
08/04/2007 10/03/2007
with this query ---
SELECT krc547.[Client DOI], krc547.[Client DOT], DateDiff("d",IIf([Client
DOI]<[Enter month and year 09/01/2007],[Enter month and year
09/01/2007],[Client DOI]),IIf([Client DOT] Is Null,DateAdd("m",1,[Enter month
and year 09/01/2007])-1,IIf([Client DOT]>DateAdd("m",1,[Enter month and year
09/01/2007])-1,DateAdd("m",1,[Enter month and year 09/01/2007])-1,[Client
DOT])))+1 AS Expr1
FROM krc547;
this results ---
Client DOI Client DOT Expr1
08/04/2007 30
09/01/2007 09/15/2007 15
09/01/2007 30
08/04/2007 10/03/2007 30
--
KARL DEWEY
Build a little - Test a little


krc547 said:
That works to count the number of days from DOI to 9/30/07, but it still does
not count the number of days between DOT (termination date) and DOI.

[email protected] said:
My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]

use DateDiff("d",#9/30/2007#,[Client DOI])
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

What if they leave on the same day?
Remove the +1 from the statement.
,[Enter month and year 09/01/2007])-1,[Client DOT])))+1 AS Expr1
FROM krc547;

--
KARL DEWEY
Build a little - Test a little


krc547 said:
Sorry, this works well except it returns 21 days for someone who termed on
9/20/07, it should return 19 because we do not count the day they leave.

KARL DEWEY said:
Using this data ---
Client DOI Client DOT
08/04/2007
09/01/2007 09/15/2007
09/01/2007
08/04/2007 10/03/2007
with this query ---
SELECT krc547.[Client DOI], krc547.[Client DOT], DateDiff("d",IIf([Client
DOI]<[Enter month and year 09/01/2007],[Enter month and year
09/01/2007],[Client DOI]),IIf([Client DOT] Is Null,DateAdd("m",1,[Enter month
and year 09/01/2007])-1,IIf([Client DOT]>DateAdd("m",1,[Enter month and year
09/01/2007])-1,DateAdd("m",1,[Enter month and year 09/01/2007])-1,[Client
DOT])))+1 AS Expr1
FROM krc547;
this results ---
Client DOI Client DOT Expr1
08/04/2007 30
09/01/2007 09/15/2007 15
09/01/2007 30
08/04/2007 10/03/2007 30
--
KARL DEWEY
Build a little - Test a little


krc547 said:
That works to count the number of days from DOI to 9/30/07, but it still does
not count the number of days between DOT (termination date) and DOI.

:

My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]

use DateDiff("d",#9/30/2007#,[Client DOI])
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Almost now it returns 20 days

KARL DEWEY said:
What if they leave on the same day?
Remove the +1 from the statement.
,[Enter month and year 09/01/2007])-1,[Client DOT])))+1 AS Expr1
FROM krc547;

--
KARL DEWEY
Build a little - Test a little


krc547 said:
Sorry, this works well except it returns 21 days for someone who termed on
9/20/07, it should return 19 because we do not count the day they leave.

KARL DEWEY said:
Using this data ---
Client DOI Client DOT
08/04/2007
09/01/2007 09/15/2007
09/01/2007
08/04/2007 10/03/2007
with this query ---
SELECT krc547.[Client DOI], krc547.[Client DOT], DateDiff("d",IIf([Client
DOI]<[Enter month and year 09/01/2007],[Enter month and year
09/01/2007],[Client DOI]),IIf([Client DOT] Is Null,DateAdd("m",1,[Enter month
and year 09/01/2007])-1,IIf([Client DOT]>DateAdd("m",1,[Enter month and year
09/01/2007])-1,DateAdd("m",1,[Enter month and year 09/01/2007])-1,[Client
DOT])))+1 AS Expr1
FROM krc547;
this results ---
Client DOI Client DOT Expr1
08/04/2007 30
09/01/2007 09/15/2007 15
09/01/2007 30
08/04/2007 10/03/2007 30
--
KARL DEWEY
Build a little - Test a little


:

That works to count the number of days from DOI to 9/30/07, but it still does
not count the number of days between DOT (termination date) and DOI.

:

My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]

use DateDiff("d",#9/30/2007#,[Client DOI])
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Karl, This does work. I did not save it so it was still running the
old way. It works great now.

KARL DEWEY said:
What if they leave on the same day?
Remove the +1 from the statement.
,[Enter month and year 09/01/2007])-1,[Client DOT])))+1 AS Expr1
FROM krc547;

--
KARL DEWEY
Build a little - Test a little


krc547 said:
Sorry, this works well except it returns 21 days for someone who termed on
9/20/07, it should return 19 because we do not count the day they leave.

KARL DEWEY said:
Using this data ---
Client DOI Client DOT
08/04/2007
09/01/2007 09/15/2007
09/01/2007
08/04/2007 10/03/2007
with this query ---
SELECT krc547.[Client DOI], krc547.[Client DOT], DateDiff("d",IIf([Client
DOI]<[Enter month and year 09/01/2007],[Enter month and year
09/01/2007],[Client DOI]),IIf([Client DOT] Is Null,DateAdd("m",1,[Enter month
and year 09/01/2007])-1,IIf([Client DOT]>DateAdd("m",1,[Enter month and year
09/01/2007])-1,DateAdd("m",1,[Enter month and year 09/01/2007])-1,[Client
DOT])))+1 AS Expr1
FROM krc547;
this results ---
Client DOI Client DOT Expr1
08/04/2007 30
09/01/2007 09/15/2007 15
09/01/2007 30
08/04/2007 10/03/2007 30
--
KARL DEWEY
Build a little - Test a little


:

That works to count the number of days from DOI to 9/30/07, but it still does
not count the number of days between DOT (termination date) and DOI.

:

My second question,

I have the following statement and it counts the days correctly if there is
an intake but it does not count the days correctly if there is a termination:

Days: IIf([Client DOT]<#9/30/2007#,[Client DOT],#9/30/2007#)-[Client DOI]

use DateDiff("d",#9/30/2007#,[Client DOI])
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pull latest date from Multiple entries 2
IIf statement too long. Other options? 5
getting first, last and middle record 1
IIf statement not working 4
Multiple IIf's 6
IIf function, & Date(), & AND, & > & < 6
Using a OR() like function in an IIF statement 4
joining two iif statements 2

Top