Negative month returns

[HappyGala]

Hi everyone,

Tried using this SQL below

SELECT 
   tblIncident.DateObtained, 
   tblIncident.DateOfExpiry, 
   IIf([DateObtained] Is Null 
      Or [DateOfExpiry] Is Null,
         "",
         DateDiff("yyyy",
            [DateObtained],
            [DateOfExpiry])-
               IIf(Format([DateObtained],"mmdd")>
                  Format([DateOfExpiry],
                     "mmdd"),1,0)) 
   AS Years, 
   IIf([DateObtained] Is Null 
      Or [DateOfExpiry] Is Null,
         "",
         IIf(Day([DateObtained])<=Day([DateOfExpiry]),
            DateDiff("m",[DateObtained],[DateOfExpiry])
               -[Years]*12,
            DateDiff("m",[DateObtained],[DateOfExpiry])-[Years]*12-1)) 
   AS Months, 
   IIf([DateObtained] Is Null 
      Or [DateOfExpiry] Is Null,
         "",
         DateDiff("d",DateAdd("m",[Months],
            DateAdd("yyyy",[Years],[DateObtained])),
            [DateOfExpiry])) 
   AS Days 
FROM tblIncident;

A record contains
5/8/2005 - Date1
31/8/1956 - Date2

Date2 - Date1
The query returns me this result:
49 Yrs, -1 Mth, 5 Days

The negative value will appear whenever both date contain same month and date1 day is smaller than date2 days.

Any kind soul knows how to improve this sql.

Thanks very much in advance.

[zmbd]

HappyGala;
That's alot of nested IIF, yea.

Here's what you do...
Take two dates you've had issues with... plug them into your SQL and by hand evaluate the IIF and calcs...

Once you do that, the error in the calculations will become very obvious.

[twinnyfo]

HappyGala,

Oddly enough, when I use your SQL, here are the results I get:

DateObtained  DateExpiry  Results
5/8/2005      31/8/1956   -49, 0, 26
31/8/1956     5/8/2005    48, 11, 5
 

Which, appears to work....