SQL Code for Access Database Query 1

[pete1505]

What is wrong with this Code:

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!

 

[r937]

try this --

select T.C_Name
     , T.CO_Name
     , T.[Cont_#]
     , T.[TO_#]
     , D.[Del_#]
     , D.[Invoiced Amount]
     , D.Rec_Date
     , D.HQ_App_Date
     , D.Invoiced_Date
     , D.KDF_Approval
     , D.R_Drive_Date
     , D.DB_Update_Date
     , D.Website_Date
     , D.Comments
     , iif( D.Rec_Date <= D.HQ_App_Date 
        and D.Rec_Date <= D.Invoiced_Date
          , D.Rec_Date 
          , iif( D.HQ_App_Date <= D.Rec_Date 
             and D.HQ_App_Date <= D.Invoiced_Date 
               , D.HQ_App_Date 
               , D.Invoiced_Date ) 
          ) as Test_Date
  from TASK_ORDER as T
inner 
  join DELIVERABLE as D
    on T.[Cont_#]
     = D.[Cont_#]
order
    by 15

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