IIF Statement

[mmazenge]

Please help me with syntax of this nested IIf Statement. I am trying to
track Internal Requisitions Status and report on it at any given time
as the requisition goes through various stages, i.e. raised, approved,
ordered and finally delivered, and can also be cancelled. The values of
these status are derived from a date and my syntax is as follows:

=IIf(IsDate([Fo_Date])=Yes, "Delivered", iif(IsDate([T_Date])=Yes,
"Ordered', iif(IsDate([S_Date])=Yes, "Approved","Waiting Approval")))

Thanks in advance

Regards

Memory.

 

[tina]

well, i don't think i've ever seen the IsDate() function used that way. the
function "Returns a Boolean value indicating whether an expression can be
converted to a date." (per Access Help.) unless your fields are *not*
date/time data type, you don't need to test whether the values are valid
dates. i think what you're trying to do is see whether the fields contain a
date value or whether they're *null*. i suppose the IsDate() function might
give the same result, but having never used it in that manner, i don't know
if there are circumstances where it might return unexpected results. suggest
you try instead

=IIf([Fo_Date] Is Not Null, "Delivered", IIf([T_Date] Is Not Null,
"Ordered", IIf([S_Date] Is Not Null, "Approved", "Waiting Approval")))

if you really want to use the IsDate() function for some reason, you may
need to test for a boolean value, as

=IIf(IsDate([Fo_Date])=True, "Delivered", IIf(IsDate([T_Date])=True,
"Ordered", IIf(IsDate([S_Date])=True, "Approved", "Waiting Approval")))

hth

 

[SteveS]

tina wrote:

I agree with Tina about using "Is Not Null".

But I wanted to point out why you were having problems with the compound IIF()
statement:

=IIf(IsDate([Fo_Date])=Yes, "Delivered", iif(IsDate([T_Date])=Yes,
"Ordered', iif(IsDate([S_Date])=Yes, "Approved","Waiting Approval")))

^^
There should be a double quote (") after Ordered, not a single quote (').



Its the little things that are the hardest to find.....


HTH

 

[John Spencer]

I might add that this is a good candidate for either a custom function or
the use of the Switch function. That assumes this is all being done in
Access.

=SWITCH(IsDate(Fo_Date),"Delivered",IsDate(T_Date),"Ordered",IsDate(S_Date),"Approved",
True,"Waiting Approval")

I find that a lot easier to read and construct and if you needed to add
another status it is simpler to edit for me.

tina wrote:

I agree with Tina about using "Is Not Null".

But I wanted to point out why you were having problems with the compound
IIF() statement:

=IIf(IsDate([Fo_Date])=Yes, "Delivered", iif(IsDate([T_Date])=Yes,
"Ordered', iif(IsDate([S_Date])=Yes, "Approved","Waiting Approval")))

^^
There should be a double quote (") after Ordered, not a single quote (').



Its the little things that are the hardest to find.....


HTH

 

[mmazenge]

Thanks all for your contribution, i have finally managed to accomplish
what i was trying to do and the correct syntax is as follows:

IIf(IsDate([OrderDate])=Yes,"Ordered",IIf(IsDate([DateRefered])=Yes,"Refered
To
NCS",IIf(IsDate([DateApproved])=Yes,"Appoved",IIf(IsDate([CancelledDate])=Yes,"Cancelled
/ Rejected","Waiting Approval"))))

But one more problem, am now trying to capture the user id as they log
on to the database and use the data to default it in a field, e.g. I
log on as mmazenge, and use the user id to default in a field on a
form.

Thanks in advance

Memory

 

[Guest]

But one more problem, am now trying to capture the user id as they log
on to the database and use the data to default it in a field, e.g. I
log on as mmazenge, and use the user id to default in a field on a
form.

Thanks in advance

Memory

Here are three links that should help you. The first two are on "The Access
Web"; I've used the first example.

http://www.mvps.org/access/api/api0008.htm

http://www.mvps.org/access/general/gen0034.htm


This one is from "Rogers Access Library"

http://rogersaccesslibrary.com/download3.asp?SampleName=AuditTrail.mdb


HTH