Old Problem Re-Occuring
Sep 27, 2006I have posted this once before, but didn't get a response that really helped me out. I have a series of complex calculations on several queries that ultimately point me to two calculated cells. When it comes down to the end, it's essentially this simple. A + B = C; A - C = D; so C + D = A. BUT when I'm making this calculation in the query, the last column, which should be A - C = D, Access zero's out C and the full amount is showing up in D. So essentially, A = D. Which this should never be the case. We've been working off an excel spreadsheet but transferring to Access, so I know the logic is there, but can't get the very last thing to work. 98% of the time, D is going to be $0. But the 2% is really throwing me off. Here is my SQL of the query. The section separated at the bottom is my troubled spot. If needed I can zip up the file if it helps. I have been battling this situation for a month now and written it several different ways. Any help would be greatly GREATLY appreciated.
Thanks,
Jason
PARAMETERS [Aging Date] DateTime, [Performance Date] DateTime;
SELECT [Trend - Detail Query MI Category].[Reporting Entity], [Trend - Detail Query MI Category].[Due Date], [Trend - Detail Query MI Category].[Ifs Inv From Date], [Trend - Detail Query MI Category].[Ifs Inv To Date], [Trend - Detail Query MI Category].[Ifs Balance Due], DateDiff("d",[Due Date],[Aging Date]) AS Aging, DateDiff("d",[Ifs Inv From Date],[Ifs Inv To Date]+1) AS [Period of Performance Days], IIf([Ifs Inv From Date]>[Aging Date],0,IIf([Ifs Inv To Date]<[Performance Date],[Period of Performance Days],([Performance Date]-[Ifs Inv To Date]))) AS [Accrual Days], [Period of Performance Days]-[Accrual Days] AS [Var Days],
[Ifs Balance Due]*([Accrual Days][Period of Performance Days]) AS [Accrual $], [Ifs Balance Due]-[Accrual $] AS Unearned
FROM [Trend - Detail Query MI Category];