Part of IIF Statement in Access Query Not Working 1

[pete1505]

Can you all take a look at this Query and let me know why you think it's not working:

SELECT TASK_ORDER.C_Name, TASK_ORDER.CO_Name, TASK_ORDER.[Cont_#], TASK_ORDER.[TO_#], DELIVERABLE.[Del_#], DELIVERABLE.[Invoiced Amount], DELIVERABLE.Rec_Date, DELIVERABLE.HQ_App_Date, DELIVERABLE.Invoiced_Date, DELIVERABLE.KDF_Approval, DELIVERABLE.R_Drive_Date, DELIVERABLE.DB_Update_Date, DELIVERABLE.Website_Date, DELIVERABLE.Comments,
IIf((DELIVERABLE.Rec_Date<=DELIVERABLE.HQ_App_Date) Or (DELIVERABLE.HQ_App_Date Is Null),
IIf((DELIVERABLE.Rec_Date<=DELIVERABLE.Invoiced_Date) Or (DELIVERABLE.Invoiced_Date Is Null),DELIVERABLE.Rec_Date,
IIf((DELIVERABLE.HQ_App_Date<=DELIVERABLE.Invoiced_Date) Or (DELIVERABLE.Invoiced_Date Is Null),DELIVERABLE.HQ_App_Date,DELIVERABLE.Invoiced_Date))) AS Test_Date
FROM TASK_ORDER INNER JOIN DELIVERABLE ON TASK_ORDER.[Cont_#]=DELIVERABLE.[Cont_#]

I have three date fields in a table and when I run the query to create a report I would like to sort my records based on the earliest date across the three fields for each record. My answer was to write an IIF statement that would find the earliest date between the three date fields in each record and populate a fourth date field with that earliest date called "Test_Date". Then I could sort my report based on the fourth date field which is the earliest date for each record. If "Rec_Date" or "Invoiced_Date" are the earliest, this query works just fine. However, if "HQ_App_Date" is the earliest date, I get a null value in the "Test_Date" field I'm trying to populate. Any suggestions? Thanks all!

 

[lupins46]

The logic of the iif is :
IIf((x) Or , IIf((a) Or (b), AA, IIf((c) Or (d), BB, CC)) ) AS Test_Date

This is missing a value for the first test being false
before the final ')' . (x is false and y is false)

 

[pete1505]

That's what I figured, but I needed another set of eyes to look at it. A co-worker wrote the code, and I've been modifying it. Problem is, we both though that the last IIF statement would create the value (become the "else") if the first IIF statement conditions were false. Thanks for your insight....could you give me a little more and suggest the code that I need to add to make this work? I still don't quite understand why the last part of the code does not give me a value if the first part is false. I've been looking at this stuff for too long!

 

[pete1505]

Nevermind, I re-read your post and figured out what you meant. I needed to add the following before the final ')' ",DELIVERABLE.HQ_App_Date)". Now it works just fine. Thanks for stearing me in the right direction!

 

[r937]

please do not cross-post

see

http://www.tek-tips.com/viewthread.cfm?qid=990830

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[pete1505]

I didn't mean to, and I won't in the future. I'm new (as of this morning) and I didn't know the proper forum to post this question. After I posted in the first forum I found this one which looked more promising for my question. Thanks for the tip though.

 

[r937]

well i would've replied here if i had seen this one first

as it is, it looks like i spent my time writing a solution to your problem for nothing

if you should ever accidentally do this again, then please put a link into one of the posts, pointing to the other post, like i did above



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[pete1505]

Will do, sorry if I wasted your time, but thanks for your consideration!

 

[lupins46]

IIf((x) Or , IIf((a) Or (b), AA, IIf((c) Or (d), BB, CC)) ) AS Test_Date

The third IIF is a complete expression
IIf((c) Or (d), BB, CC)
Lets say the result of this is XX.
The second IIF is then a complete statement:
IIf((a) Or (b), AA, XX)
Lets say this produces YY.
But the first iif is now:
IIf((x) Or , YY)
so it doesn't have an else value.

I think that trying to do this using IIF is pretty difficult and you would be better off with a function.

You can use something like:

Public Function MyMin(a ,b ,c ,d )as date
mymin = dateserial(2999,12,31)
if a<mymin then mymin = a
if b<mymin then mymin = b
if c<mymin then mymin = c
if d<mymin then mymin = d
end function

 

[r937]

or you can try my solution

the IIF only needs one level of nesting



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

..., DELIVERABLE.Comments
,IIf((DELIVERABLE.Rec_Date<=DELIVERABLE.HQ_App_Date Or IsNull(DELIVERABLE.HQ_App_Date))
AND (DELIVERABLE.Rec_Date<=DELIVERABLE.Invoiced_Date Or IsNull(DELIVERABLE.Invoiced_Date)),DELIVERABLE.Rec_Date
,IIf((DELIVERABLE.HQ_App_Date<=DELIVERABLE.Invoiced_Date Or IsNull(DELIVERABLE.Invoiced_Date))
AND (DELIVERABLE.HQ_App_Date<=DELIVERABLE.Rec_Date Or IsNull(DELIVERABLE.Rec_Date)),DELIVERABLE.HQ_App_Date
,DELIVERABLE.Invoiced_Date)) AS Test_Date
FROM ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244