Date program calculation

[prometheis]

I have a form that the text box information controls a query to fill in a
report. Here is what I need: I need to be able to display on the report based

on the items older than the inputed Depreciation value (1-7) I.E. If a value
of 5 is put in I want to see all the items that are 5 years old based on the
fiscal year starting 7/1 and ending on 6/30.
The form:
combo box: cmbDepreciationRate = 1 -7 (years)
text box: txtDepreciationDate = current date minus cmbDepreciationRate:
=DateAdd("yyyy",-[cmbDepreciationRate],Date())
This works well and gives me a date based on todays date - the rate that was
put in.
NEED THESE 2 BOXES TO GIVE ME THE DATE FOR THE QUERY......
I need the 2 boxes to fill in automatically based on the above date with the
criteria that if the unit is older than 6/30/(year minus depreciation year)
I.E. unit is 5 years old will return a value of 6/30/2004, then the default
date is 6/30/2004 for the start date and 7/1/2005 for the end date. If the
unit is newer than 7/1/(year minus depreciation year) then the default will
be 7/1/2004 for the start date and 6/30/2005 for the end date

text box: txtStart "the start date to search from based on fiscal year"
text box: txtEnd "the date to end the search from based on fiscal year end"
The Query:
Based on the information provided from the text boxes I want the query to
all the units based on the acquire date:
Between [Forms]![frmEnterRoomTypeCategoryDepreciation]![txtStart] And
[Forms]![frmEnterRoomTypeCategoryDepreciation]![txtEnd]


if this can all be done in the query that will be fine also but I have tried
several times in the query to build a formula only to have it ask me for some

value that I want it to search on. Any help on this would be fantastic.
Thanks All!!

 

[Steve Sanford]

Try this:

'--------------------------------------
Private Sub cmbDepreciationRate_AfterUpdate()
Dim DepDate As Date
Dim dteStart As Date
Dim dteEnd As Date
Dim YearAdj As Integer
Dim DepYear As Integer

DepDate = Me.txtDepreciationDate
DepYear = Year(DepDate)
If Month(DepDate) > 0 And Month(DepDate) < 7 Then
DepYear = DepYear - 1
End If

Me.txtStart = DateSerial(DepYear, 7, 1)
Me.txtEnd = DateSerial(DepYear + 1, 6, 30)
End Sub
'--------------------------------------

Did I understand what you wanted???


HTH

 

[prometheis via AccessMonster.com]

Steve, that is exactly what I needed, I tried a few different ways to get
what I wanted but could not get the dates I was looking for. Thanks again!!!

Try this:

'--------------------------------------
Private Sub cmbDepreciationRate_AfterUpdate()
Dim DepDate As Date
Dim dteStart As Date
Dim dteEnd As Date
Dim YearAdj As Integer
Dim DepYear As Integer

DepDate = Me.txtDepreciationDate
DepYear = Year(DepDate)
If Month(DepDate) > 0 And Month(DepDate) < 7 Then
DepYear = DepYear - 1
End If

Me.txtStart = DateSerial(DepYear, 7, 1)
Me.txtEnd = DateSerial(DepYear + 1, 6, 30)
End Sub
'--------------------------------------

Did I understand what you wanted???

HTH

I have a form that the text box information controls a query to fill in a
report. Here is what I need: I need to be able to display on the report based

[quoted text clipped - 29 lines]

value that I want it to search on. Any help on this would be fantastic.
Thanks All!!

 

[Steve Sanford]

You're welcome..
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve, that is exactly what I needed, I tried a few different ways to get
what I wanted but could not get the dates I was looking for. Thanks again!!!

Try this:

'--------------------------------------
Private Sub cmbDepreciationRate_AfterUpdate()
Dim DepDate As Date
Dim dteStart As Date
Dim dteEnd As Date
Dim YearAdj As Integer
Dim DepYear As Integer

DepDate = Me.txtDepreciationDate
DepYear = Year(DepDate)
If Month(DepDate) > 0 And Month(DepDate) < 7 Then
DepYear = DepYear - 1
End If

Me.txtStart = DateSerial(DepYear, 7, 1)
Me.txtEnd = DateSerial(DepYear + 1, 6, 30)
End Sub
'--------------------------------------

Did I understand what you wanted???

HTH

I have a form that the text box information controls a query to fill in a
report. Here is what I need: I need to be able to display on the report based

[quoted text clipped - 29 lines]

value that I want it to search on. Any help on this would be fantastic.
Thanks All!!