Solved

Using IIF and dateadd in access query.

Posted on 2008-10-09
7
602 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 36
Access 2016 7 35
Update a text value in another table 10 40
Help with DoEvents 8 28
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

810 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