Solved

Using IIF and dateadd in access query.

Posted on 2008-10-09
7
613 Views
Last Modified: 2013-11-27
I am continuing to write sql for my database and have encountered another question.

This is what I have:

FRT Due: IIf([DATE_ESCALATED]<=[DESIRED_DD],[DESIRED_DD]),IIF( [DATE_ESCALATED] > [DUE_SCHED_DATE] ,DateAdd("H",3, [DATE_ESCALATED] ), [DUE_SCHED_DATE]

This is what the excel looks like and it works =IF(F5<=H5,H5,(IF(F5>G5,F5+3,G5))), but I want this to work in an access query.

I have attache a sample of the excel book below.

Thanks
snipet.xls
0
Comment
Question by:jclem1
7 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 22682061
Are you putting the closing ) on the query?

What error are you getting on Access query?

jppinto
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22682066
FRT Due: IIf([DATE_ESCALATED]<=[DESIRED_DD],[DESIRED_DD]),IIF( [DATE_ESCALATED] > [DUE_SCHED_DATE] ,DateAdd("H",3, [DATE_ESCALATED]), [DUE_SCHED_DATE] )

A closing parenthesis??
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 150 total points
ID: 22682070
What error or bad result are you getting.  The Excel version is adding 3 days and you are adding hours.

http://www.techonthenet.com/access/functions/date/dateadd.php
FRT Due: IIf([DATE_ESCALATED]<=[DESIRED_DD],[DESIRED_DD],IIF( [DATE_ESCALATED] > [DUE_SCHED_DATE] ,DateAdd("d",3, [DATE_ESCALATED] ), [DUE_SCHED_DATE])

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 11

Expert Comment

by:LambertHeenan
ID: 22682104
FRT Due: IIf([DATE_ESCALATED]<=[DESIRED_DD],[DESIRED_DD],IIF( [DATE_ESCALATED] > [DUE_SCHED_DATE] ,DateAdd("H",3, [DATE_ESCALATED]) , [DUE_SCHED_DATE] ))

It's all about the brackets!

In general IIF works like this

IIF(SomeCondition, AnswerIfTrue, AnswerIfFalse)

Here is the solution broken up so you can seethe parts...

FRT Due: IIf(
{here is the condition}   [DATE_ESCALATED]<=[DESIRED_DD],
{the answer if the condition is TRUE} [DESIRED_DD],
{the answer id the condition is FALSE - a nested IIF} IIF(
{nested IIF condition } [DATE_ESCALATED] > [DUE_SCHED_DATE] ,
{ nested IFF true answer} DateAdd("H",3, [DATE_ESCALATED]) ,
{ nested IIF False answer} [DUE_SCHED_DATE] )
) {close parentheses on the outer IIF }
0
 
LVL 11

Accepted Solution

by:
LambertHeenan earned 350 total points
ID: 22682117
good spot mwvisa1. he answer adding 3 days would be

FRT Due: IIf([DATE_ESCALATED]<=[DESIRED_DD],[DESIRED_DD],IIF( [DATE_ESCALATED] > [DUE_SCHED_DATE] ,DateAdd("d",3, [DATE_ESCALATED]) , [DUE_SCHED_DATE] ))
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22682220
@LambertHeenan, thanks for cleaning up.  I corrected the bracket problems I saw, but forgot one of the end ones myself. :)
0
 
LVL 1

Author Closing Comment

by:jclem1
ID: 31504812
Thank you both very much, very helpful and very quick!
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question