• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 688
  • Last Modified:

Using IIF and dateadd in access query.

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
jclem1
Asked:
jclem1
2 Solutions
 
jppintoCommented:
Are you putting the closing ) on the query?

What error are you getting on Access query?

jppinto
0
 
GRayLCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LambertHeenanCommented:
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
 
LambertHeenanCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
@LambertHeenan, thanks for cleaning up.  I corrected the bracket problems I saw, but forgot one of the end ones myself. :)
0
 
jclem1Author Commented:
Thank you both very much, very helpful and very quick!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now