Old Problem Re-Occuring

Sep 27, 2006

I 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];

View Replies


ADVERTISEMENT

Why Is Locking Occuring?

Feb 8, 2005

I have a fixed length .txt which I am linking into a MS Access 2002 d/b.
My options are set to :
default open mode -shared
default record locking - no locks
Open database using record level locking is unchecked
the (datasheet) form is set to:
allow edits/deletions/additions - no
recordset type - snapshot
record locks - no locks
the text box objects are set to:
enabled - no
locked - no

I have no requery actions associated with any events at all.

My problem is that while the form is active I am unable to open the source .txt and if it is already open I cannot save it. There is obviosuly a lock occuring on the .txt object - how can I prevent it occuring?

thanks!

Gordon

View 2 Replies View Related

Calculating The Most Occuring Record In A Table.

Mar 10, 2008

I have a Database that contains a number Albums and Artists. I also have a table that contains orders from customers for these albms. Basically i want to track which album and artist is bought most? Can any one help?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved