Help with this IIF statement

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

Guest

This is an IIf statement I am using and it is returning the value of "new" on
all of them. Can someone look at this and tell me what is wrong.

Status: IIf([BgnDt]>2/1/2006,"New",IIf([BgnDt]<2/1/2006,"Ongoing"))

Thanks.
 
Try some date delimiters so that Access knows that your date is a "Date".
Access is probably treating your dates as a division problem. 2 divided by 1
divided by 2006 is 9.9700897308075772681954137587238e-4. This is close
enough to zero for it to be sometime on VBA's base date of 30 Dec 1899. All
of your dates are probably after that, so the first part of the IIf is
always true.

Example:
Status: IIf([BgnDt]>#2/1/2006#,"New",IIf([BgnDt]<#2/1/2006#,"Ongoing"))
 
After playing around I now need to change the statement I originally posted.
Now I have found that I need to add another value. This is what I need now.

IIf (BgnDt]>#2/1/2006#, "new","ongoing", IIf([EndDt] is not null,
"closed")). I know this is not correct, but can someone please tell me how I
would do this.

Thanks.
 
Lisa,

I see you also posted your original question in the Reports group. If you
are going to post to more than one group (usually not necessary) then at
least list all groups in the To box at the same time so that all replies
will go to all of them simultaneously.

For your second equation, what do you want the result to be? Are you trying
to find records where [BgnDt] is after 1 Feb 2006 AND [EndDt] isn't null or
do you only want to check [EndDt] if the date test in the first part is
false?

I suspect what you want is:
IIf([BgnDt]>#2/1/2006#, "New", IIf([EndDt] Is Null, "On Going", "Closed"))
or
IIf([BgnDt]>#2/1/2006#, "New", IIf(IsNull([EndDt]), "On Going", "Closed"))

If you leave "Not" in it, you would reverse the results (On Going and
Closed) in the second part of the equation. All Not does is turn True to
False and False to True.

--
Wayne Morgan
MS Access MVP


LisaK said:
After playing around I now need to change the statement I originally
posted.
Now I have found that I need to add another value. This is what I need
now.

IIf (BgnDt]>#2/1/2006#, "new","ongoing", IIf([EndDt] is not null,
"closed")). I know this is not correct, but can someone please tell me
how I
would do this.

Thanks.


LisaK said:
This is an IIf statement I am using and it is returning the value of
"new" on
all of them. Can someone look at this and tell me what is wrong.

Status: IIf([BgnDt]>2/1/2006,"New",IIf([BgnDt]<2/1/2006,"Ongoing"))

Thanks.
Click to expand...
Click to expand...
 
IIF(EndDt is Not Null, "Closed", IIF(BgnDt>#2/1//2006#,"New","OnGoing"))

LisaK said:
After playing around I now need to change the statement I originally
posted.
Now I have found that I need to add another value. This is what I need
now.

IIf (BgnDt]>#2/1/2006#, "new","ongoing", IIf([EndDt] is not null,
"closed")). I know this is not correct, but can someone please tell me
how I
would do this.

Thanks.


LisaK said:
This is an IIf statement I am using and it is returning the value of
"new" on
all of them. Can someone look at this and tell me what is wrong.

Status: IIf([BgnDt]>2/1/2006,"New",IIf([BgnDt]<2/1/2006,"Ongoing"))

Thanks.
Click to expand...
Click to expand...
 
Back
Top