I'm trying to get a query, or anything in Access really, that I can enter a date and it will tell me Depreciation as of that date. I have the purchase cost and also the number of years to depreciate. I also have the depreciation amounts as of September 30, 2005, but I'm not sure if that information is helpful or just confusing my calculation. I tried to take the date entered using [EnterDate] then subtracting #9/30/2005# to get years remaining to depreciate but Assess didn't like that. Any help would be greatly appreciated. Thanks
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Help with Access programming, Depreciation
- Thread starter RandDsnow
- Start date
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
Hi
This should be easy but we will need some more information. Can you outline the method of calculating the depreciation value? Also, what other data is there? Have you recorded the original purchase date, depreciation rate and depreciation method in the table? Lastly, what do you want your query to return?
Andrew
This should be easy but we will need some more information. Can you outline the method of calculating the depreciation value? Also, what other data is there? Have you recorded the original purchase date, depreciation rate and depreciation method in the table? Lastly, what do you want your query to return?
Andrew
Upvote
0
Thank for the reply. I have original purchase date, original Purchase cost, date sold (if applicable), Selling price (again if applicable), the method (we only take straight line depreciation), the number of years to depreciate, I also have depreciation as of 9/30/2005, and net book value at 9/30/05.
Simple depreciation is calculated by taking the purchase price, minus salvage value, divided by periods/number of years to depreciate.
I hope this answers you question, I sure appreciate your help.
Simple depreciation is calculated by taking the purchase price, minus salvage value, divided by periods/number of years to depreciate.
I hope this answers you question, I sure appreciate your help.
Upvote
0
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
Hi
Unless you are going to store the depreciation values for each month or year, then this would be easier to calculate 'on the fly' so I'm not sure how much value we should place on the values as at 30 Sep 2005. That said, we do want the values calculated on the fly to correspond with your existing values so I have a few more questions.
1. Is the 'years to depreciate' field a whole number? Or do you have assets with say 5.5 years to depreciate?
2. What value do you want for the first month of depreciation for a new asset? Do you want either a) a whole month, b) half a month, c) half a month if it was purchased in the latter half of the month or a whle month if it was purchased in the first half ot he month, d) nothing or e) a pro-rated value depending on the number of depreciation days versus the number of days in the month?
3. The 'salvage value' you mentioned - I presume you want this value deducted from the original cost when calculating the periodic depreciation charges?
4. If you enter a date into the query, what do you want to see? The depreciation for the month? Or the year to date? If so, what is your financial year? (30 Sep?) Or do you want to see the accumulated depreciation? Or all three? And if you enter a date of 15 March 2006, do you want to see a depreciation value for the entire month or the part month?
5. Lastly, do you want the values as at 30 Sep 2005 incorporated into the query? Or would you rather recalculate everything from scratch? If you want to include the 30 Sep values, are these values stored in the asset table or another table?
As I said earlier, this shouldn't be difficult but I'd rather have all of the information up front rather than guessing and getting it wrong.
Andrew
Unless you are going to store the depreciation values for each month or year, then this would be easier to calculate 'on the fly' so I'm not sure how much value we should place on the values as at 30 Sep 2005. That said, we do want the values calculated on the fly to correspond with your existing values so I have a few more questions.
1. Is the 'years to depreciate' field a whole number? Or do you have assets with say 5.5 years to depreciate?
2. What value do you want for the first month of depreciation for a new asset? Do you want either a) a whole month, b) half a month, c) half a month if it was purchased in the latter half of the month or a whle month if it was purchased in the first half ot he month, d) nothing or e) a pro-rated value depending on the number of depreciation days versus the number of days in the month?
3. The 'salvage value' you mentioned - I presume you want this value deducted from the original cost when calculating the periodic depreciation charges?
4. If you enter a date into the query, what do you want to see? The depreciation for the month? Or the year to date? If so, what is your financial year? (30 Sep?) Or do you want to see the accumulated depreciation? Or all three? And if you enter a date of 15 March 2006, do you want to see a depreciation value for the entire month or the part month?
5. Lastly, do you want the values as at 30 Sep 2005 incorporated into the query? Or would you rather recalculate everything from scratch? If you want to include the 30 Sep values, are these values stored in the asset table or another table?
As I said earlier, this shouldn't be difficult but I'd rather have all of the information up front rather than guessing and getting it wrong.
Andrew
Upvote
0
Andrew,
Thanks so much for your responce.
Q. 1. Is the 'years to depreciate' field a whole number? Or do you have assets with say 5.5 years to depreciate?
A. Years to depreciate are in whole numbers
Q. 2. What value do you want for the first month of depreciation for a new asset? Do you want either a) a whole month, b) half a month, c) half a month if it was purchased in the latter half of the month or a whle month if it was purchased in the first half ot he month, d) nothing or e) a pro-rated value depending on the number of depreciation days versus the number of days in the month?
A. b) half a month
Q. 3. The 'salvage value' you mentioned - I presume you want this value deducted from the original cost when calculating the periodic depreciation charges?
A. That is correct
Q. 4. If you enter a date into the query, what do you want to see? The depreciation for the month? Or the year to date? If so, what is your financial year? (30 Sep?) Or do you want to see the accumulated depreciation? Or all three? And if you enter a date of 15 March 2006, do you want to see a depreciation value for the entire month or the part month?
A. The fiscal year end is Sept 30th, I would like it to show depreciation for the month, year to date, and accumulated depreciation. So I guess I would like to see all three. If I entered a date like 15 March 2006 I would like to see depreciation for the entire month of March.
Q 5. Lastly, do you want the values as at 30 Sep 2005 incorporated into the query? Or would you rather recalculate everything from scratch? If you want to include the 30 Sep values, are these values stored in the asset table or another table?
A. The values at 30 Sept 2005 are included in the asset table. If possible I would like to have them incorporated, because they have been what was reported on our financial audit.
Thanks again for your help.
Ryan
Thanks so much for your responce.
Q. 1. Is the 'years to depreciate' field a whole number? Or do you have assets with say 5.5 years to depreciate?
A. Years to depreciate are in whole numbers
Q. 2. What value do you want for the first month of depreciation for a new asset? Do you want either a) a whole month, b) half a month, c) half a month if it was purchased in the latter half of the month or a whle month if it was purchased in the first half ot he month, d) nothing or e) a pro-rated value depending on the number of depreciation days versus the number of days in the month?
A. b) half a month
Q. 3. The 'salvage value' you mentioned - I presume you want this value deducted from the original cost when calculating the periodic depreciation charges?
A. That is correct
Q. 4. If you enter a date into the query, what do you want to see? The depreciation for the month? Or the year to date? If so, what is your financial year? (30 Sep?) Or do you want to see the accumulated depreciation? Or all three? And if you enter a date of 15 March 2006, do you want to see a depreciation value for the entire month or the part month?
A. The fiscal year end is Sept 30th, I would like it to show depreciation for the month, year to date, and accumulated depreciation. So I guess I would like to see all three. If I entered a date like 15 March 2006 I would like to see depreciation for the entire month of March.
Q 5. Lastly, do you want the values as at 30 Sep 2005 incorporated into the query? Or would you rather recalculate everything from scratch? If you want to include the 30 Sep values, are these values stored in the asset table or another table?
A. The values at 30 Sept 2005 are included in the asset table. If possible I would like to have them incorporated, because they have been what was reported on our financial audit.
Thanks again for your help.
Ryan
Upvote
0
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
Hi Ryan
Apologies for not getting back to you sooner. I had already worked out how to do this previously but am having trouble converting the calculations to account for the half month of depreciation for the first month. I will give you what I have so far and we can work on the missing parts later. (This would be so much easier in Excel given Excel has Max and Min functions which aren't available in Access to the same degree).
I have used the following field names in my table (they pretty much speak for themselves) :
Purch_Date
Purch_Cost
Sale_Date
Sale_Value
Years_to_Depreciate
Salvage_Value
30SepAccDepn
Where you have differing field names, make sure you substitute your field names in your query. This is the logic for the monthly depreciation charge (not the actual charge, that comes later):
MonthlyDepn: Int(0.5 + (100 * ((Nz([Purch_Cost]) - Nz([Salvage_Value])) / [Years_to_Depreciate]) / 12)) / 100
This rounds the depreciation value up to the nearest cent.
The number of depreciation months remaining can be calculated using this:
MonthsToGo: Int((IIf([Purch_Date] <= [MonthEnd] And (IsNull([Sale_Date]) Or [Sale_Date] > [MonthEnd]), ([Purch_Cost] - nz([Salvage_Value]) - nz([30SepAccDepn])) / [MonthlyDepn], IIf([Sale_Date] > [LastYearEnd], DateDiff("m", #30/09/2005#, [Sale_Date]), 0))) + 0.99)
To get the current month end, use this:
MonthEnd: DateAdd("d", -1, DateSerial(Year(DateAdd("m", 1, [Enter Month End])), Month(DateAdd("m", 1, [Enter Month End])), 1))
and the current year end, use this:
ThisYearEnd: IIf(Month([Enter Month End]) > 9, DateSerial(1 + Year([Enter Month End]), 9, 30), DateSerial(Year([Enter Month End]), 9, 30))
and the previous year end, use this:
LastYearEnd: DateAdd("yyyy", -1, [ThisYearEnd])
To work out the last depreciation date (assuming there is no depreciation in the month an asset is sold), use this:
LastDepnDate: IIf(IsNull([Sale_Date]), DateAdd("m", [MonthsToGo], #30/09/2005#), IIf([Sale_Date] > DateAdd("m", [MonthsToGo], #30/09/2005#), DateAdd("m", [MonthsToGo], #30/09/2005#), DateAdd("d", -Day([Sale_Date]), [Sale_Date])))
The monthly depreciation charge is calculated using this:
MonthDepnCharge : IIf([Purch_Date]>[MonthEnd] Or [LastDepnDate] < [MonthEnd], 0, IIf([Purch_Date] <= DateAdd("d", -Day([MonthEnd]), [MonthEnd]) And [LastDepnDate] > [MonthEnd], [MonthlyDepn], Int(([MonthlyDepn] * 50) +0.5)/100)) + IIf([Purch_Date] <= DateAdd("d", -Day([MonthEnd]), [MonthEnd]) And IsNull([Sale_Date]) And [LastDepnDate] <= [MonthEnd] And [LastDepnDate] > DateAdd("d", -Day([MonthEnd]), [MonthEnd]), [Purch_Cost]- nz([30SepAccDepn])- (([MonthsToGo]-1) * [MonthlyDepn]),0)
Conceptually the YTD depreciation is the Datediff less 1 of the months between the Max of (Purch_Date and LastYearEnd) and the Min of (Start of Current Month and LastDepnDate) * the Monthly Depreciation Charge + the current months depn charge, after excluding certain records. I'm currently battling to adjust this calculation for assets bought or sold in the current financial year, but will get back to you when I have solved it.
In any case, give the above calculations a test to make sure they are providing the values you expect. Once these parts are working ok we can work on the rest of the numbers (i.e. YTD depn and Accum depn). If you want an analysis of the costs (e.g. opening cost, YTD additions, YTD sales and closing cost etc.) let me know because I already have these values worked out.
HTH, Andrew
Apologies for not getting back to you sooner. I had already worked out how to do this previously but am having trouble converting the calculations to account for the half month of depreciation for the first month. I will give you what I have so far and we can work on the missing parts later. (This would be so much easier in Excel given Excel has Max and Min functions which aren't available in Access to the same degree).
I have used the following field names in my table (they pretty much speak for themselves) :
Purch_Date
Purch_Cost
Sale_Date
Sale_Value
Years_to_Depreciate
Salvage_Value
30SepAccDepn
Where you have differing field names, make sure you substitute your field names in your query. This is the logic for the monthly depreciation charge (not the actual charge, that comes later):
MonthlyDepn: Int(0.5 + (100 * ((Nz([Purch_Cost]) - Nz([Salvage_Value])) / [Years_to_Depreciate]) / 12)) / 100
This rounds the depreciation value up to the nearest cent.
The number of depreciation months remaining can be calculated using this:
MonthsToGo: Int((IIf([Purch_Date] <= [MonthEnd] And (IsNull([Sale_Date]) Or [Sale_Date] > [MonthEnd]), ([Purch_Cost] - nz([Salvage_Value]) - nz([30SepAccDepn])) / [MonthlyDepn], IIf([Sale_Date] > [LastYearEnd], DateDiff("m", #30/09/2005#, [Sale_Date]), 0))) + 0.99)
To get the current month end, use this:
MonthEnd: DateAdd("d", -1, DateSerial(Year(DateAdd("m", 1, [Enter Month End])), Month(DateAdd("m", 1, [Enter Month End])), 1))
and the current year end, use this:
ThisYearEnd: IIf(Month([Enter Month End]) > 9, DateSerial(1 + Year([Enter Month End]), 9, 30), DateSerial(Year([Enter Month End]), 9, 30))
and the previous year end, use this:
LastYearEnd: DateAdd("yyyy", -1, [ThisYearEnd])
To work out the last depreciation date (assuming there is no depreciation in the month an asset is sold), use this:
LastDepnDate: IIf(IsNull([Sale_Date]), DateAdd("m", [MonthsToGo], #30/09/2005#), IIf([Sale_Date] > DateAdd("m", [MonthsToGo], #30/09/2005#), DateAdd("m", [MonthsToGo], #30/09/2005#), DateAdd("d", -Day([Sale_Date]), [Sale_Date])))
The monthly depreciation charge is calculated using this:
MonthDepnCharge : IIf([Purch_Date]>[MonthEnd] Or [LastDepnDate] < [MonthEnd], 0, IIf([Purch_Date] <= DateAdd("d", -Day([MonthEnd]), [MonthEnd]) And [LastDepnDate] > [MonthEnd], [MonthlyDepn], Int(([MonthlyDepn] * 50) +0.5)/100)) + IIf([Purch_Date] <= DateAdd("d", -Day([MonthEnd]), [MonthEnd]) And IsNull([Sale_Date]) And [LastDepnDate] <= [MonthEnd] And [LastDepnDate] > DateAdd("d", -Day([MonthEnd]), [MonthEnd]), [Purch_Cost]- nz([30SepAccDepn])- (([MonthsToGo]-1) * [MonthlyDepn]),0)
Conceptually the YTD depreciation is the Datediff less 1 of the months between the Max of (Purch_Date and LastYearEnd) and the Min of (Start of Current Month and LastDepnDate) * the Monthly Depreciation Charge + the current months depn charge, after excluding certain records. I'm currently battling to adjust this calculation for assets bought or sold in the current financial year, but will get back to you when I have solved it.
In any case, give the above calculations a test to make sure they are providing the values you expect. Once these parts are working ok we can work on the rest of the numbers (i.e. YTD depn and Accum depn). If you want an analysis of the costs (e.g. opening cost, YTD additions, YTD sales and closing cost etc.) let me know because I already have these values worked out.
HTH, Andrew
Upvote
0
Andrew,
Thanks so much for your help. I put the information you gave me in to a query and it appears that every thing is working, the MonthsToGo is from 30/09/2005 correct? If you have the information for opening cost, YTD additions, YTD sales and closing cost, I would like to have them too. Let me know if you need any more information.
Thanks again for all the help.
Ryan
Thanks so much for your help. I put the information you gave me in to a query and it appears that every thing is working, the MonthsToGo is from 30/09/2005 correct? If you have the information for opening cost, YTD additions, YTD sales and closing cost, I would like to have them too. Let me know if you need any more information.
Thanks again for all the help.
Ryan
Upvote
0
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
Hi Ryan
Yes the 'months to go' value is from 30 Sep 2005. Try a few combinations of month end values with differing purchase and sale dates to make sure it is working correctly. I have only done very limited testing on this. I still haven't got to the YTD and acc depn values yet.
Here are some of the other values :
OpeningCost: IIf([Purch_Date] <= [LastYearEnd] And (IsNull([Sale_Date]) Or [Sale_Date] > [LastYearEnd]), [Purch_Cost], 0)
Additions: IIf([Purch_Date]>[LastYearEnd] And [Purch_Date] <= [MonthEnd], [Purch_Cost], 0)
SalesCost: IIf([Sale_Date]>[LastYearEnd] And [Sale_Date] <= [MonthEnd], -[Purch_Cost], 0)
ClosingCost: [OpeningCost]+[Additions]+[SalesCost]
HTH, Andrew
Yes the 'months to go' value is from 30 Sep 2005. Try a few combinations of month end values with differing purchase and sale dates to make sure it is working correctly. I have only done very limited testing on this. I still haven't got to the YTD and acc depn values yet.
Here are some of the other values :
OpeningCost: IIf([Purch_Date] <= [LastYearEnd] And (IsNull([Sale_Date]) Or [Sale_Date] > [LastYearEnd]), [Purch_Cost], 0)
Additions: IIf([Purch_Date]>[LastYearEnd] And [Purch_Date] <= [MonthEnd], [Purch_Cost], 0)
SalesCost: IIf([Sale_Date]>[LastYearEnd] And [Sale_Date] <= [MonthEnd], -[Purch_Cost], 0)
ClosingCost: [OpeningCost]+[Additions]+[SalesCost]
HTH, Andrew
Upvote
0
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
Hello Ryan
I think I have the formulae for you. I made a mistake in the previous calculation of the 'LastDepnDate' value. Use this calculation instead :
LastDepnDate: IIf(IsNull([Sale_Date]), DateAdd("m", [MonthsToGo], IIf([Purch_Date]<#30/09/2005#, #30/09/2005#, [Purch_Date])), IIf([Sale_Date] > DateAdd("m", [MonthsToGo], IIf([Purch_Date] < #30/09/2005#, #30/09/2005#, [Purch_Date])), DateAdd("m", [MonthsToGo], IIf([Purch_Date] < #30/09/2005#, #30/09/2005#, [Purch_Date])), DateAdd("d", -Day([Sale_Date]), [Sale_Date])))
For the opening depreciation value, use this:
OpeningDepn: IIf([Purch_Date] > [LastYearEnd], 0, IIf([Sale_Date] <= [LastYearEnd], 0, IIf([LastDepnDate] <= [LastYearEnd], [Purch_Cost], IIf([Purch_Date] < #30/09/2005#, [30SepAccDepn] + ([MonthlyDepn] * DateDiff("m", #30/09/2005#, [LastYearEnd])), [MonthlyDepn] * (0.5 + DateDiff("m", [Purch_Date], [LastYearEnd]))))))
For the month to date depreciation charge, use this:
MonthDepnCharge: IIf([Purch_Date] > [MonthEnd] Or [LastDepnDate] < [MonthEnd], 0, IIf([Purch_Date] <= DateAdd("d", -Day([MonthEnd]), [MonthEnd]) And [LastDepnDate] > [MonthEnd], [MonthlyDepn], Int(([MonthlyDepn] * 50) +0.5)/100)) + IIf([Purch_Date] <= DateAdd("d", -Day([MonthEnd]), [MonthEnd]) And IsNull([Sale_Date]) And [LastDepnDate] <= [MonthEnd] And [LastDepnDate] > DateAdd("d", -Day([MonthEnd]), [MonthEnd]), [Purch_Cost] - nz([30SepAccDepn]) - (([MonthsToGo]-1) * [MonthlyDepn]),0)
For the year to date depreciation, use this:
YTDDepn: [MonthDepnCharge] + IIf([Purch_Date] > DateAdd("d", -Day([MonthEnd]), [MonthEnd]) Or [LastDepnDate] <= [LastYearEnd], 0, IIf([LastDepnDate] > DateAdd("d", -Day([MonthEnd]), [MonthEnd]), IIf([Purch_Date] <= [LastYearEnd], [MonthlyDepn] * DateDiff("m", DateAdd("d", 1, [LastYearEnd]), DateAdd("d", 1-Day([MonthEnd]), [MonthEnd])), [MonthlyDepn] * (0.5+DateDiff("m", [Purch_Date], DateAdd("d", -Day([MonthEnd]), [MonthEnd])))), IIf([Purch_Date] <= [LastYearEnd], IIf(IsNull([Sale_Date]), [Purch_Cost]-[OpeningDepn], [MonthlyDepn] * DateDiff("m", [LastYearEnd], [LastDepnDate])), IIf(IsNull([Sale_Date]), [Purch_Cost], IIf(Month([Sale_Date]) = Month([Purch_Date]), 0, [MonthlyDepn] * (0.5+DateDiff("m", [Purch_Date], DateAdd("d", -Day([Sale_Date]), [Sale_Date]))))))))
For the depreciation to reverse on the sale of an asset, use this:
SalesDepn: IIf(IsNull([Sale_Date]), 0, IIf([Sale_Date] > [LastYearEnd] And [Sale_Date] <= [MonthEnd], -Nz([OpeningDepn])-Nz([YTDDepn]),0))
And the accumulated depreciation is this:
AccumDepn: [OpeningDepn] + [YTDDepn] + [SalesDepn]
These calculations have been designed for any month or 30 Sep year end in mind (post 2005), and they incorporate the calculations I previously supplied. Please note the 'last depreciation date' calculation has been changed (see above).
If you end up with a 'too complex' error then you may need to split the cost and depreciation calculations into 2 different queries.
HTH, Andrew
I think I have the formulae for you. I made a mistake in the previous calculation of the 'LastDepnDate' value. Use this calculation instead :
LastDepnDate: IIf(IsNull([Sale_Date]), DateAdd("m", [MonthsToGo], IIf([Purch_Date]<#30/09/2005#, #30/09/2005#, [Purch_Date])), IIf([Sale_Date] > DateAdd("m", [MonthsToGo], IIf([Purch_Date] < #30/09/2005#, #30/09/2005#, [Purch_Date])), DateAdd("m", [MonthsToGo], IIf([Purch_Date] < #30/09/2005#, #30/09/2005#, [Purch_Date])), DateAdd("d", -Day([Sale_Date]), [Sale_Date])))
For the opening depreciation value, use this:
OpeningDepn: IIf([Purch_Date] > [LastYearEnd], 0, IIf([Sale_Date] <= [LastYearEnd], 0, IIf([LastDepnDate] <= [LastYearEnd], [Purch_Cost], IIf([Purch_Date] < #30/09/2005#, [30SepAccDepn] + ([MonthlyDepn] * DateDiff("m", #30/09/2005#, [LastYearEnd])), [MonthlyDepn] * (0.5 + DateDiff("m", [Purch_Date], [LastYearEnd]))))))
For the month to date depreciation charge, use this:
MonthDepnCharge: IIf([Purch_Date] > [MonthEnd] Or [LastDepnDate] < [MonthEnd], 0, IIf([Purch_Date] <= DateAdd("d", -Day([MonthEnd]), [MonthEnd]) And [LastDepnDate] > [MonthEnd], [MonthlyDepn], Int(([MonthlyDepn] * 50) +0.5)/100)) + IIf([Purch_Date] <= DateAdd("d", -Day([MonthEnd]), [MonthEnd]) And IsNull([Sale_Date]) And [LastDepnDate] <= [MonthEnd] And [LastDepnDate] > DateAdd("d", -Day([MonthEnd]), [MonthEnd]), [Purch_Cost] - nz([30SepAccDepn]) - (([MonthsToGo]-1) * [MonthlyDepn]),0)
For the year to date depreciation, use this:
YTDDepn: [MonthDepnCharge] + IIf([Purch_Date] > DateAdd("d", -Day([MonthEnd]), [MonthEnd]) Or [LastDepnDate] <= [LastYearEnd], 0, IIf([LastDepnDate] > DateAdd("d", -Day([MonthEnd]), [MonthEnd]), IIf([Purch_Date] <= [LastYearEnd], [MonthlyDepn] * DateDiff("m", DateAdd("d", 1, [LastYearEnd]), DateAdd("d", 1-Day([MonthEnd]), [MonthEnd])), [MonthlyDepn] * (0.5+DateDiff("m", [Purch_Date], DateAdd("d", -Day([MonthEnd]), [MonthEnd])))), IIf([Purch_Date] <= [LastYearEnd], IIf(IsNull([Sale_Date]), [Purch_Cost]-[OpeningDepn], [MonthlyDepn] * DateDiff("m", [LastYearEnd], [LastDepnDate])), IIf(IsNull([Sale_Date]), [Purch_Cost], IIf(Month([Sale_Date]) = Month([Purch_Date]), 0, [MonthlyDepn] * (0.5+DateDiff("m", [Purch_Date], DateAdd("d", -Day([Sale_Date]), [Sale_Date]))))))))
For the depreciation to reverse on the sale of an asset, use this:
SalesDepn: IIf(IsNull([Sale_Date]), 0, IIf([Sale_Date] > [LastYearEnd] And [Sale_Date] <= [MonthEnd], -Nz([OpeningDepn])-Nz([YTDDepn]),0))
And the accumulated depreciation is this:
AccumDepn: [OpeningDepn] + [YTDDepn] + [SalesDepn]
These calculations have been designed for any month or 30 Sep year end in mind (post 2005), and they incorporate the calculations I previously supplied. Please note the 'last depreciation date' calculation has been changed (see above).
If you end up with a 'too complex' error then you may need to split the cost and depreciation calculations into 2 different queries.
HTH, Andrew
Upvote
0
Similar threads
- Solved
