Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / December 2005

Tip: Looking for answers? Try searching our database.

IIF Statement

Thread view: 
Enable EMail Alerts  Start New Thread
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.)


 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.