This is a toughie | auto-calculate depreciation | populate unbound

[JK]

Using Access 2003, I’m trying to have a db auto calculate an assets
depreciation values over a user specified time period. I have a main form
“frmAssets†a subform “frmAssetsSF†and then another subform inside the last
subform “Depreciation Subform.â€

Using the template example for recurring events, the subform is populated
but the data is not bound to a table. The only time the data is bound is if
it’s an exception. I like this concept and would like to be able to calculate
the depreciation values and then display them in a subform without having to
store the values in a table.

I found the following MS example online. It’s a great bit of code. It asks
for the initial cost of the asset, salvage value, and the assets useful life
in months. Then, you specify the year you would like to calculate, hit enter,
and the depreciation value is displayed. This is not straight line DP; the
values are different from year to year.

On subform “frmAssetsSF†I have the following fields that can be pointed to:

[PurchaseDate]
[PurchasePrice]
[SalvageValue]
[DepreciableLife] – months

If the purchase date is: 12/15/2008
And the purchase price is: $5,000
And the salvage value is: $100
And the depreciable life is: 60 months (5 years)

The following should be displayed in the subform:

Date: Depreciation Amount:
12/15/2009 $2,000.00
12/15/2009 $1,200.00
12/15/2010 $720.00
12/15/2011 $432.00
12/15/2012 $259.20


I know this is a lot to ask. But if someone could point me in the right
direction I would greatly appreciate it.


Private Sub cmdDepreciation_Click()

Dim lngErr As Long, strError As String
Dim Fmt, InitCost, SalvageVal, MonthLife, LifeTime, DepYear, Depr
Const YRMOS = 12 ' Number of months in a year.

On Error GoTo Save_Error

Fmt = "###,##0.00"
InitCost = InputBox("What's the initial cost of the asset?")
SalvageVal = InputBox("Enter the asset's value at end of its life.")
MonthLife = InputBox("What's the asset's useful life in months?")

Do While MonthLife < YRMOS ' Ensure period is >= 1 year.
MsgBox "Asset life must be a year or more."
MonthLife = InputBox("What's the asset's useful life in months?")
Loop

LifeTime = MonthLife / YRMOS ' Convert months to years.
If LifeTime <> Int(MonthLife / YRMOS) Then
LifeTime = Int(LifeTime + 1) ' Round up to nearest year.
End If

DepYear = CInt(InputBox("Enter year for depreciation calculation."))
Do While DepYear < 1 Or DepYear > LifeTime
MsgBox "You must enter at least 1 but not more than " & LifeTime
DepYear = InputBox("Enter year for depreciation calculation.")
Loop
Depr = DDB(InitCost, SalvageVal, LifeTime, DepYear)
MsgBox "The depreciation for year " & DepYear & " is " & _
Format(Depr, Fmt) & "."

End Sub

 

[Tom van Stiphout]

If you look at this code, the most important line is the one that
calls the DDB function. You can call that yourself, for example in a
query. As you can see in the help file it takes 4 arguments. Assuming
you have a query with those 4 columns, you can create a fifth column
using:
myDDB: DDB(cost, salvage, life, period)

-Tom.
Microsoft Access MVP

Using Access 2003, I’m trying to have a db auto calculate an assets
depreciation values over a user specified time period. I have a main form
“frmAssets” a subform “frmAssetsSF” and then another subform inside the last
subform “Depreciation Subform.”

Using the template example for recurring events, the subform is populated
but the data is not bound to a table. The only time the data is bound is if
it’s an exception. I like this concept and would like to be able to calculate
the depreciation values and then display them in a subform without having to
store the values in a table.

I found the following MS example online. It’s a great bit of code. It asks
for the initial cost of the asset, salvage value, and the assets useful life
in months. Then, you specify the year you would like to calculate, hit enter,
and the depreciation value is displayed. This is not straight line DP; the
values are different from year to year.

On subform “frmAssetsSF” I have the following fields that can be pointed to:

[PurchaseDate]
[PurchasePrice]
[SalvageValue]
[DepreciableLife] – months

If the purchase date is: 12/15/2008
And the purchase price is: $5,000
And the salvage value is: $100
And the depreciable life is: 60 months (5 years)

The following should be displayed in the subform:

Date: Depreciation Amount:
12/15/2009 $2,000.00
12/15/2009 $1,200.00
12/15/2010 $720.00
12/15/2011 $432.00
12/15/2012 $259.20


I know this is a lot to ask. But if someone could point me in the right
direction I would greatly appreciate it.


Private Sub cmdDepreciation_Click()

Dim lngErr As Long, strError As String
Dim Fmt, InitCost, SalvageVal, MonthLife, LifeTime, DepYear, Depr
Const YRMOS = 12 ' Number of months in a year.

On Error GoTo Save_Error

Fmt = "###,##0.00"
InitCost = InputBox("What's the initial cost of the asset?")
SalvageVal = InputBox("Enter the asset's value at end of its life.")
MonthLife = InputBox("What's the asset's useful life in months?")

Do While MonthLife < YRMOS ' Ensure period is >= 1 year.
MsgBox "Asset life must be a year or more."
MonthLife = InputBox("What's the asset's useful life in months?")
Loop

LifeTime = MonthLife / YRMOS ' Convert months to years.
If LifeTime <> Int(MonthLife / YRMOS) Then
LifeTime = Int(LifeTime + 1) ' Round up to nearest year.
End If

DepYear = CInt(InputBox("Enter year for depreciation calculation."))
Do While DepYear < 1 Or DepYear > LifeTime
MsgBox "You must enter at least 1 but not more than " & LifeTime
DepYear = InputBox("Enter year for depreciation calculation.")
Loop
Depr = DDB(InitCost, SalvageVal, LifeTime, DepYear)
MsgBox "The depreciation for year " & DepYear & " is " & _
Format(Depr, Fmt) & "."

End Sub

 

[JK]

Actually, as I start creating the query, it's making more and more sense. I'm
stuck on at least one thing, however... This bit of code.

DepYear = CInt(InputBox("Enter year for depreciation calculation."))
Do While DepYear < 1 Or DepYear > LifeTime
MsgBox "You must enter at least 1 but not more than " & LifeTime
DepYear = InputBox("Enter year for depreciation calculation.")
Loop

If the person enters 60 months as the assets useful life in months, I need
the query to take that field [DepreciableLife] and divide it by 12. I would
then have the number 5 in a field for 5 years. Then, I need the query to
generate a depreciation date and value for each of the five years. I suppose
this is where someone would use an If statement if this were being done in
VB. Can this be done using SQL?

I guess something like this: LifeTime : ([DepreciableLife]/12)

Using your suggestion, I have the following in a query:
Depreciation : DDB([PurchasePrice],[SalvageValue],[DepreciableLife],[DepYear])

The only field I'm missing is [DepYear].

Do you see what I mean? There are a couple of things here that are over my
head. I will continue to keep trying new things and I'll look in the
discussion forum for new ideas. If anyone is reading this and is able to help
- I would really appreciate it. I know this is a lot to ask...

Thx!
Jason

If you look at this code, the most important line is the one that
calls the DDB function. You can call that yourself, for example in a
query. As you can see in the help file it takes 4 arguments. Assuming
you have a query with those 4 columns, you can create a fifth column
using:
myDDB: DDB(cost, salvage, life, period)

-Tom.
Microsoft Access MVP

Using Access 2003, I’m trying to have a db auto calculate an assets
depreciation values over a user specified time period. I have a main form
“frmAssets†a subform “frmAssetsSF†and then another subform inside the last
subform “Depreciation Subform.â€

Using the template example for recurring events, the subform is populated
but the data is not bound to a table. The only time the data is bound is if
it’s an exception. I like this concept and would like to be able to calculate
the depreciation values and then display them in a subform without having to
store the values in a table.

I found the following MS example online. It’s a great bit of code. It asks
for the initial cost of the asset, salvage value, and the assets useful life
in months. Then, you specify the year you would like to calculate, hit enter,
and the depreciation value is displayed. This is not straight line DP; the
values are different from year to year.

On subform “frmAssetsSF†I have the following fields that can be pointed to:

[PurchaseDate]
[PurchasePrice]
[SalvageValue]
[DepreciableLife] – months

If the purchase date is: 12/15/2008
And the purchase price is: $5,000
And the salvage value is: $100
And the depreciable life is: 60 months (5 years)

The following should be displayed in the subform:

Date: Depreciation Amount:
12/15/2009 $2,000.00
12/15/2009 $1,200.00
12/15/2010 $720.00
12/15/2011 $432.00
12/15/2012 $259.20


I know this is a lot to ask. But if someone could point me in the right
direction I would greatly appreciate it.


Private Sub cmdDepreciation_Click()

Dim lngErr As Long, strError As String
Dim Fmt, InitCost, SalvageVal, MonthLife, LifeTime, DepYear, Depr
Const YRMOS = 12 ' Number of months in a year.

On Error GoTo Save_Error

Fmt = "###,##0.00"
InitCost = InputBox("What's the initial cost of the asset?")
SalvageVal = InputBox("Enter the asset's value at end of its life.")
MonthLife = InputBox("What's the asset's useful life in months?")

Do While MonthLife < YRMOS ' Ensure period is >= 1 year.
MsgBox "Asset life must be a year or more."
MonthLife = InputBox("What's the asset's useful life in months?")
Loop

LifeTime = MonthLife / YRMOS ' Convert months to years.
If LifeTime <> Int(MonthLife / YRMOS) Then
LifeTime = Int(LifeTime + 1) ' Round up to nearest year.
End If

DepYear = CInt(InputBox("Enter year for depreciation calculation."))
Do While DepYear < 1 Or DepYear > LifeTime
MsgBox "You must enter at least 1 but not more than " & LifeTime
DepYear = InputBox("Enter year for depreciation calculation.")
Loop
Depr = DDB(InitCost, SalvageVal, LifeTime, DepYear)
MsgBox "The depreciation for year " & DepYear & " is " & _
Format(Depr, Fmt) & "."

End Sub

 

[JK]

I know the syntax is way off, but this is the query I have built so far. Am I
even close?

SELECT Depreciation.DepreciationID, Depreciation.AssetID,
Depreciation.DepreciationDate, Depreciation.DepreciationAmount,
Depreciation.PurchaseDate, Depreciation.PurchasePrice,
Depreciation.SalvageValue, Depreciation.DepreciableLife,
([DepreciableLife]/12) AS LifeTime, [LifeTime]+1 AS DepYear,
DDB([PurchasePrice],[SalvageValue],[DepreciableLife],[DepYear]) AS
Depreciation
FROM Depreciation
WHERE (((Depreciation.AssetID)=[Forms]![frmAssets]![ID]) AND
(([LifeTime]+1)<[LifeTime]));


Thx so much!

If you look at this code, the most important line is the one that
calls the DDB function. You can call that yourself, for example in a
query. As you can see in the help file it takes 4 arguments. Assuming
you have a query with those 4 columns, you can create a fifth column
using:
myDDB: DDB(cost, salvage, life, period)

-Tom.
Microsoft Access MVP

Using Access 2003, I’m trying to have a db auto calculate an assets
depreciation values over a user specified time period. I have a main form
“frmAssets†a subform “frmAssetsSF†and then another subform inside the last
subform “Depreciation Subform.â€

Using the template example for recurring events, the subform is populated
but the data is not bound to a table. The only time the data is bound is if
it’s an exception. I like this concept and would like to be able to calculate
the depreciation values and then display them in a subform without having to
store the values in a table.

I found the following MS example online. It’s a great bit of code. It asks
for the initial cost of the asset, salvage value, and the assets useful life
in months. Then, you specify the year you would like to calculate, hit enter,
and the depreciation value is displayed. This is not straight line DP; the
values are different from year to year.

On subform “frmAssetsSF†I have the following fields that can be pointed to:

[PurchaseDate]
[PurchasePrice]
[SalvageValue]
[DepreciableLife] – months

If the purchase date is: 12/15/2008
And the purchase price is: $5,000
And the salvage value is: $100
And the depreciable life is: 60 months (5 years)

The following should be displayed in the subform:

Date: Depreciation Amount:
12/15/2009 $2,000.00
12/15/2009 $1,200.00
12/15/2010 $720.00
12/15/2011 $432.00
12/15/2012 $259.20


I know this is a lot to ask. But if someone could point me in the right
direction I would greatly appreciate it.


Private Sub cmdDepreciation_Click()

Dim lngErr As Long, strError As String
Dim Fmt, InitCost, SalvageVal, MonthLife, LifeTime, DepYear, Depr
Const YRMOS = 12 ' Number of months in a year.

On Error GoTo Save_Error

Fmt = "###,##0.00"
InitCost = InputBox("What's the initial cost of the asset?")
SalvageVal = InputBox("Enter the asset's value at end of its life.")
MonthLife = InputBox("What's the asset's useful life in months?")

Do While MonthLife < YRMOS ' Ensure period is >= 1 year.
MsgBox "Asset life must be a year or more."
MonthLife = InputBox("What's the asset's useful life in months?")
Loop

LifeTime = MonthLife / YRMOS ' Convert months to years.
If LifeTime <> Int(MonthLife / YRMOS) Then
LifeTime = Int(LifeTime + 1) ' Round up to nearest year.
End If

DepYear = CInt(InputBox("Enter year for depreciation calculation."))
Do While DepYear < 1 Or DepYear > LifeTime
MsgBox "You must enter at least 1 but not more than " & LifeTime
DepYear = InputBox("Enter year for depreciation calculation.")
Loop
Depr = DDB(InitCost, SalvageVal, LifeTime, DepYear)
MsgBox "The depreciation for year " & DepYear & " is " & _
Format(Depr, Fmt) & "."

End Sub

 

[JK]

Holy crap, I think I did it! I never thought in a million years I would be
able to pull this off. This is what I did. I created two queries using the
Access Template Events as an example.

My only problem now; if I want to depreciate the asset over five years, the
first query actually generates six records, not five. I need to figure out
why. So, when I run the second query, the first record has #Error in the
myDDB field.

Just thought i'd followup with the solution. It's not working 100% but it's
very close. Thx for getting me started!

Query#1
SELECT Depreciation.DepreciationID, tblCount.CountID AS InstanceID,
Depreciation.PurchaseDate, Depreciation.DepreciableLife,
Depreciation.PeriodFreq, Depreciation.PeriodTypeID, Depreciation.AssetID,
Depreciation.PurchasePrice, Depreciation.SalvageValue,
Depreciation.DepreciationDate, Depreciation.DepreciationAmount
FROM Depreciation, tblCount
WHERE (((Depreciation.DepreciableLife) Is Null)) OR
(((tblCount.CountID)<=[Depreciation].[DepreciableLife]))
ORDER BY Depreciation.DepreciationID;


Query#2
SELECT qryDepreciationCartesian.DepreciationID,
qryDepreciationCartesian.InstanceID,
IIf([DepreciationException].[DepreciationID] Is
Null,IIf(([qryDepreciationCartesian].[PeriodTypeID] Is Null) Or
([qryDepreciationCartesian].[PeriodFreq] Is Null) Or
([qryDepreciationCartesian].[InstanceID] Is
Null),[qryDepreciationCartesian].[PurchaseDate],DateAdd([qryDepreciationCartesian].[PeriodTypeID],[qryDepreciationCartesian].[InstanceID]*[qryDepreciationCartesian].[PeriodFreq],[qryDepreciationCartesian].[PurchaseDate])),IIf([DepreciationException].[IsCanned],Null,[DepreciationException].[InstanceDate]))
AS DepDate, qryDepreciationCartesian.PurchaseDate,
qryDepreciationCartesian.DepreciableLife, qryDepreciationCartesian.AssetID,
qryDepreciationCartesian.PurchasePrice,
qryDepreciationCartesian.SalvageValue,
qryDepreciationCartesian.DepreciationDate,
qryDepreciationCartesian.DepreciationAmount,
qryDepreciationCartesian.PeriodFreq, DepPeriodType.PeriodType,
DDB([PurchasePrice],[SalvageValue],[DepreciableLife],[InstanceID]) AS myDDB
FROM (qryDepreciationCartesian LEFT JOIN DepPeriodType ON
qryDepreciationCartesian.PeriodTypeID = DepPeriodType.PeriodTypeId) LEFT JOIN
DepreciationException ON (qryDepreciationCartesian.InstanceID =
DepreciationException.InstanceID) AND
(qryDepreciationCartesian.DepreciationID =
DepreciationException.DepreciationID)
ORDER BY qryDepreciationCartesian.DepreciationID,
qryDepreciationCartesian.InstanceID;

If you look at this code, the most important line is the one that
calls the DDB function. You can call that yourself, for example in a
query. As you can see in the help file it takes 4 arguments. Assuming
you have a query with those 4 columns, you can create a fifth column
using:
myDDB: DDB(cost, salvage, life, period)

-Tom.
Microsoft Access MVP

Using Access 2003, I’m trying to have a db auto calculate an assets
depreciation values over a user specified time period. I have a main form
“frmAssets†a subform “frmAssetsSF†and then another subform inside the last
subform “Depreciation Subform.â€

Using the template example for recurring events, the subform is populated
but the data is not bound to a table. The only time the data is bound is if
it’s an exception. I like this concept and would like to be able to calculate
the depreciation values and then display them in a subform without having to
store the values in a table.

I found the following MS example online. It’s a great bit of code. It asks
for the initial cost of the asset, salvage value, and the assets useful life
in months. Then, you specify the year you would like to calculate, hit enter,
and the depreciation value is displayed. This is not straight line DP; the
values are different from year to year.

On subform “frmAssetsSF†I have the following fields that can be pointed to:

[PurchaseDate]
[PurchasePrice]
[SalvageValue]
[DepreciableLife] – months

If the purchase date is: 12/15/2008
And the purchase price is: $5,000
And the salvage value is: $100
And the depreciable life is: 60 months (5 years)

The following should be displayed in the subform:

Date: Depreciation Amount:
12/15/2009 $2,000.00
12/15/2009 $1,200.00
12/15/2010 $720.00
12/15/2011 $432.00
12/15/2012 $259.20


I know this is a lot to ask. But if someone could point me in the right
direction I would greatly appreciate it.


Private Sub cmdDepreciation_Click()

Dim lngErr As Long, strError As String
Dim Fmt, InitCost, SalvageVal, MonthLife, LifeTime, DepYear, Depr
Const YRMOS = 12 ' Number of months in a year.

On Error GoTo Save_Error

Fmt = "###,##0.00"
InitCost = InputBox("What's the initial cost of the asset?")
SalvageVal = InputBox("Enter the asset's value at end of its life.")
MonthLife = InputBox("What's the asset's useful life in months?")

Do While MonthLife < YRMOS ' Ensure period is >= 1 year.
MsgBox "Asset life must be a year or more."
MonthLife = InputBox("What's the asset's useful life in months?")
Loop

LifeTime = MonthLife / YRMOS ' Convert months to years.
If LifeTime <> Int(MonthLife / YRMOS) Then
LifeTime = Int(LifeTime + 1) ' Round up to nearest year.
End If

DepYear = CInt(InputBox("Enter year for depreciation calculation."))
Do While DepYear < 1 Or DepYear > LifeTime
MsgBox "You must enter at least 1 but not more than " & LifeTime
DepYear = InputBox("Enter year for depreciation calculation.")
Loop
Depr = DDB(InitCost, SalvageVal, LifeTime, DepYear)
MsgBox "The depreciation for year " & DepYear & " is " & _
Format(Depr, Fmt) & "."

End Sub