Solved

Using IIF and dateadd in access query.

Posted on 2008-10-09
7
619 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

749 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