MS Access Forum / Queries / December 2005
IIF Statement
|
|
Thread rating:  |
mmazenge@border.co.zw - 28 Nov 2005 08:12 GMT 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 - 28 Nov 2005 08:45 GMT 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
> 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 [quoted text clipped - 10 lines] > > Memory. SteveS - 28 Nov 2005 09:19 GMT 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
 Signature Steve S. -------------------------------- "Veni, Vidi, Velcro" (I came, I saw, I stuck around.)
John Spencer - 28 Nov 2005 12:49 GMT 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.
> I agree with Tina about using "Is Not Null". > [quoted text clipped - 10 lines] > > HTH mmazenge@border.co.zw - 01 Dec 2005 08:23 GMT 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
SteveS - 01 Dec 2005 09:06 GMT > 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 [quoted text clipped - 4 lines] > > 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
 Signature Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.)
|
|
|